Share via

Mapping Dashboard Filters to Analytic Charts and Grids

  • Article

Been asked about this capability a bit recently, so thought I would create  post specifically around this topic.

When creating  Analytic Charts and Grids you have 2 ways to create these.  First, using the Analytic View Designer (AVD - design tab) or second, by manually entering MDX on the Query tab.

Analytic View Designer Queries

These are created by dragging and dropping the hierarchies and measures that you want applied to the query out to the layout area at the bottom of the chart or grid. Then select the items you want from the hierarchies.  Each of the hierarchies placed in the layout area of the view will be available as a filter endpoint.

clip_image002

When linking a filter, you will see the Item By Category hierarchy, the Time Calendar hierarchy, the Measures hierarchy and the Store District hierarchy.

clip_image004

Custom MDX Queries

You can also create an analytic chart or grid by manually entering your MDX expression on the Query  tab of the view. If you manually enter MDX you have to manually insert your parameters using the <<paramname>> syntax in your actual MDX expression, this will make the paramname a dashboard endpoint item that you can map a dashboard filter to.  Notice that in the below query, I manually inserted the Parameter <<geo>> into the place in the query where I want to apply the filter.  The filter will apply using an MDX overwrite of the parameter token that you have assigned.  Only those tokens that you have inserted will be available as a filter endpoint when constructing your dashboard.

clip_image005

For these views, only those parameter tokens are available as a filter endpoint.

clip_image007

I recommend creating all the views that you can using the AVD simply because those views will enable users to navigate on the hierarchies within the view, performing drill down, drill up, cross drill operations etc.  When creating a view using an MDX query, you will lose the ability to navigate the hierarchies. 

Alyson Powell Erwin

alysonp@microsoft.com

Comments

  • Anonymous
    October 30, 2008
    Analytical Grid and Chart, are wonderful reports to enhance your dashboard. However they pose several

  • Anonymous
    May 29, 2009
    Awesome.  Thank you for sharing this.  I got a chart working with a measure parameter using a custom MDX query and was disappointed to see drill down capabilities disabled.  By using the AVD, a custom measure list, and selecting the measures hierarchy in the filter link,  I was able to get the exact drill-enabled functionality I needed.  Much appreciated.

  • Anonymous
    August 27, 2009
    Alyson, I have more of a question than a comment, I am trying to use a analytic grid and retail the drill down functionality but with a calculated memeber. I have tried a custom MDX query and as stated above you loose the drill down and cross drill. I have the calculated memeber in the cube and in excel it works fine but once I add the value to the analytic grid I loose the drill down. Do you know of a way to add a calculation and retain the drill down?

  • Anonymous
    August 27, 2009
    Unfortunately, any time you use custom MDX you lose the drill down functionality.  Can you construct the view without using custom MDX and just use the analytic view designer?