Purchase On-Demand MDX Training

MDX Cheat Sheet


MDX Expression Cheat Sheet

Problems Calculation Expression
Simple Gross Profit Calculation
                        
[Measures].[Sales Amount] - [Measures].[Total Product Cost]                        
                        
                    
Sales in the USA
                        
([Measures].[Sales Amount], [Customer].[Country].&[United States])                        
                        
                    
Year to Date Sales (Works for any level of Date Hiearchy)
                        
Aggregate(

            PeriodsToDate( [Date].[Calendar Hierarchy].[Year],

            [Date].[Calendar Hierarchy].CurrentMember ),

            ([Measures].[Sales]))                        
                        
                    
Alternate Year To Date Expression (YTD, QTD, MTD)
                        
AGGREGATE (

            YTD ([Date Order].[Calendar].CurrentMember),

            Measures.[Sales Amount])                        
                        
                    
Product Ranking
                        
IIF (Product.Product.CurrentMember IS Product.Product.[All],NULL,

IIF (Measures.[Sales Amount] = 0, NULL, 

RANK(Product.Product.CurrentMember, ORDER (Product.Product.Members, Measures.[Sales Amount]))))
                        
                    
Sales from 365 Days Ago
                        
(ParallelPeriod([Invoice Date].[Date Hierarchy].[Date], 365, [Invoice Date].[Date 
Hierarchy].CurrentMember), [Measures].[Sales Amount])                      
                        
                    
Sales in the Previous Period
                        
(Measures.[Sales Amount], [Date Order].[Calendar].CurrentMember.PrevMember)
                        
                    
Top 10 Selling Products (Named Set)
                        
TopCount (Product.Product.Product.Members,10,Measures.[Sales Amount])                      
                        
                    
Three Years Average Sales From NOW()
                        
Avg(

{ParallelPeriod( [Date].[Date].[Year], 3,

StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")) :

StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")} , [Measures].[Sales Count])
                        
                    
Drillthrough Action Caption
                        
'Get Sales Details for'

+[Product].[Product].CurrentMember.Member_Caption                     
                        
                    
Change SSAS Calculation Text color
                        
IIF([Measures].[Profit Percentage] < .40, 255 , 0)                     
                        
                    
Changing a Calculation with a SCOPE statement
                        
SCOPE ([Measures].[Scope Profit]);

THIS = ([Measures].[Sales Amount] - [Measures].[Standard Product Cost]);

END SCOPE;                    
                        
                    
Clear Ration Value when at all level
                        
SCOPE ([Customer].[Customer Geography].[All], Measures.RatioOverParent);

THIS = NULL                    
                        
                    
SSAS KPI Value Expression
                        
[Measures].[Sales Amount] * 1.2                    
                        
                    
SSAS KPI Goal Expression
                        
Case

    When IsEmpty

         (ParallelPeriod

           ([Date Order].[Fiscal].[Fiscal Year],

             1,[Date Order].[Fiscal].CurrentMember))

    Then [Measures].[Sales Amount]

    Else 1.10 *

         ([Measures].[Sales Amount],

          ParallelPeriod([Date Order].[Fiscal].[Fiscal Year],

          1,[Date Order].[Fiscal].CurrentMember))

End                    
                        
                    
SSAS KPI Status Expression
                        
Case

    When KpiValue( "Sales Revenue YTD" ) / KpiGoal( "Sales RevenueYTD" ) > 1

    Then 1

    When KpiValue( "Sales Revenue YTD" ) / KpiGoal( "Sales Revenue YTD" ) <= 1

               And 

               KpiValue( "Sales Revenue YTD" ) / KpiGoal( "Sales Revenue YTD" ) >= .85

    Then 0

    Else -1

End                    
                        
                    
SSAS KPI Trend Expression
                        
Case

    When IsEmpty

         (ParallelPeriod

           ([Date Order].[Fiscal].[Fiscal Year],

             1,[Date Order].[Fiscal].CurrentMember))

    Then 0 

    When VBA!Abs

         ((KpiValue( "Sales Revenue YTD" )- (KpiValue( "Sales Revenue YTD" ),

            ParallelPeriod( [Date Order].[Fiscal].[Fiscal Year],

                1, [Date Order].[Fiscal].CurrentMember)))

          /(KpiValue( "Sales Revenue YTD" ),

            ParallelPeriod

            ( [Date Order].[Fiscal].[Fiscal Year],

              1,[Date Order].[Fiscal].CurrentMember))) <=.02

    Then 0

    When (KpiValue( "Sales Revenue YTD" )- (KpiValue( "Sales Revenue YTD" ),

             ParallelPeriod

             ( [Date Order].[Fiscal].[Fiscal Year], 1,

               [Date Order].[Fiscal].CurrentMember)))

         /(KpiValue( "Sales Revenue YTD" ),

           ParallelPeriod( [Date Order].[Fiscal].[Fiscal Year],1,[Date Order].[Fiscal].CurrentMember)) >.02

    Then 1

    Else -1

End