MDX result set group by a custom name

Question

Here I came up with a scenario while developing a report, I need to group the result set first with a custom name and then year.

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 -100 ELSE [measures].[Varience] / [measures].[Previous Year Internet Sales Amount] END ) ,FORMAT_STRING = "Percent" MEMBER [measures].[Previous Year Internet Order Count] AS ( [Measures].[Internet Order Count], PARALLELPERIOD ([Date].[Calendar Year].[Calendar Year], 1, [Date].[Calendar Year].CurrentMember) )

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

SELECT {[measures].[Previous Year Internet Sales Amount],[Measures].[Internet Sales Amount] , [measures].[Varience],[Measures].[PercentageVarience] , [measures].[Previous Year Internet Order Count],[Measures].[Internet Order Count] , [measures].[Order Count Varience],[Measures].[Order Count PercentageVarience]} ON COLUMNS , [Date].[Calendar Year].children ON ROWS FROM [Adventure Works]

The result set for the first 4 columns needs to come as [Internet Sales] and next 4 columns as [Internet Orders] so in report layer I get the group by options with custom names. Expected output from the MDX as enter image description here Could you plea help me to find a way to add a group column to result set

ic3 ic3 · Accepted Answer · 2011-12-24T08:39:58

You can use fake mdx calculated members on another dimension to get your label. In our example let's use the [Organization] dimension for this :

WITH 
  // Does not change the value of a tuple
  MEMBER [Organization].[Organizations].[Internet Sales] as [Organization].[Organizations].defaultmember 
  MEMBER [Organization].[Organizations].[Internet Order] as [Organization].[Organizations].defaultmember
SELET
  {[Organization].[Organizations].[Internet Sales]} * {[measures].[Previous Year Internet Sales Amount],[Measures].[Internet Sales Amount] , [measures].[Varience],[Measures].[PercentageVarience]}
  +
  {[Organization].[Organizations].[Internet Order]} * {[measures].[Previous Year Internet Order Count],[Measures].[Internet Order Count] , [measures].[Order Count Varience],[Measures].[Order Count PercentageVarience]} 
  ON 0,
... as your request

That should get your result

MDX result set group by a custom name

1 Answers