AdSense

Thursday, 26 September 2013

Lifesaver - SSAS MDX Quick fixes

Lifesaver - SSAS MDX Quick fixes
How many times have you thought in development that "This must be a common scenario" and then searched all over the place to find the solution for it. I am listing down such scenarios and fixes for those scenarios which are very frequent in the software industry. This will help not only to jumpstart but save your time afterwards durng development. I will be trying to cover as many areas as I can under the heading "Lifesaver".
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