MDX

Here are some useful MDX expressions for specific requirements which we generally come across while working with SQL Server Analysis Services.  I have used sample "Adventure Works" olap database for all the expressions.


1. MDX: To return Product Subcategories which has sales>20000 for every year

SELECT

{[Measures].[Internet Sales Amount]}

ON 0,

Generate(

[Product].[Subcategory].[Subcategory],

FILTER(

      BOTTOMCOUNT(([Product].[Subcategory].CurrentMember*[Date].[Calendar Year].[Calendar Year])                 

            ,1

            ,[Measures].[Internet Sales Amount])

      ,[Measures].[Internet Sales Amount]>20000))          

ON 1

FROM [Adventure Works]

2. MDX: To return Product Subcategories which has sales>20000 for at least 1 year 

SELECT

{[Measures].[Internet Sales Amount]}

ON 0,

Generate(

[Product].[Subcategory].[Subcategory],

FILTER(

      TOPCOUNT(([Product].[Subcategory].CurrentMember*[Date].[Calendar Year].[Calendar Year])                 

            ,1

            ,[Measures].[Internet Sales Amount])

      ,[Measures].[Internet Sales Amount]>20000))          

ON 1

FROM [Adventure Works]