I have a cube built in SSAS 2008r2. I'm exposing it for the moment through an excel PivotTable.
I recently added a KPI to the cube, and when I show the KPI in my PivotTable interface it causes non-applicable dimension values to be shown at the current level.
For instance, suppose I have customers:
CustomerA CustomerB CustomerC
NY VA TX
When I look at the PivotTable without the KPI,
CustomerB has no rows under
NY because they have no locations there.
When I add my KPI in and display just the
value of the KPI, all is well.
When I add in the status indicator (i.e. traffic light or whatever), I see every customer under every state.
Is this expected behavior? Is there a way around it? I like the KPI but it defeats the purpose if I get every value of my dimension at each level.
Some more details:
Value Expression for the KPI is just a calculated measure, which works fine.
Status Expression is:
CASE WHEN KpiValue("MyKPI") / KpiGoal("MyKPI") > .90 THEN 1 WHEN KpiValue("MyKPI") / KpiGoal("MyKPI") <= .90 AND KpiValue("MyKPI") / KpiGoal("MyKPI") > .75 THEN 0 ELSE -1 END