multidimensional-expression-part-4

How do I group dimension members dynamically in MDX?

You can create calculated members for dimension and then use them in the query. Example below will create 3 calculated members based on filter condition:


With MEMBER [Product].[Category].[Result1] AS 'Aggregate(Filter([Product].[Category].[All].Children, [Product].[Category].currentmember.Properties("Key") > "3"))'
MEMBER [Product].[Category].[Result2] AS 'Aggregate(Filter([Product].[Category].[All].Children, [Product].[Category].currentmember.Properties("Key") = "3"))'
MEMBER [Product].[Category].[Result3] AS 'Aggregate(Filter([Product].[Category].[All].Children, [Product].[Category].currentmember.Properties("Key") > "3"))'
SELECT NON EMPTY {[MEasures].[Order Count]} ON COLUMNS
,{[Product].[Category].[Result1],[Product].[Category].[Result2],[Product].[Category].[Result3]} ON ROWS
FROM [Adventure Works]


Result will be:
Order Count
Result1 19,523
Result2 9,871
Result3 19,523

How you can add another dimension as a row on your sample code?
Just add new members for new dimension and add same members in you row details.

How do you write MDX query that returns measure ratio to parent value?
Below is example on how is ratio calculated for measure [Order Count] using Date dimension. Using parent function, your MDX is in dependent on level that you are querying data on. In example below, if you query data at year level, ratio will be calculated to level [All]:


WITH MEMBER [Measures].[Order Count Ratio To Parent] AS
IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0
, NULL
, [Measures].[Order Count]
/
([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent)
)
, FORMAT_STRING = "Percent"

SELECT {[Measures].[Order Count], [Measures].[Order Count Ratio To Parent]} ON 0
, {DESCENDANTS([Date].[Calendar].[All Periods], 1), [Date].[Calendar].[All Periods]
} ON 1
FROM [Adventure Works]

Result will be like:

Order Count Order Count Ratio To Parent
CY 2001 1,379 4.38%
CY 2002 3,692 11.74%
CY 2003 12,440 39.55%
CY 2004 13,944 44.33%
CY 2006 (null) (null)
All Periods 31,455 (null)

If you query data at month level, ratio will be calculated comparing to level quarter:

WITH MEMBER [Measures].[Order Count Ratio To Parent] AS
IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0
, NULL
, [Measures].[Order Count]
/
([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent)
)
, FORMAT_STRING = "Percent"

SELECT {[Measures].[Order Count]
, [Measures].[Order Count Ratio To Parent]} ON 0
, {DESCENDANTS([Date].[Calendar].[Calendar Quarter].&[2003]&[4], 1)
, [Date].[Calendar].[Calendar Quarter].&[2003]&[4]
} ON 1
FROM [Adventure Works]


Result will be like:

Order Count Order Count Ratio To Parent
October 2003 1,779 29.95%
November 2003 1,888 31.79%
December 2003 2,272 38.26%
Q4 CY 2003 5,939 58.87%