Knowledge Base

How to use Multidimensional Expressions (MDX) in PerformancePoint Dashboard Designer in filters, in key performance indicator (KPI) data mappings, and in the Analytic View Designer

Article ID: 942514

Article Last Modified on 10/30/2007

APPLIES TO

Microsoft Office PerformancePoint Server 2007, 32 Bit Edition
Microsoft Office PerformancePoint Server 2007, 64 Bit Edition

INTRODUCTION

You can use Multidimensional Expressions (MDX) queries in PerformancePoint Dashboard Designer on a computer that is running Microsoft Office PerformancePoint Server 2007. An MDX query can be used in the following areas:

Filters
Key performance indicator (KPI) data mappings
The Analytic View Designer

This capability lets you perform advanced selections and queries for online analytical processing (OLAP) data sources that you cannot perform through the Dashboard Designer user interface.

MORE INFORMATION

Filters

By using an MDX query in filters, you can provide your dashboard users with more relevant views based on their filter selections. For example, you can write an MDX query for the Region filter to display the top 10 products based on unit sales for the selected region. Or, you can write an MDX query for a multiselect Region filter to display the five cities that have the greatest sales amounts for each region that is selected.

The following chart uses a single filter to display the following views:
[GRAPHIC: ]
The filter uses an MDX query to display the children of the selected member to the view:

France, Germany, and Italy for Europe
Canada, Mexico, and the United States for North America

The simple MDX query that created the previous filter is shown in the follow chart:
[GRAPHIC: ]
In this chart, UniqueName represents the MDX syntax for the selected member. The appended Children operation is an MDX function that returns all items immediately under the selected item in the OLAP hierarchy.

In the following chart, the view returns the top two cities for each country that is selected:
[GRAPHIC: ]
Users can select one or more countries to compare with the multiselect parameter. The same view and filter creates the following grid by selecting different countries:
[GRAPHIC: ]
The following is the MDX query that is used to create the previous filter.

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 queries in KPI data mappings for target values, in which the target value is a calculation based on another member. For example, you want the Target value of a Sales KPI to be 10 percent more than it was during the same period last year. You may also have to use an MDX query to select a calculated member because this capability is not available in the selection tree.

By using the MDX PrevMember function, this MDX query returns the Sales Amount value for the prior time period multiplied by 10 percent.

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

See the following chart:
[GRAPHIC: ]
By using the MDX ParallelPeriod function, this MDX query returns the Sales Amount value for the same time period of the previous year multiplied by 20 percent.

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

The Analytic View Designer

The Analytic View Designer is displayed when you create an Analytic Chart or an Analytic Grid. The Analytic View Designer provides an MDX query mode. On the Query tab, users can provide advanced MDX queries for their views. See the following chart:
[GRAPHIC: ]
The MDX query mode is useful when you want to create an analytic view that cannot be created by using the drag-and-drop user interface on the Design tab. For example, you could use an MDX query to create a view that uses a top count filter. Users can provide any valid MDX query in this editor.

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

Keywords: kbhowto kbinfo kbgraphxlink kbexpertiseadvanced KB942514

Microsoft KB Archive/942514

Contents