Sql-server – SSAS Cube in Excel – Show only applicable rows with KPI

excelsql-server-2008-r2ssas

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

And States:

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:

The Value Expression for the KPI is just a calculated measure, which works fine.

The 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

Best Answer

Thanks to @ConcernedOfTunbridgeWells for pointing me in the right direction on this.

The issue was with the status expression.

Since it ALWAYS returns a 1, 0 or -1 it didn't account for NULLs.

I added a check at the beginning of the expression:

CASE WHEN IsEmpty(KpiValue("MyKPI")) THEN NULL

...which correctly leaves those fields empty.

Related Question