Home > MDX > MDX – Labels

MDX – Labels

While developing a MDX query for a report, need to implement a custom group label to all the columns so the end-user will understand the data at a glance.
Simulating the custom group names (Labels) in MDX using AdventureWorks cube as
 
Step 1: Original result set
 

WITH MEMBER [measures].[Previous Year Internet Sales Amount] 
AS
( 
[Measures].[Internet Sales Amount], 
PARALLELPERIOD
([Date].[Calendar Year].[Calendar Year], 1, [Date].[Calendar Year].CurrentMember) 
),FORMAT_STRING = "Currency"

MEMBER [measures].[Varience]  AS
(
[Measures].[Internet Sales Amount] - [measures].[Previous Year Internet Sales Amount]
)
MEMBER [Measures].[PercentageVarience] AS
(
CASE WHEN [measures].[Previous Year Internet Sales Amount] IS NULL 
OR ISEMPTY([measures].[Previous Year Internet Sales Amount]) THEN 1
     WHEN [measures].[Internet Sales Amount] IS NULL THEN -1
ELSE [measures].[Varience] 
 /
[measures].[Previous Year Internet Sales Amount] 
END )
,FORMAT_STRING = "Percent"
  
SELECT
 
   {  [measures].[Previous Year Internet Sales Amount],[Measures].[Internet Sales Amount] 
     , [measures].[Varience],[Measures].[PercentageVarience]
    }

  ON 0

, [Date].[Calendar Year].children ON ROWS
FROM [Adventure Works]


 
Step 2: Result set using a fake dimension default member as
 

WITH MEMBER [measures].[Previous Year Internet Sales Amount] 
AS
( 
[Measures].[Internet Sales Amount], 
PARALLELPERIOD
([Date].[Calendar Year].[Calendar Year], 1, [Date].[Calendar Year].CurrentMember) 
),FORMAT_STRING = "Currency"

MEMBER [measures].[Varience]  AS
(
[Measures].[Internet Sales Amount] - [measures].[Previous Year Internet Sales Amount]
)
MEMBER [Measures].[PercentageVarience] AS
(
CASE WHEN [measures].[Previous Year Internet Sales Amount] IS NULL 
OR ISEMPTY([measures].[Previous Year Internet Sales Amount]) THEN 1
     WHEN [measures].[Internet Sales Amount] IS NULL THEN -1
ELSE [measures].[Varience] 
 /
[measures].[Previous Year Internet Sales Amount] 
END )
,FORMAT_STRING = "Percent"

MEMBER [Organization].[Organizations].[Internet Sales] as [Organization].[Organizations].defaultmember 
  
SELECT
  {[Organization].[Organizations].[Internet Sales]} 
  * {  [measures].[Previous Year Internet Sales Amount],[Measures].[Internet Sales Amount] 
     , [measures].[Varience],[Measures].[PercentageVarience]
    }

  ON 0

, [Date].[Calendar Year].children ON ROWS
FROM [Adventure Works]


 
The above calculations are related to Internet Sales so I preferred to have an Internet Sales as the custom group label to all the columns
Here, faking a dimension default member and use the same to cross join with measures in order to get all the default members of the dimension.
It is like creating a dimension to available for all the measures and using the same for the cross join to make a logical AND operation.

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: