Extend PerformancePoint dashboards by using MDX queries

  • Article

 

Applies to: SharePoint Server 2010 Enterprise

You can extend functionality in your dashboards by using custom Multidimensional Expression (MDX) queries in PerformancePoint Dashboard Designer. You can use them to do any of the following:

  • Configure connections between sophisticated dashboard filters and dashboard items by using the Connection Formula dialog box, in which you type a query that refines the way a particular filter works.

  • Create a customized view in analytic charts and grids by using MDX queries on the Query tab.

  • Create calculated members for key performance indicators (KPIs) in a scorecard by using MDX tuples in the Dimensional Data Source Mapping dialog box.

MDX queries are especially useful for advanced dashboard authors who are very familiar with MDX and the SQL Server Analysis Services data that is used in PerformancePoint dashboard items.

This article contains some examples of MDX statements that you can use. However, these examples are an incomplete list. There are many more MDX queries that you can use with Dashboard Designer. To learn more about MDX queries, see Multidimensional Expressions (MDX) Reference (https://go.microsoft.com/fwlink/p/?LinkId=185458).

Note

The examples in this article were created by using the AdventureWorks sample database that is available with Microsoft SQL Server.

In this article:

-
Use MDX in the Connection Formula dialog box (for filters)

-
Use MDX in the Query tab for an analytic chart or grid

-
Use MDX in the Dimensional Data Source Mapping dialog box (for scorecard KPIs)

Use MDX in the Connection Formula dialog box

You can use the Connection Formula dialog box to enhance the way your dashboard filters work with your dashboard items. For example, suppose that you have created a dashboard that includes a Geography filter and an analytic chart that shows product sales. By default, when a dashboard user selects a region or country in the filter, the chart displays the product sales amounts for that region or country. You might also want to display just the 10 best-selling products for each region/country. Your solution is to use the Connection Formula dialog box to enter an MDX query that displays the information that you want to show. Your filter and analytic chart do not change. However, the way the filter works with the report does change.

When you enter MDX queries in the Connection Formula dialog box, you can include one or more monikers, such as <<uniquename>>, in your formula. The moniker that you use can include additional MDX code, but it is not required. 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 display 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 Connection Formula dialog box.

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

The Hierarchize function, which 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'))

You open and use the Connection Formula dialog box when you are connecting a dashboard filter to a report or a scorecard. For more information, see Connect a filter to a report or a scorecard by using Dashboard Designer and Use the Connection Formula dialog box in Dashboard Designer to configure a filter connection.

Use MDX in the Query tab for an analytic chart or grid

When you create or edit analytic charts and grids, you can use the Query tab to configure your report by using a custom MDX query. You typically do this to create certain analytic views that you cannot replicate by using the drag-and-drop functionality that is available on the Design tab.

noteVisual Basic Note
Reports created by using custom MDX lose some of their functionality in dashboards. For example, users will be unable 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 on the Query tab for analytic charts and grids.

Description Syntax

Add a constant line to an analytic chart to indicate a target value. After you create your view by 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 [AdventureWorks]

Create a custom MDX query that can 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. This query also creates the parameters <<Product>> and <<Geography>>.

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

You use the Query tab when you are creating or editing an analytic chart or grid. For more information, see Create an analytic chart or grid by using Dashboard Designer.

Use MDX in the Dimensional Data Source Mapping dialog box

When you want to configure sophisticated KPIs in your scorecard, you can use the Dimensional Data Source Mapping dialog box to enter an MDX tuple to create calculated values. For example, suppose that you have created a dashboard that includes a scorecard for a sales team. The scorecard compares quota amounts to actual sales amounts. However, the sales quota amounts change from one year to the next. Your solution is to create KPIs that use calculated target values. You can use the Dimensional Data Source Mapping dialog box to enter an MDX tuple.

The following table includes some code examples that you can use.

Description Syntax

Calculate a target KPI that is 10% more than the same period last year.

([Measures].[Sales Amount], [Date].[Fiscal].[Fiscal Year].&[2007].PrevMember)*1.1

Create a Trend Indicator for a KPI.

(([Date].[Calendar].CurrentMember, [Measures].[Customer Count])-([Date].[Calendar].CurrentMember.PrevMember, [Measures].[Customer Count]))/([Date].[Calendar].CurrentMember.PrevMember, [Measures].[Customer Count])

Calculate a 20% increase in the Sales Amount value from the same time period the prior year.

([Measures].[Sales Amount], ParallelPeriod([Date].[Fiscal].[Fiscal Year], 2, [Date].[Fiscal].[Fiscal Quarter].&[2007]&[2]))*1.2

You use the Dimensional Data Source Mapping dialog box when you are configuring a KPI. For more information, see Create and configure a KPI by using Dashboard Designer.