MDX in Dashboards, Scorecards, and Views?

You can use MDX in Dashboard Designer in three areas: filters, KPI data mappings, and the Analytic View Designer. This capability allows to you do advanced selections and queries for OLAP data sources that may not be possible through the designer user interface.

Filters

By using MDX in filters, you can provide your dashboard users with more relevant views based on their filter selections. For example, you can write MDX for the Region filter to display the top 10 products based on Unit Sales for the selected region. Or you can write MDX for a multi-select Region filter to display the five cities with the greatest Sales Amount for each region that is selected.

The following chart uses a single filter to display both of the following views:

The filter uses MDX to display the children of the selected member to the view: France, Germany, and Italy for Europe and Canada, Mexico, and USA for North Amer.

The simple MDX used to create the filter is shown below:

In the example above, <<UniqueName>> represents the MDX syntax for the selected member (such as [Geography].[Geography].[Region].&[1] for Europe). The appended Children operation is an MDX function that returns all items immediately below the selected item in the OLAP hierarchy.

Using a more advanced example, the following view returns the top two cities for each country selected:

With the multi-select parameter, users can select one or more countries to compare. The same view and filter above creates the following grid, simply selecting different countries:

The MDX used to create this filter is shown below:

Hierarchize(Union(<<UniqueName>>, Generate(<<UniqueName>>,

TopCount(Descendants([Geography].[Geography].CurrentMember, [Geography].[Geography].[City]), 2, ([Measures].[Sales Amt], [Time].[FY Year].&[2006]) )) ))

KPI Data Mappings

Typically, you will use MDX in KPI Data Mappings for Target values, where the target value is a calculation based on another member. For example, you want the Target value of a Sales KPI to be 10% greater than the same period last year (year-over-year growth). You may also need to use an MDX expression to select a calculated member because this capability is not available in the selection tree.

Using the MDX function "PrevMember," this MDX returns the Sales Amount value for the prior time period, multiplied by 10%.

([Measures].[Sales Amt], [Time].[Fiscal].CurrentMember.PrevMember)*1.1

Here's how it appears in the scorecard:

Using the MDX function "ParallelPeriod," this MDX returns the Sales Amount value for the same time period the prior year, multiplied by 20%.

([Measures].[Sales Amt], ParallelPeriod([Time].[Fiscal].[Year],1, [Time].[Calendar].CurrentMember))*1.2

Analytic View Designer

The Analytic View Designer, displayed when you create an Analytic Chart or Analytic Grid, provides an MDX mode, located on the Query tab, where users can provide advanced MDX for their views.

This mode is useful when you want to create an analytic view that cannot be created using the drag-and-drop user interface on the Design tab, such as a view that uses a top count filter. Users can provide any valid MDX in this editor.

Note Views created on the Query tab using MDX do not support ad hoc navigation. Users will not be able to drill down or expand on these views.

Elaine Andersen (elainean@microsoft.com)