Create a filter by using the MDX Query template in Dashboard Designer

 Note   Before you begin, make sure that you have already created a dashboard, and that you are familiar with both Multidimensional Expressions (MDX) and the SQL Server Analysis Services data cube that you want to use for your filter. For more information about MDX, see the Multidimensional Expressions (MDX) Reference on TechNet.

In PerformancePoint Dashboard Designer, you can create filters that make it easy for dashboard users to view specific information of interest to them. For example, you can create a Geography filter that dashboard consumers can use to view information that is specific to a particular region. Or, you can create a Product Categories filter that dashboard consumers can use to view information that is specific to a particular group of products.

When you create dashboard filters that you want to link to one or more reports in your dashboard, you typically begin by using the Create a Filter wizard. You use the wizard to select one of several templates, which vary by the method and the data source that you use to create your filter.

Select the MDX Query template to create a filter that works with data that is stored in SQL Server 2000 Analysis Services or SQL Server 2005 Analysis Services. When you create the filter, you must specify a Multidimensional Expressions (MDX) query that you want PerformancePoint Monitoring Server to use to retrieve a list of items for your filter.

What do you want to do?


Create an MDX Query filter

  1. In Dashboard Designer, click the Home tab, and then click Refresh to refresh the list of elements that are saved to Monitoring Server.
  2. In the Workspace Browser, click Dashboards. The center pane of the workspace displays two tabs: Server and Workspace. The Server tab lists all the dashboards that you and other dashboard authors have published to Monitoring Server. The Workspace tab lists all the dashboards that you have created or opened in your workspace.

    Click the Workspace tab to view the list of dashboards that are available in your workspace. If the dashboard that you want to edit is not listed, click the Server tab, and then double-click the dashboard to open it in your workspace.
  3. In the center pane of the workspace, click the Filters tab. In the Filters section, click New Filter. The Select a Dashboard Filter Template dialog box opens. Click MDX Query, and then click OK to open the Create a Filter wizard.
  4. In the Name box, type a name for your filter, and then type any descriptive information about the filter in the Description box. Click Next.
  5. In the Select a Data Source step of the wizard, select the Analysis Services data source that you want to use. If you do not see the data source listed, click Refresh to update the list. If you cannot find the data source, click Toggle View to see the available data sources arranged by their folder structure.

    After you select the data source you want to use for your filter, click Next.
  6. In the Enter MDX Formula step of the wizard, type the MDX query that you want to use for your filter.

    Your MDX statements should look something like the following examples, although your particular MDX query might vary, depending on the configuration of your data cube.
Use a query like this to do this
[Product].[Category].members 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 ten resellers in the United States.
  1.  Tip   For more information about MDX queries, see the Multidimensional Expressions (MDX) Reference on TechNet.

  2. After you type your MDX query, click Next.
  3. In the Choose Display Method step of the wizard, select one of the following display options for your filter:
    • List     This filter appears 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.
    • Tree     This filter appears as a list that includes an expandable tree control in a drop-down menu. When you use this display type, dashboard consumers click to expand the tree control, and then they select an individual item or a group of items to apply as a filter.
    • Multi-Select Tree     This filter appears as a list that includes an expandable tree control with check boxes in a drop-down menu. When you use this display type, dashboard consumers click to 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 a display type for your filter, click Finish.

  1. In the Confirmation screen, review the information in the Details pane to verify that your filter was successfully created. Then click Close to close the wizard.
  2. Click the Home tab, and then click Publish All to save your changes to Monitoring Server. You can now add your filter to a dashboard page and link it to reports and scorecards.

Top of Page Top of Page

Get more information

To learn more about how to add your filter to a dashboard page and link it to reports and scorecards, see Link filters to scorecards and reports.

To learn more about all the different types of filters that you can create using Dashboard Designer, see Creating filters in PerformancePoint Dashboard Designer.

To learn more about the overall process of creating a dashboard filter, see How do I create a dashboard filter in Dashboard Designer?

Top of Page Top of Page