Report Portal

How do I group dimension members dynamically in MDX?

How do I group dimension members dynamically in MDX? Source: MSDN SSAS Newsgroup.

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].[Case Result 1] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") < "3"))'
MEMBER [Product].[Category].[Case Result 2] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") = "3"))'
MEMBER [Product].[Category].[Case Result 3] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") > "3"))'
SELECT NON EMPTY {[Measures].[Order Count] } ON COLUMNS
, {[Product].[Category].[Case Result 1],[Product].[Category].[Case Result 2],[Product].[Category].[Case Result 3] } ON ROWS
FROM [Adventure Works]

Result will be

 Order Count
Case Result 118,845
Case Result 29,871
Case Result 319,523

 

 

Tags: mdx, faq, non empty

 

2007-2015 VidasSoft Systems Inc.