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.

This article contains some examples of MDX statements that you can use. However, these examples are by no means an exhaustive list. There are many more MDX queries that you can use with Dashboard Designer. For more information on MDX queries, see PPS 2007 Multidimensional Expressions (MDX) Reference.

---

• Using MDX in the Formula Editor dialog box

• Using MDX in the Query tab

• Using MDX in the Dimensional Data Source Mapping dialog box

---

Use 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 some code examples that you can use in the Formula Editor.

Description	Syntax
Children of the selected member	<<UniqueName>>.Children or <<SourceValue>>.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 2008	TopCount({Descendants(<<UniqueName>>,2)}, 10, ([Date].[Calendar].[Calendar Quarter].&[2008]&[3],[Measures].[Internet Sales Amount]))
Hierarchize function that lets you 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]) )) ))
Nonempty values function	NONEMPTY(EXISTS([Dimension].[Hierarchy - Dimension].[Level number].members,<<SourceValue>>,'Measure Group'))

 Top of Page

Use 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 some 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] )

 Note   When you create an analytic chart or grid by using an MDX query, the buttons that are associated with query navigation are hidden on the deployed dashboard. Users cannot see or use the toolbar commands for filtering Empty Rows or Empty Columns (analytic grids) or for filtering Empty Series and Empty Bottom Axis (analytic charts). However, when you create the charts or grids by using the Design tab in Dashboard Designer, those toolbar commands are available.

 Top of Page

Use 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 fohttp://office.microsoft.com/en-us/performancepoint/HA102422371033.aspxr scorecards that include target values that change over time, such as sales targets that increase by 10% each fiscal year.

The following table includes some 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% more 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