Business Scorecard Manager – Extended Page Filter Functions

In a previous post I detailed how to utilise the BSM-generated Page Filter parsing code in order to hook SSRS report view parameters to the values in scorecard page filters. If you haven’t read the original post it may be worth having a look at it before reading on as the rest of this post assumes some familiarity with this technique. We do cover this process in step-by-step detail in the upcoming BSM book which recently has appeared on Amazon, so the day it becomes available is not far off now.

Something I experimented with while researching the book was the ability to easily access a bit more information from the PageFilter XML fragment than just scorecard page filter values. The well-formed XML fragment that is passed from the scorecard view to report views contains not only data about the page filters engaged on the scorecard but also contextual row and column slicer data not to mention objective, scorecard and KPI GUIDs. In order to be able to gain access to this data I made a few small adjustments to the functions and added a new one.

GetPageFilterData() – this was originally the GetPageFilter() function. I have extended it to accept second string parameter named “type”. The type parameter requires a value of “Page”, “Row” or “Column”. The string returned will be an array of the values and label pairs corresponding to the part of the scorecard that has been requested.

GetElementGUID() – this is a new one. Similar code to the GetPageFilterData() function in that it accepts an extra string parameter also named type. This parameter requires a value of either “Scorecard”, “Objective”, or “KPI”. It will return the GUID for the corresponding item in the PageFilter XML fragment. The GUIDs returned are not really of too much use in this context but I figured it would maybe come in handy at some point. Note that the KPI GUID is not the GUID which identifies the base KPI but instead the GUID of the Actual or Target instance within the scorecard view. I’m sure there is some more code that can be written by someone (anyone?) which will allow you to take this GUID and track it back to the KPI GUID that it is based on. If anyone has had any experience with this please let me know.

GetFormatSetting() – this one is unchanged from the original BSM-generated code and simply returns the nth item in the array of label and value pairs that is passed to it by the GetPageFilterData() function.

The GetPageFilterData() function is the main point of this post. If you have a scorecard with page filters, dimension members across the columns and/or the rows axis the ability to easily configure SSRS reports to use the value corresponding to the row/column intersection of a data cell (as well as the currently engaged page filter values) is very useful.

The image below shows a simple scorecard view that has 2 page filters as well as dimension members on both columns and rows. By enabling Cell Selection (Pass cell selection to report view) in the Toolbar Options of a scorecard view we allow the user to click on values in the data grid of the scorecard and hence make the details of where in the scorecard they have clicked available inside the PageFilter XML fragment.



The cell that has been clicked is highlighted in yellow. The SSRS report below the scorecard uses the extended page filter functions to parse out and use the page filter as well as the row and column slicer context of the clicked value. In the end we’re just passing parameters to the report’s MDX query to return the Reseller Sales Amount for CY 2004 in France for Accessories.

Note that there is a second page filter set on “All Products” but we’re returning “Accessories”. The example scorecard view incorporates the MDX dynamic row/column member functionality introduced into BSM in hotfix 3 (hotfix 4 is the latest, though), so selecting “All Products” in the page filter will display the children (Accessories, Bikes, Clothing etc.) of that selected member on the rows axis. I included this just to add a bit more dynamic functionality to the example. In the context of this SSRS report view the Product page filter value is irrelevant – we just need to get the “accessories” row member.

In order to expose precisely what is happening under the covers I have built a second report which does nothing other than break up the page filter XML fragment into its raw and formatted parts and display them. Although not displayed in the shot below, the report also shows the results of the KPI, Objective and Scorecard GUID values as well as the raw PageFilter XML fragment.



The files needed for all of this can be downloaded here. The zip file contains the following:

1. A BSM workspace file that contains definitions for the example KPI & scorecard, data source, indicator etc.
2. An SSRS solution containing the two reports: Extended PageFilter data.rdl and Dynamic Report Data.rdl
3. A text file containing the code for the extended page filter functions. The code is also embedded in both reports too but I figured it would be helpful to have it separate

Enjoy!