PerformancePoint Top 10 Filter in Analytic Grid

Applies To: SharePoint 2010, SQL 2008 R2, PerformancePoint

Ran into a surprising issue with PerformancePoint today when creating a simple Analytic Grid. I had all the measures and dimensions setup correctly and decided it would be best to just show the top 10 rows. Fortunately, the Dashboard Designer has a handy Top 10 filter built in. Unfortunately, it doesn’t work.

When you apply the filter you will get a message like “There are no data rows to display” or “There is no data to display” or even “Drag measures, dimensions, or named sets to Rows and Columns to lay out the report.” This is especially irritating because it works just fine for Analytic Charts.

Some quick research revealed that the problem is with the generation of the MDX (Query) when working with the Analytic Grid and how SQL 2008 R2 handles it. You can request a hotfix for the problem (for SQL 2008 R2), but it apparently doesn’t fully fix the problem.

So, you’re pretty much stuck with a half fix from Microsoft. This is better than nothing and I would recommend doing it. However, if you’re like me and not in charge of the database management and will have to wait for that team to evaluate and apply the hotfix (if they even will), you might be interested in the workaround I’m using.

My workaround is pretty simple and fixes simple Analytic Grids you would like to start with the Top X type filters, but doesn’t fix the problem when these filters are applied on the fly by the end user using the dashboard. Basically, we take advantage of the fact that your grid’s report type can easily be switched in the designer and that Top X filters work fine for Analytic Charts. This solution is inspired by this Question and Answer on TechNet where Dan English delves into the auto generated MDX.

  • Create your report with all the dimensions, formatting, measures, etc. that you want with the exception of the Top X filter.
  • Right-Click on the Grid and select Report Type > Pie Chart
  • Now apply your Top X filter (Right-Click and select Filter > Top 10) – Generally you will select the Top 10 choice in the series section to apply it to one of the column values
  • Switch to the Query tab
  • Click in the text area, Select All (Ctrl-A) and Copy (Ctrl-C)
  • Switch to the Design tab
  • Right-Click and select Report Type > Grid
  • Switch to the Query tab
  • Click in the text area, Select All (Ctrl-A) and Paste (Ctrl-V)
  • Switch back to the Design tab to see it applied correctly

This solution is a little messy but it works until Microsoft releases a Service Pack with a full fix for the issue.

Note about Filters – Since the query has been edited, none of the automatic connection points exist. You will have to manually add parameters (Query Tab) to the report. For instance, I wanted to use a Time Intelligence filter with my top 10 list to show the top 10 for a given time period. With a regular report I would just add the calendar dimension to the Background and then hook up my filter by using the Source value of “Formula”. Now that the Top 10 filter has been applied and I have no automatic parameters, I simply replaced the Where portion of the query that had my background calendar dimension with a parameter (Query Tab). Then in the connection dialog of the dashboard I choose Source value equal to “Member Unique Name”.

2 thoughts on “PerformancePoint Top 10 Filter in Analytic Grid

  1. You shouldn’t need to copy-and-paste the query from the chart to grid. Once you have the chart filtered the way you want it you can simply switch to the grid and you are good to go. The issue is if you start with the grid. This way you can still use the filters without having to setup parameters and lose the end-user interaction with the report.

    So always start with the chart and then switch to the grid. A pain, but that is what currently works.

    • @denglishbi Thanks for your comments, and thanks for the article I linked above! Unfortunately, I cannot replicate what you are talking about here (I’ve tried a couple of times). Starting with the Analytic Chart and then switching to Report Type: Grid still causes the issue for me. The only workaround I’ve got currently is still the cutting and pasting outlined above. Perhaps there is something I am missing? Thanks!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s