Speaking the language of business intelligence with an Australian accent

Sunday, February 24, 2008

Debugging Filter Links with Web Page Reports

Often when hooking up and testing dashboard filter links you may find the need to see precisely what value is being passed by that filter link. This becomes particularly urgent if you just can't figure out why things in your dashboard are not functioning the way you expect them to.

A few months ago the PPS team put out this post about using web page reports to mimic the absent OverridingURL functionality that used to exist in BSM. It is this technique that I have adapted to create a simple debug page to display the values a filter link passes across to other dashboard items.

Dashboard Filter Links and Request.Params

For those who may not have done much web development (this includes me) the Request.Params collection typically contains things like browser metadata, cookies, server variables and query string values among others. It is a common means of providing page-to-page communication within web applications.

Filters and scorecards pass outgoing filter link data to other dashboard items by appending the data to the Request.Params collection. The receiving dashboard item finds the appropriate Request.Params item it has been configured to look for in the collection and obtains the data needed to drive its behaviour.

What we want to do is simply expose the items in the Request.Params being sent out by either a dashboard filter or scorecard item so that we can see what they're passing across.

1. Create & Configure ASPX page

Create an .ASPX file named RequestParamItems.aspx using the following code. (thanks to Hassan Syed for his help filling the gaps in my C# skills)

<%@ Page Language="C#"%>

<%
    for (int i = 0; i < Request.Params.Count; i++)
    {
        Response.Write(
            "<b>Param Name: </b>" + Request.Params.AllKeys.GetValue(i) + "<br>" +
            "<b>Param Value: </b>" + Request.Params[i].ToString() + "<br><br>");
    }
%>

The code contained in the file is simple and can certainly be expanded to do much more. All this version does is iterate through the page's Request.Params collection and write the values out. Once complete copy the file into the %Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\Root\ directory.

2. Create Web Page Report

Using Dashboard Designer create a web page report that references this file that has been dropped in the Root directory. Note the URL property in the shot below points towards http://localhost:40000/RequestParamItems.aspx.


FilterLinkValuesDebugReport

Once set up, the report immediately retrieves the static members of the Request.Params collection. This provides a good way to confirm that your report is configured correctly and is ready to do its job once contained in a dashboard.

There's some interesting data that is brought back at this initial stage but where are the custom filter link values? They don't exist yet, we have to create them in a dashboard first. The web page report itself when viewed in DD will never contain anything more than the default members of the Request.Params collection.

3. Use the web page report to a dashboard

Add the web page report to a dashboard and create incoming filter links on it. A new item is added to the top of the Request.Params collection for each unique filter link source value name (e.g. Display Value, Member UniqueName etc.) that is added to the report. The filter link values associated with each source value name will be contained within the same Request.Params item delimited with semi-colons.

In the shot below I have created 6 incoming filter links on my FilterLink Values Debug report. Unfortunately from a UI perspective the links look like they are duplicated, they are not; the names of the item the filter link originates from is displayed which can be a little confusing. In fact I have added 3 Display Value links and 3 Member UniqueName links one from each of the three filters located above the report.

DashboardFilterLinks

4. Publish the dashboard and deploy it to the Preview site

Once deployed we can interact with our filters and actually see what those values are via the report. In the screenshot below you can see where the values for the filter selections for Product Color, Geography and Calendar appearing at the top of the report grouped with their source value name.

PreviewRequestParamDebug

This debugging functionality should only be used when deploying to the Preview site. It goes without saying (but I'll say it anyway) that the detailed contents of Request.Params will only be confusing to end users.

The real value of having a debug web page report like this published to the Monitoring server is reusability. Whenever you need to troubleshoot filter link values you simply add it to a dashboard and hook up the troublesome filter links.

Debug Filter Link Formulas

Note that our debug web page report can also be useful for displaying the values returned by filter link formulas. The following shot shows the resultant set that is added to the Request.Params collection after the filter link formula has been applied to the Calendar filter link. In this case it is the .CHILDREN of the currently selected filter value - the two semesters of calendar year 2004.

PreviewFilterLinkFormulaDebug

Add hard-coded parameters

It should also be noted that we can manually add values to the Request.Params collection via the URL configured in the report definition. Simply add a "?" followed by the name you want to give parameter and the value to be assigned to it separated by "=".

WebPageReportCustomParameter

While the addition of a hard-coded parameter is definitely not a dynamic solution at all it can still be very useful in certain situations. For example, the project I am currently working on requires users be able to write and save textual commentary at the dashboard page level. The .NET team has developed a great comment collector control which they embedded in an ASPX page.  We call this page using a web page report and hook up the appropriate filter links in the dashboard. However, each instance of the control needs to "know" what dashboard page it is located on so that the comments entered can be properly associated to that particular page. To solve this we've created an instance of the comment collector web page report for each dashboard page, we simply append an extra parameter to each report URL which passes the name of the page that the comment collector will reside on. 

Remember that the URL in a web page report is static, we cannot alter it at runtime. All we can do is tell the web page report where it needs to send the user. What happens after that is up to the code within the page in concert with the contextual data provided by filter links and/or URL-based static parameters.

The sample workspace file and .aspx page used in the shots above can be downloaded here.

8 comments:

Anonymous said...

Hi Nick
Thanks for the helpfull insight, actually I am looking at a scnenario where I need to pass parameter to a scorecard from an Image(which is a Web Page Report added to the dashboard), OR from the image to a filter also will do

Matt said...

Hey Nick - Thanks for the post. I was developing a custom filter / selection control, and this was invaluable in helping to figure out what I was doing wrong!

Thanks!
~Matt Wilson.

abhay said...

hi nick...is it posible to have dynamic filters?
e.g.based on selection in the first filter values in the next filter should be changed

Nick Barclay said...

not in this relase, unfortunately

Anonymous said...

I'm new at this, so am suppose to build my custom filter on the same Performance Point server? Then I can see my custom filter in Dashboard Designer?

T-SQL said...

Hi Nick,
It is possible to add a Hyperlink property to KPI and pass value of filters to a ssrs report which is deployed in MOSS 2007 site?

FUTURESULTS said...

You can get a text search filter at http://futuresults.com

Anonymous said...

Hi Nick

I have three reports on a dashboard and a 4th zone with 3 stacked detail reports. I want the user to be able to click on one of the three reports and show the associated detail report in the fourth zone. Is there any way to do this in PPS