Archive

Archive for August, 2013

MDX – Dynamic Selected Vs Rest of Selected analysis

August 19, 2013 Leave a comment

Most of the times we will come across a generic reporting requirements for business analysis and developing MDX to handle these scenarios is so much fun. Here planning to replicate a scenario using AdventureWorks.
Let’s consider you want to get the sales of Australia and Rest of Australia for comparison. We can achieve this as below
Step 1: First, get all countries sales as

SELECT 
{ [Measures].[Reseller Sales Amount] } ON 0,
NON EMPTY {    
               [Geography].[Country].[Country].Members		 
          } ON 1
FROM 
[Adventure Works]

Step 2: Create a calculated member to hold the Rest of Countries scenario as

WITH MEMBER [Geography].[Country].[RestOfCountries] as
Aggregate 
         ( EXCEPT 
                ([Geography].[Country].[Country].Members,
                     [Geography].[Country].&[Australia]       
                )
         )
SELECT 
{[Measures].[Reseller Sales Amount] } ON 0,
NON EMPTY {    
               [Geography].[Country].&[Australia]
			  ,[Geography].[Country].[RestOfCountries]	 
          } ON 1
FROM 
[Adventure Works]

Step 3: Let’s make it bit dynamic using Axis and real fun starts here

WITH MEMBER Geography.Country.RestOfCountries as
Aggregate 
         ( EXCEPT 
                (Axis(1).Item(0).parent.children,
                   Axis(1).Item(0)  
                )
         )
SELECT 
{[Measures].[Reseller Sales Amount] } ON 0,
NON EMPTY {    
               [Geography].[Country].&[Australia]
	      ,[Geography].[Country].[RestOfCountries]		 
          } ON 1
FROM 
[Adventure Works]

Step 4: We can do a calculated measure to avoid creating calculated member, this is completely independent of required member and based on any input member it will read the hierarchy to get the rest of members information

WITH MEMBER [Measures].[RestOfSelectedMember] as
 SUM(
	      EXCEPT 
                (Axis(1).Item(0).parent.children,
                   Axis(1).Item(0)  
                )
	 , Axis(0).Item(0)
	)
	, format_string = "currency"
SELECT 
{[Measures].[Reseller Sales Amount],[Measures].[RestOfSelectedMember] } ON 0,
NON EMPTY {    
               [Geography].[Country].&[Australia]	
               // Only required member as input	   
          } ON 1
FROM 
[Adventure Works]

Dynamic calculated measure
Step 5: Let’s apply this dynamic logic to another member Product category i.e. take any member

SELECT 
{ [Measures].[Sales Amount] } ON 0,
NON EMPTY {    
               [Product].[Product Categories].[Category].Members		 
          } ON 1
FROM 
[Adventure Works]
GO
WITH MEMBER [Measures].[RestOfSelectedMember] as
 SUM(
	      EXCEPT 
                (Axis(1).Item(0).parent.children,
                   Axis(1).Item(0)  
                )
	 , Axis(0).Item(0)
	)
	, format_string = "currency"
SELECT 
{[Measures].[Sales Amount],[Measures].[RestOfSelectedMember] } ON 0,
NON EMPTY {    
      StrToMember("[Product].[Product Categories].[Category].&[4]",   CONSTRAINED) 
  // Parameter for the required member		   
          } ON 1
FROM 
[Adventure Works]
GO

Dynamic calculation testing
Now we can apply this logic to any required member and it will get the member and rest of member results for various business comparisons. Technically Axis function will do lot of dynamic calculations.

Categories: MDX Tags: