Create a custom MDX filter using the Formula Editor dialog box

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.

In addition to creating standard filters such as these, you can create more sophisticated filters by using the Formula Editor. For example, you can configure a custom filter that displays a higher or lower level of detail than a standard filter. Or, you can configure the filter to display the top or bottom members of a group.

When you create a filter using the Formula Editor, you complete the process in three phases. First, you create a filter for the dashboard. Next, you add the filter to your dashboard page and link it to one or more scorecards or reports. Then, you use the Formula Editor to configure the filter. Depending on where you are in the process, you can start with any one of the following procedures.

What do you want to do?


 Note   The following procedure shows you how to create a filter that uses Named Sets. However, you can create many other types of filters by changing the expressions that you use in the Formula Editor. For more information, see About syntax for Time Intelligence expressions and About using MDX in Dashboard Designer.

Part I: Create a dashboard filter

 Note   This procedure assumes that you have already created a dashboard with at least one element such as a scorecard, analytic chart, or analytic grid, that uses SQL Server 2005 Analysis Services data.

  1. In Dashboard Designer, locate the Workspace Browser, and double-click the dashboard that you want to edit to open its workspace.
  2. In the center workspace pane, click the Filters tab, and then click New Filter.
  3. In the Select a Dashboard Filter Template dialog box, select Named Sets, and then then click OK to open the Create a Filter wizard.

 Note   We selected the Named Sets template for this procedure, but you can select any one of the following for your filter:

ShowMDX Query

You use the MDX Query template to create a custom filter. This template is appropriate for advanced users who are familiar with MDX language queries.


ShowNamed Sets

You use the Named Sets template to create a filter that uses one of the named sets that has been defined in the Analysis Services data cube.


ShowMember Selection

You use the Member Selection template to create a filter that uses a set of members that is defined in the Analysis Services data cube. You specify which dimension you want to use, and then you select individual members.


  1. In the Name the Filter page, type a name for your filter. You can also provide a basic description of your filter. Click Next.
  2. In the Select a Data Source page, select a data source from the list. Be sure to select the data source that is used by the report or scorecard you want to link to the filter. Click Next.
  3. In the Select Named Set page, use the Select named set list to pick a named set that has been defined in the data cube. Then click Next.
  4. In the Choose Display Method page, select one of the following display methods for your filter, and then click Finish.

ShowList

The List option displays your filter as a simple list in a drop-down menu. Dashboard users click an item in the list to specify what information to display.


ShowTree

The Tree option displays your filter as a list that includes an expandable tree control in a drop-down menu. Dashboard users click to expand the tree control, and then select an individual item or a group of items to apply as a filter.


ShowMulti-Select Tree

The Multi-Select Tree option displays your filter as a list that includes an expandable tree control with check boxes in a drop-down menu. Dashboard users click to expand the tree control, and then select the check boxes for the items that they want to use as a filter.


  1. Review the information in the Confirmation page, and then click Close. Click the Home tab, and then click Publish All. Your dashboard filter has been created.

 Note   When you create and publish your dashboard filter, you save it to PerformancePoint Monitoring Server. However, you must add it to a dashboard page and link it to one or more elements for the filter to have any effect on your dashboards.

Top of Page Top of Page

Part II: Link a filter to a scorecard or report

In this section, you add a filter to a dashboard page and link it to a scorecard or a report, such as an analytic chart or grid.

 Note   This procedure assumes that you have already created a dashboard that has at least one filter and an analytic chart, analytic grid, or scorecard that use Analysis Services data.

  1. In Dashboard Designer, locate the Workspace Browser, and double-click the dashboard that you want to edit to open its workspace. In the center workspace pane, click the Editor tab.
  2. In the Details pane, click to expand the Filters list. Drag the filter that you want to use to a dashboard zone.
  3. Click the down arrow next to the filter name in the dashboard zone, and then click Create Link. The Filter Link Editor dialog box opens.
  4. In the Filter Link Editor dialog box, click the Link Items tab. The name of the filter that you just added to the dashboard should be listed in the Filter box. If not, use the Filter list to select it. Then, use the Linked dashboard item list to select the report or scorecard that you want to link to the filter. Click the Link Options tab.
  5. In the Link Options tab, use the Dashboard item endpoint list to select the dimension that you want to use. This specifies where you want the results of the query to appear in the report. For example, suppose you have a report that contains product categories and measures such as gross profit and gross profit margin. Suppose further that you want to link the report to a filter that contains a list of different geographical regions. If you select Product Categories in the Dashboard item endpoint list, then the product categories information in your report is replaced with the information about the geographical regions that are selected in the filter. After you specify the Dashboard item endpoint, click OK. The Filter Link Editor dialog box closes.
  6. Click the Home tab, and then click Publish All. Your filter is now in your dashboard and is linked to a report or scorecard. The dashboard has been saved to Monitoring Server.

Top of Page Top of Page

Part III: Use the Formula Editor to specify how a filter works

In this section, you specify how a dashboard filter works by typing or editing a formula in the Formula Editor dialog box.

 Note   This procedure assumes that you have created a dashboard that includes a report or a scorecard and a filter that is linked to the report or scorecard.

  1. In Dashboard Designer, locate the Workspace Browser and double-click the dashboard that you want to edit. In the center workspace pane, click the Editor tab.
  2. In the center pane of the workspace, locate the filter that you want to edit. Click the down arrow next to the filter, and then click Edit Link to open the Filter Link Editor dialog box.
  3. Click the Filter Link Formula button to open the Formula Editor dialog box.
  4. In the Formula Editor dialog box, type an MDX statement to specify what you want the filter to do in the dashboard. The statement that you use can consist of one or more monikers, such as Member Name, which is written as <<uniquename>>. You can apply additional MDX syntax, as shown in the following examples:
    • To show the next level of detail, you can type <<uniquename>>.children
    • To show a higher level of detail, you can type <<uniquename>>.parent

 Note   There are many different expressions that you can type in the Formula Editor dialog box. For more information, see About syntax for Time Intelligence expressions and About using MDX in Dashboard Designer.

  1. When you finish typing your MDX statement, click OK to close the Formula Editor dialog box. Notice that a status icon now appears next to the Filter Link Formula button to indicate that a link formula has been entered.
  2. Click OK to close the Filter Link Editor dialog box. On the Home tab, click Publish All to save your changes to Monitoring Server.
  3. Redeploy your dashboard.

Top of Page Top of Page