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)