Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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
add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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