Creating filters in Dashboard Designer

Filters are dashboard elements that you can create and attach to scorecards and reports to restrict the types of data that you want to display. Some types of filters make it easy for dashboard users to customize the information that they want to see; other types of filters are more rigid and display particular information by default.

For example, suppose that you create dashboards for a sales manager who uses dashboards to track sales information. To help the manager focus on specific information, you might create a Geography filter to display the sales amounts for a particular geographical region. Or, you might create another filter to display information about the highest and lowest performing sales representatives. You could also include a scorecard in your dashboard that is linked to one or more reports. Then, the sales manager could click a value in your scorecard to view more detailed information about that value in the linked reports.

What do you want to learn about?


Types of filters that you can create

Dashboard filters vary in appearance and functionality, but typically belong to one of the following groups:

ShowIndividual elements such as lists or expandable trees.

Create these filters to make it easy for each dashboard user to view the specific information of interest to him or to her.

For example, suppose you create a dashboard that shows the gross sales amounts for your organization. Although the various sales teams find your dashboard useful, what they actually want to see are the sales amounts for particular teams, not sales totals overall. In response, you create a Teams filter for the dashboard and link it to applicable scorecards and reports. By using the filter, team members can select a team from the list and display sales information for that team.

When you create dashboard filters that are individual elements, you select one of the following display types:

  • List    This filter appears as a simple list in a drop-down menu. Dashboard consumers click an item in the list to specify what information to display. For example, suppose that you create a dashboard that contains sales information for a toy manufacturer. By default, the dashboard displays information for all the toys the manufacturer produces, but the dashboard also includes a Category filter that lists various items such as Games, Dolls, Bicycles, and Crafts. When a user selects a particular item on the list, the scorecards and reports that are linked to the filter automatically refresh to display information exclusively for that category.
  • Tree    This filter appears as a list that includes an expandable tree control in a drop-down menu. Dashboard consumers click to expand the tree control, and then select an individual item or a group of items to apply as a filter. For example, suppose that you create a dashboard that contains sales information for a car dealer who has several different teams. By default, the dashboard displays information for all the teams, but the dashboard also includes a Team filter. The filter displays the individual teams in a tree with All Teams at the top level, and the individual teams listed as branches underneath. When a dashboard user selects a team in the tree, the scorecards and reports that are linked to the filter automatically refresh to display information exclusively for that team.
  • Multi-Select Tree    This filter appears as a list that includes an expandable tree control with check boxes in a drop-down menu. Dashboard consumers click to expand the tree control, and then select the check boxes for the items that they want to use as a filter. For example, suppose that you create a dashboard to display marketing information for an advertising agency. By default, your dashboard displays information about leads that were generated through marketing activities over several years, but the dashboard also contains a Time filter. The Time filter lists different time periods in a tree with Years at the top level, Quarters at the next level underneath Years, and Months at the third level underneath Quarters. Because this filter uses a multi-select tree display, dashboard consumers can select any combination of months, quarters, or years, and apply those selections as a single filter. When a dashboard user selects one or more items in the tree, the scorecards and reports that are linked to the filter automatically refresh to display information for those time periods.

ShowHidden queries or background items in reports or scorecards

Create these filters to display very specific information that you know will be of interest to dashboard consumers. For example, suppose that you want to create an analytic chart that displays the sales amounts for the top ten sales representatives in your organization. Suppose further that you want the dashboard to display these results by default, and not just when dashboard users apply a filter. Your solution is to write a custom multidimensional expression (MDX) query to display the results in a chart. Your query includes a calculation that a custom MDX statement performs. Dashboard consumers do not see the query itself, only the information that is displayed in the chart. In this way, your filter is hidden within your custom MDX query.

As another example, suppose that you want to create an analytic chart that displays information about three of five possible dimension members. Your solution is to put the three dimension members in the background of the analytic chart, which causes the chart to display information for just the three dimension members.

To explain this concept further, when you create an analytic chart, you can put dimension members into the Series, Bottom Axis, or Background sections. Dashboard consumers can see which dimension members and measures are in the Series and Bottom Axis sections, but they do not necessarily see what is in the Background of an analytic chart unless they apply the Show Information feature. Background items act as hidden filters for the chart. For example, if you put a Named Set called Core Products in the Background, you display information that is specific to the Core Products in the chart.

 Note   Dashboard consumers might not be able to navigate to see higher or lower levels of detail in analytic charts and grids that have custom MDX queries or background selections. Thus, you must consider whether dashboard consumers need that functionality in the reports.


ShowKPIs in a scorecard that are linked to other reports

Create these links from one or more key performance indicators (KPIs) to other reports, such as analytic charts, analytic grids, and SQL Server 2005 Reporting Services reports. You would typically create the links to connect individual numeric values to other reports that provide more information about the numeric values.

For example, suppose that you create a dashboard that contains a scorecard. The scorecard displays the gross profit amounts for several product categories, and gives the dashboard consumer a quick way to assess whether the overall gross profit amount is on or off target. Now suppose that the dashboard users also want to see gross profit amounts for individual product categories over specific periods of time.

A single scorecard that contains all this information might be too large and overwhelm the user. Your solution is to create a link from each KPI to two different reports. The first report is a line chart that shows gross profit amounts for each product category over specific time periods. The second report is a bar chart that shows gross profit amounts for various product categories and the primary manufacturers within each product category. Dashboard users can click a scorecard KPI and refresh the two analytic reports simultaneously to display the information that is specific to the KPI.

When you link a KPI to other reports such as analytic charts and grids, or SQL Server 2005 Reporting Services reports, the KPI serves as an individual filter for the reports. Then, dashboard consumers can quickly view additional information about the KPI.


Top of Page Top of Page

Dashboard filter templates

To create dashboard filters that appear as individual dashboard elements, use the Create a Filter wizard and select a template for your filter. The following table describes the different filter templates that are available in Dashboard Designer.

Select this template to do this using this data
MDX Query

Create a filter by specifying a Multidimensional Expressions (MDX) query that Monitoring Server uses to create a list of items in your filter.

 Note   Before you use this template, you should be familiar with the database and with MDX. For more information about MDX, see the Multidimensional Expressions (MDX) Reference on TechNet.

  • SQL Server 2005 Analysis Services
  • SQL Server 2000 Analysis Services
Member Selection Create a filter by selecting individual members in your data source.
  • Analysis Services
  • Excel Services
  • Excel 2007 workbook
  • SharePoint list
  • SQL Server table

 Note   The data source you select for your filter must contain dimension members.

Named Sets Create a filter by selecting a named set, which is a group of members that is defined in the database.
  • SQL Server 2005 Analysis Services
  • SQL Server 2000 Analysis Services
Tabular Values Create a filter by selecting members that are in a table.
  • Excel Services
  • Excel 2007 workbook
  • SharePoint list
  • SQL Server table
Time Intelligence

Create a Time filter by specifying a formula that uses the Simple Time Period Specification (STPS) syntax. Your filter must contain a list of time periods, such as a number of most recent years, months, or dates. When you link your filter to scorecards and reports, you can display information for those time periods in your dashboard.

 Note   Before you use this template, you should be familiar with the database and with STPS. For more information on STPS, see About syntax for Time Intelligence expressions.

Any data source that meets the following conditions:

  • The data source contains a time dimension that contains the time periods you want to use in your filter.
  • The time and aggregation settings for the data source have been configured to work with Time Intelligence formulas. For more information, see Configure time and aggregation settings.
Time Intelligence Post Formula

Create a Time filter by specifying a formula that uses the STPS syntax. Use the filter, which appears as a calendar control in the dashboard, to display different time periods in various reports and scorecards. For example, you can use a single Time Intelligence Post Formula filter to display data for years in one report or scorecard, and months in another.

 Note   Before you use this template, you should be familiar with the database and STPS. For more information on STPS, see About syntax for Time Intelligence expressions.

Any data source that meets the following conditions:

  • The data source contains a time dimension that contains the time periods you want to use in your filter.
  • The time and aggregation settings for the data source have been configured to work with Time Intelligence formulas. For more information, see Configure time and aggregation settings.

 Note   When you create a dashboard filter, you must select a data source that contains similar data types to the data types in your reports and scorecards. A filter can only act on the types of data that it has in common with the dashboard elements that are linked to it. For example, suppose you create a Geography filter that includes a list of countries or regions. Suppose further that you want to link that filter to an analytic chart, an Excel Services report, and a Reporting Services report. For the filter to work, all three reports must use data sources that include the same countries or regions.

Top of Page Top of Page

Next steps

To see the overall process of how to create a dashboard filter, see How do I create a dashboard filter in Dashboard Designer?.

To learn how to connect a dashboard filter to other dashboard elements, see Link filters to scorecards and reports.