Mimicking PerformancePoint Filter Connection Formulas using the Query String (URL) Filter

In this post, I'm going to show you an easy trick using SharePoint's Query String (URL) Filter to create complex and dynamic dashboard filter connections for PerformancePoint Scorecards and Reports.

If you‘ve built any PerformancePoint dashboards, you’ve undoubtedly created and connected PerformancePoint Filters to scorecards and reports (Analytic Grids, Analytic Charts, Reporting Services Reports, etc.) to create powerful and interactive dashboards.

A simple (and maybe not so powerful) example, which I will build upon, would be creating a Customer Country filter using the AdventureWorks SSAS multi-dimensional database and connecting it to an Analytic Grid report summarizing data based on the Customer dimension's Customer Geography hierarchy, as in the figure below:


Figure 1: A Not So Powerful Example of Using a PerformancePoint Filter

Slightly less common is utilizing a PerformancePoint Filter’s Connection Formula to filter a report in a more sophisticated manner using MDX functions (see Extend PerformancePoint dashboards by using MDX queries for a thorough explanation of Connection Formulas). For example, you could add the following simple MDX function as a Connection Formula to modify the filter connection created above:

<<UniqueName>>.Children

This formula would apply the Children MDX function to the value of the filter and when applied to the Customer Geography hierarchy, would summarize the measures by State-Province (i.e., the child level of Country).



Figure 2: A More Powerful Example Using a PerformancePoint Filter with a Connection Formula

The Query String (URL) Filter

Another filter that can be useful for PerformancePoint Dashboards is SharePoint's Query String (URL) Filter. This filter allows you to access a query string parameter and pass its value to a connected PerformancePoint Scorecard or Report. I use Query String (URL) Filters for a couple of different scenarios: one is when I want to link dashboards based on an attribute hierarchy by having a hyperlink on a dashboard (e.g., in an embedded SSRS report) which navigates to, for example, a dashboard focusing on a particular parent or child geographic area. The other scenario is when I have a parameter with hundreds or thousands of options (e.g., counties) and instead of populating a PerformancePoint Filter, I use a custom search box control to set a query string argument which is used to filter the dashboard. There are certainly drawbacks to this filter, like having to reload a page or having an attribute member value exposed in a URL, but it is also very powerful and it can open up new possibilities in your dashboard design.

Using this filter is fairly straightforward allowing you to add the filter to your dashboard and specify the query string parameter name. 

Figure 3: Query String (URL) Filter Web Part Editor

By passing an attribute member value (e.g., [Geography].[Geography].[Country].&[United States]) in the “area” query string parameter, we can accomplish the same thing that the PerformancePoint Filter did without the Connection Formula. The URL would look something like the following, where "area" is the name of the query string parameter (make sure to URL encode the parameter value, otherwise it may not work):

http://servername/site/subsite/Dashboards/AdventureWorks/Page1.aspx?area=%5BCustomer%5D.%5BCustomer+Geography%5D.%5BCountry%5D.%26%5BUnited+States%5D

Using this type of filter opens up the possibility of dynamically controlling what is being summarized in a report via the URL. The problem with this approach, however, occurs when you want to create a more sophisticated filter using something similar to the PerformancePoint Filter's Connection Formula. There is no interface to enter a Connection Formula for a Query String (URL) Filter, so it can't be done. At least that's what I thought until recently when I started playing with the query string parameter value and discovered that I could apply MDX functions directly in the URL.

For example, we can use the same Children MDX function used above directly in the URL, passing [Geography].[Geography].[Country].&[United States].Children as the query string value. The URL encoded query string would look like the following:

http://servername/site/subsite/Dashboards/AdventureWorks/Page1.aspx?area=%5BCustomer%5D.%5BCustomer+Geography%5D.%5BCountry%5D.%26%5BUnited+States%5D.Children

This allows us to produce the exact same results as when we used the PerformancePoint Filter with the Children MDX function in the Connection Formula, as shown below:

Figure 4: Producing the Same Results Using a PerformancePoint Filter (left) and a Query String (URL) Filter (right)

With this knowledge, I began to try slightly more complicated MDX formulas. For example, I could pass TopCount({Descendants([Customer].[Customer Geography].[Country].&[United States], 2)}, 10, [Measures].[Internet Order Count]) to get the top 10 U.S. cities based on Internet Order Count. The URL encoded string would look like:

http://servername/site/subsite/Dashboards/AdventureWorks/Page1.aspx?area=TopCount%28%7BDescendants%28%5BCustomer%5D.%5BCustomer+Geography%5D.%5BCountry%5D.%26%5BUnited+States%5D%2C+2%29%7D%2C+10%2C+%5BMeasures%5D.%5BInternet+Order+Count%5D%29

This would produce something similar to the following:



Figure 5: Top 10 U.S. Cities Based on Internet Order Count (right side)


By this point you can see that there is great potential in using a Query String (URL) Filter with MDX functions. Using this trick you can create very dynamic dashboards which can be controlled using query string parameters instead of having to hard code a conditional formula into PerformancePoint Filter. Just remember, as with anytime you are passing query string parameters in a URL, make sure that you understand how a savvy user may try to modify it to gain access to information that you did not intend.

MDX: Top N by Group with All Others

When summarizing data using MDX, it’s a common requirement to find the top N by group (i.e., the top N tuples of one set for each member of another set). For example, you may need to find the top N brands by state or the top N products by calendar year based on sales count or amount. In MDX, this is accomplished using a combination of the Generate and TopCount functions. As an example, the following returns the top 5 products for each region based on sales amount:

WITH SET [Top N Products] AS
    Generate(
        [Sales Territory].[Sales Territory Region].Children
 , TopCount( 
            [Sales Territory].[Sales Territory Region].CurrentMember
  * [Product].[Product Categories].[Product].MEMBERS
            , 5
            , ( [Measures].[Internet Sales Amount] )
        ) 
    )
SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS,
    NON EMPTY { [Top N Products] }  ON ROWS
FROM [Adventure Works]

This commonly available query uses the Generate function to evaluate the set produced by the TopCount of the cross product of regions and products, and applies it to each of the region member's children. This produces the following results:

Top N by Group Results

But this only provides part of the picture. Typically, I would want to include all other products within each region as well so that I could provide context to the top N items. For example, in a report (which could also be accomplished using MDX... but let's keep the MDX straightforward for this post) I could calculate each product's percent of sales within each region or each region's percent of sales versus all sales. In order to produce the all other products row for each region, we need to create a new calculated member (i.e., [All Other Products]) using the Aggregate and Except functions.

WITH SET [Top N Products] AS
    Generate(
        [Sales Territory].[Sales Territory Region].Children
 , TopCount( 
            [Sales Territory].[Sales Territory Region].CurrentMember
  * [Product].[Product Categories].[Product].MEMBERS
            , 5
            , ( [Measures].[Internet Sales Amount] )
        ) 
    )
    MEMBER [Product].[Product Categories].[All Other Products] AS 
 Aggregate( 
     Except( 
  NonEmpty( 
             { [Sales Territory].[Sales Territory Region].CurrentMember 
      * [Product].[Product Categories].[Product].MEMBERS } 
  )
  , [Top N Products] 
     )
 )
SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS,
    NON EMPTY { 
 [Top N Products]
 , ( 
     [Sales Territory].[Sales Territory Region].[Sales Territory Region] 
     , [All Other Products] 
 ) 
    } ON ROWS
FROM [Adventure Works]

The Aggregate function takes a set (e.g., the cross product of region and product) and aggregates a measure (e.g., sales amount) within the current context. However, we additionally must use the Except function to exclude the top N products for each region from the aggregate. 

While the NonEmpty function within the Except doesn't make a difference in this example, it can provide a significant performance gain depending on the cross product that is being evaluated.

This query results in the following (as a note, I did use an Order function so that All Other Products would sort within each group, but I left it out of the MDX to keep the example clear):

Top N by Group with All Others Results

Although the MDX is a little more complicated, the results are much more useful, providing a basis for creating context for your top N values.