Key Performance Indicators, Actions, and the DRILLTHROUGH Statement - Microsoft SQL Server 2008 Analysis Services - page 261

Now you have to define the goal that the enterprise strives to achieve, by specifying the
MDX expression in Listing 15.2.

LISTING 15.2

MDX Expression for a KPI Goal

Case

When [Time].[Time].CurrentMember.Level Is [Time].[Time].[Year] Then .30
When [Time].[Time].CurrentMember.Level Is [Time].[Time].[Quarter] Then .075
When [Time].[Time].CurrentMember.Level Is [Time].[Time].[Month] Then .025
When [Time].[Time].CurrentMember.Level Is [Time].[Time].[Date] Then .0008
Else “NA”

End

To calculate the status, compare Value and Goal using the expression in Listing 15.3.

LISTING 15.3

MDX Expression for KPI Status

Case

When KpiValue(“Growth in Sales” ) >= KpiGoal ( “Growth in Sales” ) Then 1
When KpiValue(“Growth in Sales” ) >= .90 * KpiGoal(“Growth in Sales”) And
KpiValue(“Growth in Sales”) < KpiGoal (“Growth in Sales”) Then 0
Else -1

End

You are finally ready to define the trend (see Listing 15.4).

LISTING 15.4

MDX Expression for a KPI Trend

Case

When [Time].[Time].CurrentMember.Level is [Time].[Time].[(All)] Then 0
When KpiValue(“Growth in Sales”) -
(KpiValue(“Growth in Sales”), ParallelPeriod ([Time].[Time].[Year], 1,
[Time].[Time].CurrentMember)) /
(KpiValue(“Growth in Sales”), ParallelPeriod ([Time].[Time].[Year], 1,
[Time].[Time].CurrentMember)) <=.02 Then 0
When KpiValue(“Growth in Sales”) - (KpiValue(“Growth in Sales”),
ParallelPeriod ([Time].[Time].[Year], 1, [Time].[Time].CurrentMember ))/
(KpiValue(“Growth in Sales”), ParallelPeriod([Time].[Time].[Year], 1, [Time].
[Time].CurrentMember ) ) >.02 Then 1
Else -1

End