MDX: Ordered versus Purchased Product by a Customer

WITH  MEMBER Measures.SoldProducts AS     DISTINCTCOUNT(       NonEmpty       (         Descendants         (           [Product].[Product Categories]          ,[Product].[Product Categories].[Product]         )        ,[Measures].[Internet Sales Amount]       ))      SELECT   {   [Measures].[Internet Order Quantity],Measures.SoldProducts   } ON 0  ,filter( [Customer].[Customer].&[15536],Measures.SoldProducts>0) ON 1 FROM [Adventure Works]

MDX Top N with Remainder every Year

This is some good stuff. I tried a bit on this but was lost in mid way. This was coolly solved by HrvojePiasevoli in the MSDN forums. Thought of sharing this and may be this will be useful for few more.

Learnt Extract, Union and many more.
The requirement here is to Display the Top N sales, remainder and total for each year

with member [Product].[Product].[AllProducts] AS [Product].[Product].[All Products] ,BACK_COLOR ="&H0000D00D" MEMBER [Product].[Product].Remainder AS Aggregate(         [Product].[Product].[Product].MEMBERS -         EXTRACT(                   TOPCOUNT(                         [Date].[Fiscal Year].CURRENTMEMBER *                         NonEmpty([Product].[Product].[Product].MEMBERS, [Measures].[Internet Sales Amount]),                         3,                         [Measures].[Internet Sales Amount]                         )         ,[Product].[Product])             ), BACK_COLOR ="&H0000FFFF"             set TopSetWithReminder as Generate(     [Date].[Fiscal Year].[Fiscal Year].MEMBERS,     UNION(             TOPCOUNT             (                   [Date].[Fiscal Year].CURRENTMEMBER *                   NonEmpty([Product].[Product].[Product].MEMBERS, [Measures].[Internet Sales Amount]),                   3,                   [Measures].[Internet Sales Amount]                   ),                   ([Date].[Fiscal Year].CURRENTMEMBER,[Product].[Product].Remainder),                   ([Date].[Fiscal Year].CURRENTMEMBER,[Product].[Product].[AllProducts] )             )     ) SELECT {       [Measures].[Internet Sales Amount]       ,[Measures].[Internet Gross Profit] } ON 0, TopSetWithReminder ON 1 FROM  [Adventure Works] CELL PROPERTIES FORMATTED_VALUE, BACK_COLOR

MDX Employee Ancestors

MDX to get the employee and managers above him. User can restrict to what level above him the manager details need to be fetched. Here we are getting immediate manager and his manager.

WITH       MEMBER [EmployeeLevel] AS [Employee].[Employees].CURRENTMEMBER.LEVEL_NUMBER      SELECT       FILTER(             ORDER(                   NONEMPTY(                         ASCENDANTS([Employee].[Employees].&[291])                         ,[Measures].[Reseller Order Count]                   ),                   EmployeeLevel,BDESC             ),             EmployeeLevel<>0 and EmployeeLevel>=2       )       ON 0,       {             [EmployeeLevel],             [Measures].[Reseller Order Count]                  } ON 1 FROM [Adventure Works]

Using the descendant function

WITH MEMBER Manager as [Employee].[Employees].currentmember.parent.name MEMBER Director as [Employee].[Employees].currentmember.parent.parent.name SELECT NON EMPTY {       [Measures].[Reseller Order Count],       measures.manager,       measures.director } ON COLUMNS, NON EMPTY filter (       {             (    DESCENDANTS([Employee].[Employees].[Employee Level 04].ALLMEMBERS,,leaves)             )       },        [Measures].[Reseller Order Count] >0 ) ON ROWS FROM ( SELECT [Employee].[Employees].&[291] ON COLUMNS FROM [Adventure Works])

MDX:Display seconds in dd:HH:mm:ss format

WITH MEMBER measures.seconds as 25324 MEMBER Measures.ddhhmmss as cstr(int(measures.seconds/86400)) + ":" + format(cdate(measures.seconds/86400 - int(measures.seconds/86400)), "HH:mm:ss") SELECT ddhhmmss on 0 FROM [Adventure Works]

If the format is made as "dd:HH:mm:ss" then it would display the value as 31:02:28:44 and not 01:02:28:44

d/dd: It is supposed to give the day of the month, therefore it can not work for more than 31 days.
example: member measures.x as cdate(95324/86400), FORMAT_STRING = 'd:HH:mm:ss'

MDX Top N

MDX query to show the Top 5 products sold for last 3 years using Rank Function. 

WITH             SET [OrderedSet] AS       Generate       (             {                   STRTOMEMBER("[Date].[Calendar].[Calendar Year].&[" +VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]").LAG(3)                   :                   STRTOMEMBER("[Date].[Calendar].[Calendar Year].&[" +VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]")                   },             ORDER             (                   NonEmpty                   (                         (                               [Date].[Calendar].CurrentMember,                               [Product].[Product].[Product]                         ) ,                         { [Measures].[Sales Amount] }                   ),                   [Measures].[Sales Amount], BDESC             )       )       MEMBER [Measures].[Rnk] AS       Rank       (             (                   [Date].[Calendar].CurrentMember,                   [Product].[Product].CurrentMember             ),             Exists             (                   [OrderedSet],                   {[Date].[Calendar].CurrentMember}             )       ) SELECT       {[Measures].[Sales Amount], [Measures].[Rnk]} on 0,       FILTER([OrderedSet],[Measures].[Rnk]<=5) on 1 FROM       [Adventure Works]

MDX query to show the Top 5 products sold for last 3 years using TopCount Function. 

WITH SET Years AS {             STRTOMEMBER("[Date].[Calendar].[Calendar Year].&[" +VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]").LAG(3)       :       STRTOMEMBER("[Date].[Calendar].[Calendar Year].&[" +VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]") } SET Top5 AS Generate(Years,       TopCount(Years.CurrentMember * [Product].[Product].[Product], 5,             [Measures].[Sales Amount])       ) select {[Measures].[Sales Amount]} on 0, Top5 on 1 from [Adventure Works]

When to us Rank approach?

Suppose you want to get the Top 5 products and not just the Top 5 rows, then you might want to consider the Rank approach. 

Example:

Product1  $100   Rank=1

Product2  $90     Rank=2

Product3  $80     Rank=3

Product4  $80     Rank=3

Product5  $70     Rank=4

Product6  $60     Rank=5

Product7  $50     Rank=6

Rank approach will return you Product1,2,3,4,5,6

Top count approach will return you Product1,2,3,4,5


Well today Nov/06/2010,


The exists function here would rank the same value as different. There was requirement where the same value measure value should show same ranks and also one should be able to rank the products per year basis. (i.e. rank based on multiple dimension members)

WITH SET [OrderedSetPerYr] AS          Generate ( Date].[Calendar].[Calendar Year].members , ORDER ( NonEmpty ( ( [Date].[Calendar].CurrentMember, [Product].[Product].[Product].Members ), { [Measures].[Internet Sales Amount] } ) , [Measures].[Internet Sales Amount], DESC ) )       MEMBER [Measures].[Rnk] AS Rank ( ( [Date].[Calendar].CurrentMember, [Product].[Product].CurrentMember ), NonEmpty ( ( [Date].[Calendar].CurrentMember, [Product].[Product].[Product].Members ), { [Measures].[Internet Sales Amount] } ) ,[Measures].[Internet Sales Amount] ) SELECT       {[Measures].[Internet Sales Amount],[Measures].[Rnk] } on 0,       [OrderedSetPerYr]  on 1 FROM  [Adventure Works]

MDX Hierarchy Ordering

Problem
User wishes to fetch a measure for the state and zip codes belonging to the states. The state and zip code is part of a Geography Hierarchy that has hierarchy defined as Country -> State-Province -> City -> Postal Code. The  result gets order at the zip code level but does not get ordered at the City Level.

SELECT  {[Geography].[Geography].[City], [Geography].[Geography].[Postal Code]} ON ROWS,  {} ON COLUMNS FROM  [Adventure Works]

The query has 2 problems in the way the result set is displayed

• The City names comes first
• Zip codes comes next
• Order is not maintained

We would like to have the result come in proper order and in a proper Hierarchy.

Solution


One could now use the Hierarchize() function, but the problem is that it sorts members in a level in their natural order. 

SELECT  Hierarchize  (       {[Geography].[Geography].[City], [Geography].[Geography].[Postal Code]}   ) ON ROWS,  {} ON COLUMNS FROM  [Adventure Works]

But the sort order at the City level is not maintained. We would like to have the maintain the order even at the city level. To solve this issue one could use the ORDER() provided by MDX.

WITH       MEMBER [Measures].[OrderKey]       AS       ANCESTOR([Geography].[Geography].currentmember, [Geography].[Geography].[City]).member_name       + "#" +       ANCESTOR([Geography].[Geography].currentmember, [Geography].[Geography].[State-Province]).member_name SELECT       ORDER       (             {[Geography].[Geography].[City], [Geography].[Geography].[Postal Code]},             [Measures].[OrderKey],BASC       ) ON ROWS,       {[Measures].[OrderKey]} ON COLUMNS FROM  [Adventure Works]

As we don’t want the original order of the members created by the hierarchy, we need to use BASC. BASC means ascending order breaking the hierarchy. So instead of using HIERARCHIZE(), we will use ORDER using the OrderKey with BASC.