Speaking the language of business intelligence with an Australian accent

Friday, February 16, 2007

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



Ashok said...


I purchased the book but I am unable to locate the two topics I am specifically interested in namely, Capturing the cell selection from a scorecard view in a custom report view or SSRS view and extending Page Filter Functions. Can you pls let me know where I can find them?

Nick Barclay said...

Hi Ashok,

I thought what you'd be looking for is covered in this post and its predecessor. What particular things are you trying to do that is not covered here?


Smartweb Studios said...

I have been looking all over for a solution to one problem I am having with Business Scorecard Manager but have not had any luck as of yet with my specific issue. In the application that I am working with, there is a wealth of information being filtered and processed. When a user goes through the steps of setting up their filters and finally clicks "apply filters", the result may take up to 20 seconds to be generated. This is currently on a testing server so in the end when it is moved over to our main server the speed will be slightly improved, but I am worried that some impatient users may not know that the filter application is in progress due to the fact that a progress bar is not shown. The page also remains the same until the moment right before it refreshes the content so a blank page is not even shown in the interim. Do you know of a way to either A) Add a progress bar to this filtering of data, or B) Add in a custom code snippet to change the button after it is clicked or display some kind of animation letting the user know their request is being processed? Thank you and hopefully you can help me. Your blog appears to be one of the single most helpful sites on the net for this application! - Tim

Nick Barclay said...

Hi Tim,

Sorry, I don't really have any answers for you on that front. Please let me know if you have any luck tracking any down.


Anonymous said...

I need to create a scorecard that can use the username to filter the data. I have implemented dynamic security using the factless user approach to the reseller table in AdventureWorksDW. This works great in Excel, Proclarity, and SSAS browser. When I set the row member in Scorecard Manager to the Reseller that contains the allowed set it doesn't work in BSM. How can you filter the data without having the user manually select a page filter in BSM to use the username.

Fred Robinson

Nick Barclay said...

Hi Fred,

Apologies for my tardy response. Sounds like you're looking for a solution that requires Per User connections. The reason your solution is working so well in Excel, PC, SSAS etc. is that the users are connecting to the OLAP cube as themselves. BSM out of the box uses an application pool identity account to connect to data sources.

Have walk through the example in this blog post on how to set this up