Speaking the language of business intelligence with an Australian accent

Thursday, February 28, 2008

Creating Dynamic Web Page Reports

As a follow up to my post on debugging filter links with web page reports I thought it would be worthwhile to put together a quick example to illustrate just what an unsung hero the web page report type is. Just think of the web page report as the catch-all report type. Basically, if you can't do it with one of the many pre-baked M&A report types then the web page report is what you want.

In the debugging filter links post we saw how dashboard items communicate with each other by embedding data in the Request.Params collection. In that example we simply displayed the contents of this collection. The next logical step is to actually do something with those contextual values we are passing to the report item at runtime. Remember that all the web page report does is send the user to a static URL, the dynamic part comes from two places:

  1. the incoming filter links configured on the report item embedded in the dashboard
  2. what is done with this filter link data by the code contained in the page we send the user to

Web page reports pretty much opens up the entire .NET framework to us, the possibilities are virtually limitless.

For example, let's imagine (in a somewhat bizarre set of circumstances) that our users want a dashboard page that will allow them to select a which search engine they wish to use. They wish to select their search engine of choice using a dropdown. the home page of the search engine name they select will appear in the dashboard and they can then search to their hearts content.

1. Create a tabular data source for the search engine filter

This will be done using a simple Import from Excel 2007 Workbook tabular data source and manually entering the names of three lesser-known search engines. Please note that they are arranged in alphabetical order, nothing more :)

TabularDataSource

Note the Column Name property in the shot above is set to SiteName. This value could be anything but be aware that when creating filter links from a tabular data source filter it is this column name value that will be used as the name of the item that is added to Request.Params.

2. Create the redirect ASPX Page

Next is to write the code for our redirect page. This will be the page that we point the web page report towards and contains the logic that makes our report dynamic.

In this example I have called it SearchEndineRedirect.aspx. Once completed drop the file (or a copy of it) into the %Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\Root\ directory.

Here is the code for that page.

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

<%
    if(!string.IsNullOrEmpty(Request.Params["SiteName"]))
    {
        string SearchEngine = Request.Params["SiteName"];
        string WebSite = "
http://www." + SearchEngine + ".com";
        Response.Redirect(WebSite);
    }
    {
        Response.Write("No filter values available yet - create a dashboard and configure a SiteName filter link on this report");
    }
%>

All this code is does is look for the item named SiteName in the Request.Params collection and then concatenates the value contained therein with a "http://www." in front and a ".com" at the end. We then redirect the user to this newly-generated URL.

3. Create a web page report

Configure the URL property to point towards the newly-created SearchEngineRedirect.aspx page (http://localhost:40000/SearchEngineRedirect.aspx) as seen below.

WebPageURL

Remember, all we're doing is sending the user to a URL, it could be any URL. Preferably, though, we want it to be a page that has been created with the intention of doing something useful with the filter link values that are going to be passed to it. Remember, we can also pass static parameter values to our report via the web page report URL. This technique is detailed in the debugging filter links post

Note that our C# code from step 2 checks whether the SiteName item of Request.Params collection IsNullOrEmpty. In essence we're checking whether this item exists in the collection and handle things from there. By doing this we avoid a stack trace error being displayed in the DD interface when configuring the report because, at this point, the SiteName item can't exist; we need to create the filter link in the dashboard first. Handling this unavoidable situation elegantly helps in confirming that our ASPX page is ready for use.

4. Create and publish the dashboard

Create a simple 1 page dashboard and create a Tabular Values filter referencing the Search Engines data source and using the SiteName field configured in step 1.

DashboardUIFilterLink 

5. Deploy the dashboard to the preview site and test

Now all that is left is to admire our handiwork. As you can see from the shots below the user's interaction with the dropdown passes the selected value to our SearchEngineRedirect.aspx which then generates the URL and redirects them to the appropriate page via the web page report.

DashboardGoogle

DashboardLive 

The sample workspace and .aspx file used in this post can be downloaded here.

11 comments:

Jeffrey Carr said...

This is an inspired (and inspiring) post, Nick. I'm planning a How To article with some ideas on how to leverage the Web page report, particularly for data visualization (IMO), so thanks for a great post.

Anonymous said...

This worked great when I used tabular values as my filter. However, my Country filter is from an SSAS 2005 cube, and I couldn't get it to work. I named my filter Country but utilizing the Request.Params["Country"] didn't get me anywhere. I then tried Request.Params["[Dim Geography].[Country].CurrentMember.Name"] but to no avail. How do I do this utilizing a SSAS cube as a filter?

Nick Barclay said...

Try debugging the values being passed across using the technique detailed in this post http://nickbarclay.blogspot.com/2008/02/debugging-filter-links-with-web-page.html

Cheers,
NB

Anonymous said...

Nick,

I might sound silly , What is the equivalent location in Performance Point Server 2010 for the root directoty of PPS 2007 which was ---

%Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\Root\ directory

I need to place a redir.sapx file there to link a KPI to a Webpage using the "OverridingURL" paramaeter.

Thanks in advance.

Michael

Nick Barclay said...

Michael, I don't think there is one any more. I'm pretty sure all the PPS 2010 web infrastructure is managed through MOSS. ..\Root was just a virtual directory that was used to house the Dashboard Preview site bit (which does not exist in the new product). Why not just create your own virtual directory and place the .ASPX file there?

Anonymous said...

Nick,
I have tried putting the redir.aspx file in the virtual directory but the code doesn't seem to work since when i try to drag the OverridingURL condition onto the Redirect Blank page (which has the Webpage report) in the corresponding properties dialog box, instead of "Parameters" (in PPS 2007) in drop-down we have "URL" in PPS 2010 to link to the KPIROW value. In the redirect.aspx code we are referring to "Parameters" while in the PPS 2010 we have the "URL" property. Or am i missing something? could you please help? Does the old redir.aspx code still work in PPS 2010 scenario?
Please advise.

Thanks in advance.
Michael

Nick Barclay said...

Michael, try using the debugging web page I detailed in the related "Debugging Filter Links" post that links from this one. You'll probably get a clearer idea of just what's going on under the covers here.

Cheers,
NB

Ásgeir said...

Hi Nick. We have a link between scorecards and webpages using overridingurl in our PPS2007. When we replicate that in MOSS2010 it works, but there is no hand over the KPI name on the scorecard. There is a hand over all other columns but our users are used to clicking the KPI name. Have you seen this and do you know a workaround?

Ásgeir said...

When I read my comment I can see it's a bit unclear. What I mean is that the mouse icon is not changed to a hand like normally when you have a hyperlink, put stays as a pointer.

Nick Barclay said...

Sorry Asgeir, not sure of the answer here. Have not been working with PPS 2010 much at all.

Sorry,
NB

Naat said...

The next time I read a blog, Hopefully it won't fail me as much as this one. I mean, I know it was my choice to read, nonetheless I really believed you'd have something useful to say. All I hear is a bunch of crying about something that you could fix if you weren't too busy searching for attention.