Create MDX Query filters by using Dashboard Designer
Published: October 2, 2012
Summary: Learn how to create MDX query filters using PerformancePoint Services Dashboard Designer.
Applies to: SharePoint Server 2013
When you use PerformancePoint Dashboard Designer to create a dashboard filter, you can select from various filter templates that include the MDX Query filter template. You can use the MDX Query template to create a list of items by specifying a Multidimensional Expressions (MDX) query to select a group of items in a database.
The MDX Query template works with data that is stored in SQL Server Analysis Services. For more information about dashboard filters, see Overview of PerformancePoint dashboard filters and connections.
Before you begin to create a filter by using an MDX query, make sure that you are familiar with MDX. For more information about MDX queries, see Multidimensional Expressions (MDX) Reference (http://go.microsoft.com/fwlink/p/?LinkId=185458).
To create an MDX Query filter
In Dashboard Designer, in the Workspace Browser, click PerformancePoint Content.
On the Create tab, click Filter.
The Select a Filter Template dialog box opens.
In the Select a Filter Template dialog box, click MDX Query, and then click OK.
The Create a Filter wizard opens.
On the Select a Data Source page, select the Analysis Services data source that you want to use, and then click Next.
Use the SharePoint Site tab to view a list of data sources that have been saved to SharePoint Server 2013.
Use the Workspace tab to view a list of data sources that you have created or used.
On the Enter MDX Formula page, type an MDX query to select the items that you want to use for the filter.
The MDX query that you select might resemble the examples that are shown in the following table.
Create a list of the product categories that are in the Products dimension in the data cube.
TOPCOUNT([Reseller].[Reseller].[Reseller].MEMBERS.10.([Geography].[Country].[United States]. [Measures].[Reseller Sales Amount]))
Create a list of the top 10 resellers in the United States.
On the Select Display Method page, select one of the display types shown in the following table.
Display method Description
Select this option to display the filter as a simple list in a drop-down menu. When you use this display type, dashboard consumers click an item in the list to specify what information to display.
Select this option to display the filter as a list that includes an expandable tree control in a drop-down menu. When you use this display type, dashboard consumers expand the tree control, and then they select one item or a group of items to apply as a filter.
Select this option to display the filter as a list that includes an expandable tree control that has check boxes in a drop-down menu. When you use this display type, dashboard consumers expand the tree control, and then they select the check boxes for the items that they want to use as a filter.
After you select the display type that you want to use for the filter, click Finish.
In the center pane, click the Properties tab.
In the Name box, type the name that you want to use for the filter.
(This step is optional.) To specify a location for the filter, click the Display Folder button, and then select or create a folder.
In the Workspace Browser, right-click the filter, and then click Save.
After you have created the filter, you can add it to a dashboard page, and then connect it to one or more dashboard items. For more information, see Connect filters to reports or scorecards by using Dashboard Designer (SharePoint Server 2013).