BSM - Passing page filters to SSRS report views

I recently experienced a bit of pain while trying to get BSM scorecard page filters to pass their values to SSRS reports. Unfortunately, this behaviour is not possible in BSM without a bit of work. Nonetheless it can be done.

When setting up an SSRS report view you have the option to append page filters. You also get access to set default values for the parameters that a particular report exposes. However, if you have tried setting this up expecting BSM to seamlessly pass its page filter values over you will have found that, once published, the SSRS report view screams out for a parameter named msps and fails; something along the lines of "An attempt was made to set a report parameter 'msps' that is not defined in this report". OK, you think, I'll give you your lousy msps parameter. So you create a simple parameter with that name (you may also repeat the process for a tmsps parameter) and redeploy. Now the SSRS report view renders beautifully but will still not pick up the page filters that are being applied to the scorecard it sits next to.

It's unfortunate that the UI for hooking up SSRS reports kind of leads us to expect that we can hook the page filters to the report parameters without much work. The fact is that we can't achieve this without a little bit of code (which we don't have to write). As a result of the research I did for this post I now understand that the UI only goes part of the way to completely hooking the scorecards and their elements to SSRS reports. In the UI we get access to the parameters of the report itself but we don't have the ability to tell BSM which report parameters to bind to which page filters (or row/column slicers etc.). All we can do in this UI is, in essence, hard-code the report parameters, which doesn't help us achieve our aim.



All we're really doing when we check the append page filters box is ensure that the scorecard appends a well-formed XML fragment to the URL it sends to the report view. If the report does not know what to do with this XML, the fragment vanishes harmlessly into the ether and the report remains oblivious to the data once available to it. We need to embed code into the report to make proper use of the XML.

There is a post in the BSM newsgroup asking how to get around this issue. Some have worked around it by sending users via a custom .aspx page which contains similar code to what we will use in this example to parse out the appropriate bits from the well-formed XML fragment that BSM creates and then pass a homemade URL to the report in question. This approach certainly works but is an ugly solution as it potentially requires a custom-built, hard-coded .aspx page for each report. The solution I present here, while far from graceful & elegant, is at least a little easier to hook up and maintain across multiple reports. If you want more information on the XML fragment that contains not only PageFilter but also Column & Row slicer data and more I highly recommend reading the Business_Scorecard_Manager_Passing_Parameters_WP white paper found on this page.

How do we do it?

First we need to harvest some code. BSM designer gives us the ability to generate a scorecard report .RDL file by selecting Tools -> Deploy Scorecard -> To Reporting Services Report... Ensure you choose "Save SQL Server Report to the File System", all we want is an .RDL file, we don't need to deploy it. Open the RDL file in BIDS and have a look in the Code tab of the report properties. You will find code looks similar to that found in the white paper referenced above. This is the stuff we want. The two functions in this code take the XML fragment and turn it into something more useful. The GetPageFilters() function parses out the page filters and their display values into an array. The GetFormatSetting() function simply returns the nth item in that array. All we need to do is embed this code in our report that we want to hook up to our scorecard. Note that there are always two parameters present in the BSM-generated RDL: msps and tmsps. All we need to do is mimic the way these two parameters are used and use their output to populate the .Value property of our report parameters.

Here are the basic steps needed to ensure your report picks up page filters from a scorecard.

1. Create your SSRS report with parameters that line up with the page filters on your scorecard. You don't have to create parameters for all the page filters on the scorecard, just the ones that are relevant for your report.

2. Copy & paste the code from the Code tab of the BSM-generated report into the Code tab of your report. For a bit of reusability you could create an assembly using this code, register it in the GAC, and then reference that class in all subsequent reports. We're doing things the quick way here.

3. Still in the report properties of the new report ensure you make a reference to the System.XML assembly in the references tab (whether it's 2.0 or 1.1 will depend on what .NET framework version you're working with)



4. Create two parameters named msps and tmsps with the same settings as those in the BSM-generated RDL. Order the parameters so that msps is first and tmsps second.

5. GetPageFilters() returns an array made up of the Parameter.Value and Parameter.Label data we need to pass to our parameters. For example if we have a scorecard with 2 page filters the array returned will be in the form of:

(PageFilter1.Value,PageFilter1.Label,; PageFilter2.Value, PageFilter2.Label,;)

We then use the following code in the Default Values -> Non-Queried area of each parameter to get the item of the array we need. In this case we need either item 0 or item 2 in the array to get the two .Value elements. We wrap GetPageFilters() inside the GetFormatSetting() function.

=Replace(Code.GetFormatSetting(Code.GetPageFilters(Parameters!msps.Value), 0), ";", "")

Some may notice the use of the Replace function to get rid of some unwanted semi-colons in the array returned by GetPageFilters(). There is plenty of opportunity here to tweak the code we get from BSM in order to avoid this and even extend it further to handle Column and Row slicer data. For the purposes of this post I have kept the code generated by BSM as I found it. The sample Pass Page Filters.rdl displays the various parts, both processed and unprocessed, of the BSM XML fragment, this serves to give a clearer picture of what data we're receiving and what we're doing with it.



The sample scorecard and associated report can be downloaded here. Inside the .zip is:

1. a BSM workspace with two KPIs and one scorecard (Internet Scorecard). The scorecard has one scorecard view. The SSRS report view is configured at the scorecard view level.

2. an SSRS solution containing two reports: a clean, BSM-generated, RDL file (Internet Scorecard.rdl) and a report that implements the page filter functionality detailed above (Pass Page Filters.rdl)

3. a .vb file (PageFilters.vb) with the necessary code which I simply cut & pasted from the Code tab in the Internet Scorecard.rdl report.

All you need to do is deploy the SSRS reports first and then publish the scorecard. Note that all has been set up using localhost so if you're working with any named instances you may need to reconfigure some of the data sources.

Hope this is of use to those out there who have been wanting to implement this kind of functionality.

UPDATE: For some more advanced use of this technique including functions that expose row & column member data see this post