Speaking the language of business intelligence with an Australian accent

Thursday, July 27, 2006

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

13 comments:

Anonymous said...

If you go to Tools->Deploy Scorecard->To Reporting Services it will auto-generate RDL with sample code in the code section on how to hook up the parameters

Anonymous said...

I've found after following all these steps that reports which already existed on the scorecard before this change was implemented SOMETIMES return an error along the lines of "Default value or value provided for the report parameter is not a valid value". Where the parameter named in the error is your 'handler' parameter ie the one that calls GetPageFilter etc.

To get around this, go back to your RDL and amend the report parameter to contain a valid but hard-coded default value ie so it no longer refers to GetPageFilter etc. Deploy. Now add your report view again. Publish. And everything should be working as expected - albeit with a hardcoded value.

Now go back to your RDL and replace the hard coded parameter value with GetPageFilter etc function. Deploy.

The page filters should now work perfectly.

Anonymous said...

Nick: it works! But I have a question: Is it possible to open the reporting services report in a new window when clicking on a kpi, still passing the params? The solution works when I use a office report view, but there's to much info for the users on the page, so I want to be able to open a new window..

Regards Jan

Nick Barclay said...

Hi Jan,

Try using the "Open in new Window" option in the properties of the KPI instance in the scorecard view.

Cheers,
Nick

Anonymous said...

Thanks Nick.
Only one more question:
For this to work, do I have to remove the reportview?

Jan

Nick Barclay said...

Remove the report view? What for?

Anonymous said...

Never mind, my bad..
Didn't work though..
But thanks anyway :-)
Jan

Anonymous said...

Hi Nick.
This question is a bit out of the topic, but anyway:
Is it possible to dynamically (by mdx) set the page filter in the scorecard manager? Let's say we want to change the time page filter automatically when there is a new month.. I can't see there is any way to do this now, we have to manually edit the page filters every month.

Bulle

Nick Barclay said...

Hi Bulle,

Unfortunately BSM itself won't provide the solution to this common requirement. Instead one way to solve this is to make some additions to the OLAP time dimension. I plan to post on this technique soon.

In essence you need to ensure that the "current" attribute member/s of your time dimension always has the same attribute key for the relevant day/month/year etc. This doesn't mean changing the dimension key value but using the attribute keys that relate to the dimension key. With a dynamic calendar view in the DSV you would try to ensure the key to, say, the month attribute "current" member is always the same i.e. [Calendar].[Month].&[1]. Because the key does not change BSM will remember this as the default page filter member (if you check the option). This way when users access the scorecard it will automatically be sliced by the current member.

More in an upcoming post soon.

Cheers,
Nick

Anonymous said...

Thanks Nick, looking forward to your post on this topic!

Bulle

Anonymous said...

Hi Bulle,

You can resolve it in this way:
1. In the Cube
a)add a new caculated member e.g.[Current Member].
b)Set "Parent Hierarchy" with the calendar dimension
c)Set the "Expression" like this:
StrToMember(
"[Calendar].[Month].&["
+ CStr(year(now()))
+ "].&["
+ CStr(month(now()))
+ "]"
)
2.In the BSM, set the page filter to it:[Current Month]


The [Current Month] filter will get date from machine system time and always point to current month.It's dynamically. You don't need change it later.

vani said...

Hello Nick,
This article have been very helpful to me.But got stuck up with the creation of SSRS report.Can U be clear with "SSRS report with parameters in line with the page filters in bsm".lets say Time.[Calendar Year] is the page filter in Bsm,then same param has to be defined in SSRS Report.If it is that then why to create parameters named msps ,tmsps in SSRS.And where to set the default values, for time param defined or tmsps.Please make these things clear.I'll be glad if U can provide me the code of it.The link provided for the code download is not working properly ,its requesting for credentials.

Anticipating ur reply ASAP.

Thanks & regards,
Vani

Nick Barclay said...

Hi Vani,

Sorry about the file not being available, try it now. The tmsps and msps parameters are there to capture the XML fragment that is passed to it by the scorecard. In actual fact you don't need two parameters. All that is really needed is a parameter (of any name) that can capture that XML fragment. The only reason I kept it that way was because these were the structures MS uses when rdl is generated from a scorecard that is deployed to SSRS.

HTH,
Nick