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:


And States:


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:

    WHEN KpiValue("MyKPI") / KpiGoal("MyKPI") > .90
        THEN 1
    WHEN KpiValue("MyKPI") / KpiGoal("MyKPI") <= .90
        AND KpiValue("MyKPI") / KpiGoal("MyKPI") > .75
    THEN 0
  • That's weird, it shouldn't do that. Do you make a complicated MDX to calculate your KPI? – Lamak May 15 '12 at 18:53
  • @Lamak not super complicated. I posted the code. – JNK May 15 '12 at 18:54
  • @Lamak Is there maybe some MDX to filter to only show rows that are applicable at the current level? – JNK May 15 '12 at 19:08
  • I was actually trying to do the same, and I was able to replicate the behaviour you are obtaining, so now I'm trying to understand what is happening here – Lamak May 15 '12 at 19:15
  • @Lamak well its nice to know it's not just me, and that you understand the issue. I am new to SSAS so I worried by misuse of terminology might complicate troubleshooting. – JNK May 15 '12 at 19:21

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:


...which correctly leaves those fields empty.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.