Issues with scorecard with several dimensions on rows and metrics on columns RRS feed

  • Question

  • Hi.

    I have a scorecard in PPS 2010 in which I have a Dashboard Designer KPI with different metrics (actual X; actual Y; target; indicator showing red, yellow or green; trend arrow) on columns. On rows I would want drill down functionality. First, I want to show four perspectives á la balanced scorecard. Beneath these perspectives I want different goals/KPI's set in the organisation, such as "budget deviation", "staff turnover" etc. If I drag the latter dimension in, all is well. When dragging the perspective dimension in (and above the goals/KPI's on rows) I can make them drillable.

    The problem is that on the perspective rows (i.e. the ones I see when the scorecard is collapsed) I get all empty cells except for one metric: "target". There instead I get what seems to be the max value from the underlying goals/KPI's (that show a mix of units, % etc.).

    In the cube I use as a data source I have no aggregated values on perspectives, so blank cells is acceptable on perspective rows (but the best thing would of course be to hide the metric columns showing blank cells, just to make them appear when I drill down and values exist - I suppose that is not possible). But what I would like is the indicators for the perspectives to show an average of the indicators of the goals/KPI's beneath. Is this possible in any way with my setup? Or can I solve my problem with another scorecard setup? I prefer not to manually create one DD KPI for every organizational KPI  and place them under objective KPI's as this would be very time consuming.

    Another issue I have is that beneath the perspectives I see ALL goals/KPI's instead of just the goals/KPI's bound to the particular perspective. I can of course filter the scorecard to remove empty rows so it looks alright for the end user, but it seems a bit weird that Dashboard Designer shouldn't be able to recognize the relations between the two dimensions. Please explain this to me.

    I hope I made myself clear.

    Thanks a lot for any help.

    Tuesday, October 12, 2010 9:53 AM


  • What you can do is create Objective KPIs in dashboard designer for you perspectives.  For the Calculation property setting you can chose the Avg of Children setting to get the look that you want and then this will roll-up your KPIs to present an Average of the targets.

    Average of children - Take the corresponding target score for each of the child KPIs immediately below this KPI and use the average as the input value for this KPI.

    For the cube design it sounds like you should denormalize the two dimensions and possibly combine them into a single dimension table.


    Dan English's BI Blog
    Tuesday, October 12, 2010 10:28 AM