| Author |
 Topic  |
|
|
invantix
USA
6 Posts |
 Posted - 09/21/2009 : 08:29:04
|
I have a query that brings back the results I want, the sales for each product line for the each of the previous 12 weeks. I put it in the MDX editor and it works fine but if I change anything in the GUI the MDX changes. I am wondering, can I create a named set or something to get the last 12 months to show a columns.
Here is my query
Select non empty Crossjoin(HIERARCHIZE(DISTINCT({{
[Market Segment].[All Market Segment].[A269],
[Market Segment].[All Market Segment].[Beverage],
[Market Segment].[All Market Segment].[Commercial],
[Market Segment].[All Market Segment].[Encapsulated Wire]}})),{
[Measures].[Total Dollars]}) on rows,
non empty HIERARCHIZE(DISTINCT({{
Tail(NonEmptyCrossJoin([Time].[Week Ending].[Week Ending].Members,{[Measures].Members}),1).Item(0).Item(0):
Tail(NonEmptyCrossJoin([Time].[Week Ending].[Week Ending].Members,{[Measures].Members}),1).Item(0).Item(0).Lag(12)}})) on columns
From [Shipping Analysis]
The part of the code that gives the previous 12 weeks is:
Tail(NonEmptyCrossJoin([Time].[Week Ending].[Week Ending].Members,{[Measures].Members}),1).Item(0).Item(0):
Tail(NonEmptyCrossJoin([Time].[Week Ending].[Week Ending].Members,{[Measures].Members}),1).Item(0).Item(0).Lag(12)
I would like to create this as a named set and use it in many OLAP reports. If I can do that I can do the same for quarters and years.
Thanks |
|
|
admin
1637 Posts |
Posted - 09/22/2009 : 00:11:53
|
You can create a named-set on SSAS side or in ReportPortal. To create a named-set in ReportPortal, in OLAP report design mode click on named-set toolbar button, select dimension, click on MDX tab and paste your MDX:
Tail(NonEmptyCrossJoin([Time].[Week Ending].[Week Ending].Members,{[Measures].Members}),1).Item(0).Item(0):
Tail(NonEmptyCrossJoin([Time].[Week Ending].[Week Ending].Members,{[Measures].Members}),1).Item(0).Item(0).Lag(12)
Alternatively, you can enable Options > General tab > “Preserve MDX” option to prevent the MDX from being replaced when using a filter.
|
 |
|
|
invantix
USA
6 Posts |
Posted - 09/22/2009 : 10:21:58
|
Thanks!
This worked. I tried on the client and it worked. I created them on the server since I will be using them a lot.
|
|
|
| |
Topic |
|
|
|
Report Portal
MDX Editor vs Builder for dynamic date
