Speaking the language of business intelligence with an Australian accent

Wednesday, February 6, 2008

Show Details functionality on Scorecards

A somewhat unknown feature of scorecards is the ability to right-click a cell and drill to the details behind it by selecting Show Details. By default, however, the show details right-click option always seems to be grayed out. Why?


A common assumption is that this is a scorecard scoped setting like the ability to add scorecard comments. In fact, the answer to enabling this functionality lies in the calculation settings of the individual metrics of each KPI contained in the scorecard. In order to enable show details functionality the metric calculation must be set to Source data. The PPS team posted about this a while back. They also wrote a more detailed post a short time ago too that also covers drillthrough actions on analytic charts and grids, definitely worth a read.


Once Source data has been selected the Show Details option will be enabled in any scorecard that contains the metric's parent KPI. Such a fine level of control can be handy for only allowing drillthrough on specific KPI metrics only.


So, the trick to getting the Show Details option ungrayed for a specific scorecard cell is to ensure the KPI metric calculation property is set to source data. However, although the item is now un-grayed... will it work straight away? Depending on the structure of your scorecard you may find that you get the following error:

Cannot show details for this cell because both the row and column must have a selection.

This basically means that the scorecard wants at least one dimension member on its row or column axis so that it has some form of reference from which to perform the drillthrough. Sometimes, though, you may not want your scorecard to have dimension members on present columns or rows; you just want straight KPIs and metrics: a "traditional" scorecard.

Here's the trick to making this work while keeping your scorecard structure the way you want it:

I found that I only needed to have one dimension reference on one axis to get things to work. All you need to do is add a benign dimension member (one that is not vital for the operation of the scorecard) on the top level of the row axis. Once added right-click the item and select Hide. In the screenshot below I added the All member of the Customer attribute hierarchy.


Once the dimension is hidden the dummy All Customers member that we have hidden will display in red. The scorecard has its dimension reference and end users will be none the wiser.


Now that this is done the Show Details gets its dimension reference on one axis (which seems to be enough in this case) and the drillthrough function will return a nice data set to you from a simple right-click and selection of Show details in the scorecard.


Other tips

Don't forget to ensure that the Application Pool account (or the users themselves if you've implemented PerUser security) has read access to the relational database itself and that drillthrough access has been granted on the source cube.


Also remember that you won't have much luck performing a drillthrough on a calculated member or a metric that uses a custom MDX formula.

1 comment:

Anonymous said...

but there is a big bug...
the drillthrough is executed against the wrong connection!!!

I have a scorecard with KPI coming from 4 cubes.
the drillthrough runs against the first cube and not the one behind the selected KPI!!!

there is something wrong in my setup?