Applying a Complex Filter to an Analytic Grid/Chart without losing Navigation

Currently, in v1 of the Monitoring server you cannot apply complex filters, the only filter capabilities in the analytic charts/grids is non empty filtering. You can apply complex filtering by writing custom MDX for an analytic chart/grid, but when you do that you lose the navigation on the view. I have been asked many times if there is some way to filter without losing the navigation. I have come up with a workaround for this - it is not ideal and may not work in every instance though - so proceed with caution! If it works for you great, if not, you will have to wait until v2 when we will be implementing better filtering capabilities in the analytic grids/charts.

The workaround that I have established is to create your analytic grid/chart with the dimension you want to apply the filter to added to the series for a chart or the rows for a grid. Select the "All" member and then apply a sort to the chart/grid. The sort will do nothing with only one member selected but when you have multiple members, it will be in place and ready to go. Then, select the measures or metrics as columns/bottom axis in your view. Add any other hierarchies you want to filter by in the background axis, I added time and geography in my view below.

Now, add the view to a dashboard page and create a filter on the hierarchy that you want to filter on. For my filter I added a Product filter and selected the "All" member as well as one level down in the hierarchy. Add the filter to your dashboard page and then link the filter to the analytic grid by Member UniqueName. Click the Filter Link Formula. button and enter your filter expression in the dialog there:

For my expression, I used a topcount expression and selected members at the Product level in my hierarchy that were descendants of the member selected in my filter. This becomes the set that will pass to my view and be shown in place of the "All" member I used in the initial view.

TopCount ((descendants(<<UniqueName>>, [Product].[Product].[Product])), 20, [Measures].[Sales Amt])

Publish your dashboard, note the results below. Because of the sort that was applied in the view, the view will always be sorted as well. Finally, note that in the last screenshot, you still have full navigation on the view.

 

Alyson Powell Erwin (alysonp@microsoft.com)

Comments

• Anonymous
  March 28, 2008
  Great tip. Thank you, Alyson. I was just wondering, can we do a smiliar thing with the parameters? For instance, can I have a parameter which has two values, Top 10 Products, Bottom 10 Products. Cem

• Anonymous
  April 04, 2008
  The comment has been removed

• Anonymous
  April 07, 2008
  No way to have a parameter with values such as Top 10 or Bottom 10 - we resolve the MDX to members.  Sorry. As for the sorting, when you click browse on the chart/grid in the dashboard designer you can right click and choose sort.

• Anonymous
  May 06, 2008
  What about making a dynamic option for the count of the topcount? Instead of hard coding it to 20, I would like to be able to have the user enter in what they would like or passed as a parameter based on the page or user who is viewing the page.

• Anonymous
  May 06, 2008
  You may be able to make a custom filter to allow you to do what you are trying but the product doesn't currently allow for inputs into a parameter. Thanks, Alyson

• Anonymous
  May 13, 2008
  How to create and map Date range filters(From Date and To Date) to dashboard using PPS monitering server.

• Anonymous
  October 23, 2008
  i have exactly the same task to implement. i followed the steps by you, but its not working :(

• Anonymous
  October 24, 2008
  Does your filter work without the MDX expression override?  If that works, can you verify your MDX expression to ensure that your syntax is correct for the MDX.