Home > MDX > MDX – Dynamic Selected Vs Rest of Selected analysis

MDX – Dynamic Selected Vs Rest of Selected analysis

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.

Advertisements
Categories: MDX Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: