About using MDX in Dashboard Designer

You can extend the functionality in your dashboards by using custom Multidimensional Expression (MDX) queries in PerformancePoint Dashboard Designer, and you can do so in many different contexts:

  • Create sophisticated dashboard filters by using the Formula Editor.
  • Customize analytic charts and grids by using MDX queries in the Query tab.
  • Create calculated members for key performance indicators (KPIs) in a scorecard by typing MDX tuples in the Dimensional Data Source Mapping dialog box.
What do you want to learn about?


Using MDX in the Formula Editor dialog box

You can use the Formula Editor dialog box to create complex filters without necessarily having to type complex code. For example, you can create a filter that automatically displays a lower or higher level of detail than is displayed in the default view.

You can type an MDX formula that can include one or more monikers, such as <<uniquename>>, in the Formula Editor dialog box. The moniker that you use can include additional MDX code, but does not necessarily require it. For example, you could use <<uniquename>> by itself to show general information about the dimension member that you want to filter. Or, you could use <<uniquename>>.children to show the next level of detail about the dimension that you want to filter.

The following table includes a few code examples that you can use in the Formula Editor.

Description Syntax
Children of the selected member <<UniqueName>>.Children
Parent of the selected member <<UniqueName>>.Parent
Descendants of the member at level 2 Descendants(<<UniqueName>>,2)
Top 10 descendants of the member at level 2 for the "Internet Sales Amount" measure for Quarter 3 of 2003 TopCount({Descendants(<<UniqueName>>,2)}, 10, ([Date].[Calendar].[Calendar Quarter].&[2003]&[3],[Measures].[Internet Sales Amount]))
Hierarchize function that allows you to compare several countries or regions and select the top two members (cities) in each region Hierarchize(Union(<<UniqueName>>, Generate(<<UniqueName>>, TopCount(Descendants([Geography].[Geography].CurrentMember, [Geography].[Geography].[City]), 2, ([Measures].[Sales Amt], [Time].[FY Year].&[2006]) )) ))

Top of Page Top of Page

Using MDX in the Query tab

You can use the Query tab to create a custom MDX query for analytic charts and grids. MDX queries are especially useful for advanced dashboard authors who are very familiar with MDX and the SQL Server 2005 Analysis Services data that the analytic reports use. This option enables you to create certain analytic views that you could not replicate by using the drag-and-drop functionality that is available in the Design tab. For example, you can use custom MDX code to create an analytic chart that has a built-in top-count filter.

 Note   Dashboard users might not be able to explore data to see higher or lower levels of detail in analytic charts and grids that have custom MDX code. Be sure to take this into account when you use custom MDX queries.

The following table includes a few code examples that you can use in the Query tab for analytic charts and grids.

Description Syntax
Code that you can use to add a constant line to an analytic chart to indicate a target value. After you create your view using drag-and-drop functionality, you can click the Query tab and then add a WITH MEMBER clause. WITH MEMBER [Measures].[Target] As 0.04, FORMAT_STRING="0.0%" SELECT { DESCENDANTS( [Date].[Fiscal].[FY 2003], [Date].[Fiscal].[Month] ) } ON COLUMNS, { [Measures].[Reseller Gross Profit Margin], [Measures].[Target] } ON ROWS FROM [Adventure Works]
Code that you can use to create a custom MDX query that might be used to specify a member set for an analytic chart. This query example returns a member set that contains the top 20 products by sales amount for the calendar year 2006. SELECT {[Time].[Calendar].[Year].&[2006]} ON COLUMNS, {TOPCOUNT({DESCENDANTS(<<Product>>, [Product].[Product].[Product]) }, 20, ( [Time].[Calendar].[Year].&[2006], [Measures].[Sales Amt]) ) } ON ROWS FROM [Sales] WHERE ( <<Geography>>, [Measures].[Sales Amt] )

Top of Page Top of Page

Using MDX in the Dimensional Data Source Mapping dialog box

You can use the Dimensional Data Source Mapping dialog box to specify calculated target values. For example, you can create a target value that is based on a calculation of another member, such as a quota amount. This is useful for scorecards that include target values that change over time, such as sales targets that increase by 10% each fiscal year.

The following table includes a few code examples that you can use for Dimensional Data Source Mapping.

Description Syntax
Tuple that you can use to calculate a target KPI that is 10% greater than the same period last year. ([Measures].[Sales Amt], [Time].[Fiscal].CurrentMember.PrevMember)*1.1
Tuple that you can use to create a Trend Indicator for a KPI. (([Time].[Calendar].CurrentMember, [Measures].[Units])-([Time].[Calendar].CurrentMember.PrevMember, [Measures].[Units]))/([Time].[Calendar].CurrentMember.PrevMember, [Measures].[Units])
Tuple that you can use to calculate a 20% increase in the Sales Amount value from the same time period the prior year. ([Measures].[Sales Amt], ParallelPeriod([Time].[Fiscal].[Year],1, [Time].[Calendar].CurrentMember))*1.2

Top of Page Top of Page

Where to get more information

Create a custom MDX filter using the Formula Editor dialog box
Creating filters in Dashboard Designer
What is the Formula Editor, and how do I use it?