416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Defining a Filter

Hide navigation
RSS
Modified on Wed, 03 Sep 2014 11:59 AM Categorized as Filtering, OLAP, Wizard
Click to return to: Documentation | Virtual Tables | Datasets | KPIs


Overview

This article shows how to define a data filter that can be applied to dataset column values, KPI measure values, KPI dimension values, or the column values of a virtual table. Filters let you select the data that ultimately appears on your dashboard. As an example, if you define a filter on a virtual table column that limits the data to the year 2008, any datasets or KPIs (and indirectly, dashboards) based on this virtual table will be able to access data only for that year.

Public versus private

A filter can be defined as being public or private. A public filter is accessible to the next stage in the dashboarding process. For example, if you create a public filter while configuring the dimensions of a KPI, that filter will be accessible during the dashboard design stage. This lets a dashboard designer tie the filter to a dashboard parameter, which can then be displayed on the dashboard in the form of an input control (⪚ dropdown list) for changing filter values interactively.

In short, make a filter public if you want it to be user-selectable.

Tip: A public filter with default settings is automatically created for each of your instant dimensions when you complete the KPI Setup Wizard.

Filter wizards

There are a number of wizards in the user interface of &dw; where filters can be defined:


The Data Filter Setup Wizard (for a virtual table column).

The Data Filter Setup Wizard (for a virtual table column).


Example

The dashboard in the figure below displays a KPI with a range filter defined on the Product ID instant dimension. The range filter is connected to a dashboard parameter, which appears on the dashboard in the form of two up-down controls for selecting a range of Product ID values.

A dashboard showing a KPI with a range filter.

A dashboard showing a KPI with a range filter.


Defining a data filter

Step 1 - Make the filter public if desired

To configure a public filter, follow these steps:

  1. Select the Public check box.
    Configuring a public filter.
  2. Enter a name for the filter in the Dashboard label box.
  3. Enter a description for the filter in the Description box.
  4. Select the (All) and open ranges allowed for value selection check box if you want to have the following user-selectable (or default value) options in your filter:
    • All values (which represents all possible filter values)
    • -Infinity (applies only to numeric column types)
    • +Infinity (applies only to numeric column types)
    • Beginning of time (applies to date-time columns)
    • End of time (applies to date-time columns).
  5. Select the (Default) token allowed as dashboard parameter selection check box if you want the Default filter value to be available as a parameter token.
  6. Select the (Null) allowed for value selection check box if you want to include the Null value as a user-selectable (or default value) option in your filter. Note that this choice will be unavailable (&ie; disabled) if your filter is for a KPI measure that uses aggregation (since the result of an aggregate function is never null).
  7. Select the (Not Null) allowed for value selection check box if you want to include the Not Null value as a user-selectable (or default value) option in your filter.

Note: If you choose the (All) and open ranges option, you will not be able to specify validation rules on filter values.

Step 2 - Choose the filter selection type

Choose the filter selection type as follows:

  1. Select the single value selections option if you want to create a single-valued filter.
  2. Select the range value selections option if you want to create a range filter.
    Range value selection.
    Note: The range value selection and multiple value selection options are mutually exclusive. You cannot choose both options at the same time.

  3. Select the multiple value selection option if you want to create a multi-valued filter. In this case, you will also need to select the single value option. The figure below shows a multi-valued filter rendered as a check box list on a dashboard.
    Multi-value input control.
  4. Click Next to go to the next screen of the wizard, which lets you use a look-up table or assign default values to your filter.
    The second screen of the Data Filter Setup Wizard.

Step 3 - Use a look-up table for filter values

You now have the option of associating filter values with values from a look-up table for display purposes (⪚ if you intend your filter to be public and user-selectable).

This option also applies to the setup of stored procedure/function/manual parameters as described in the article, Creating a Virtual Table. For example, the look-up table option lets you specify the possible values of a stored procedure parameter (for a stored procedure-based virtual table) without having to enter the values manually during the dashboard design stage.

The look-up table option cannot be used in conjunction with a range selection filter. Any look-up table settings that you apply here will be ignored if you also chose the range value selection option in the previous step.

To use a look-up table:

  1. Click the Choose a look-up table button in the wizard.
    Choosing a look-up table.
  2. The Look-up table dialog is displayed, which lists available virtual tables. Select a virtual table from the list to serve as a look-up table.
    Choosing a look-up table.
  3. Click OK. The dialog closes and the columns of the look-up (virtual) table that you selected are displayed in the wizard. Each row in the grid corresponds to a column from the look-up table.
    A look-up virtual table.
  4. The look-up table column that matches your filter values will be selected automatically in the Value column of the grid.
  5. Choose a look-up table column to provide the display name for your filter values by selecting the corresponding option in the Name column of the grid.
  6. Choose the sort order for your filter values by using the Sort order dropdown list. You can sort either by name or by value, and also choose which direction to sort (ascending or descending).

Step 4 - Set default filter values

To set a default value for your filter:

  1. Enter the default value in the box provided.
    A default filter value.
  2. Alternatively, if you are using a look-up table, click the Select a default value from the list button.
    The Select a default value from the list button.
  3. The Select default value dialog is displayed, which lets you choose a default value based on your look-up table values.
    The Select default value dialog.

Controls for entering default values

The actual controls for entering default values will vary depending on the type of filter selection you chose (&ie; single, range, or multiple), the data type of the column/measure/dimension that you are filtering on, and whether you selected the (All), (Null), or (Not Null) options in Step 1.

As an example, the figure below shows the default value controls for a range filter on an instant dimension of data type, DateTime. The controls let you choose absolute start and end date values via a date picker, or select relative date values from a dropdown menu. If you select a relative date value such as Today or Month to date, this implicitly defines a date range, and hence it is not necessary to set the end date value. Other relative date choices, such as Now or Beginning of current year, define a specific instant in time.

To ignore the time portion of date-time values, select the Ignore time check box. In this case, the precise end of the date range will be the end of the day that is implied by the Default end value.

Specifying a default date range for a filter on an instant DateTime dimension.

Specifying a default date range for a filter on an instant DateTime dimension.


As another example, the figure below shows the default value controls for a single value filter on an integer column whose values can be null.

Specifying a default value for a filter on a column that can contain null values.

Specifying a default value for a filter on a column that
can contain null values.


Step 5 - Define validation rules on filter values

You can define validation rules on filter values only if you have chosen a default value for your filter and you have not selected the (All) or open ranges allowed for value selection option. In this case, click Next to go to the next step and define validation rules as described in the article, Defining Validation Rules.

Defining an OLAP filter

The OLAP Filter Setup wizard lets you configure a filter for a measure/dimension/column of a KPI or dataset that is based on a virtual cube. You can access this wizard from:


The OLAP Filter Setup screen for an OLAP measure.

The OLAP Filter Setup screen for an OLAP measure.


The OLAP Filter Setup screen for an OLAP dimension.

The OLAP Filter Setup screen for an OLAP dimension.


Step 1 - Make the filter public if desired

To configure a public OLAP filter, follow these steps:

  1. Select the Public check box.
  2. Enter a name for the filter in the Dashboard label box.
  3. Enter a description for the filter in the Description box.
  4. Select the (All) member allowed check box if you want to have the (All) value as a user-selectable option in your filter.
  5. Select the (Default) token allowed as dashboard parameter selection check box if you want the Default filter value to be available as a parameter token.

Step 2 - Choose the filter selection type

Choose the filter selection type as follows:

  1. Select the single value selections option if you want to create a single-valued filter.
  2. Select the range value selections option if you want to create a range filter.
  3. Select the multiple value selection option if you want to create a multi-valued filter.

Step 3 - Set hierarchy levels and dimension grain

If you are filtering on an OLAP dimension, you can restrict the available hierarchy levels by choosing the desired Top Level and Bottom Level of the hierarchy.

If the dimension is also selected as the visualization axis:

  • You will be given the option of choosing the Default Dimension grain or level to use for grouping on the visualization axis. To enable interactive selection of the grain, ensure that the filter is Public, and then click the User selectable check box.
  • Select the Check grain values for children existence option if you want to add extra an column to your KPI/dataset to indicate whether child members of a hierarchy exist for each data point. This children information is intended primarily to be accessed from script. See Checking Grain Values for Children Existence for more details.
  • Select the Request ancestry fields option if you want to add extra columns to your KPI/dataset to display ancestry information, such as parent category values. In this case, also choose the top ancestor level that you want to be returned. The ancestry information is intended primarily to be accessed from script. See Requesting Ancestry Fields for more details.

If the dimension is not selected as the visualization axis:

  • You will have the option to Use MDX Subquery for SSAS data sources. If enabled, the filter expression will be put to the FROM clause instead of the WHERE clause, which may improve performance in some cases.

Use MDX Subquery option.

Use MDX Subquery option.


Step 4 - Set default filter values

The actual controls for entering default values will vary depending on the type of filter selection you chose (&ie; single, range, or multiple) and the data type of the measure/dimension that you are filtering on.

As an example, the figure below shows the Select value dialog, which lets you choose default values for a filter on the Product dimension.

Selecting a default value for an OLAP filter.

Selecting a default value for an OLAP filter.


Examples

Using a look-up table to provide filter values

This example shows how to use a look-up table to provide the values for a filter on an instant string dimension. The values will be loaded dynamically into a parameter dropdown list control that is connected to the filter. If new records are subsequently added to the look-up table, the dropdown list on the dashboard will automatically reflect the new filter values. For more details on dashboard parameters, see Adding Parameters.

Here are the steps for the example:

  1. Create a data connector for a database that has a Cities table.
    Cities table.
  2. Create and check-in a virtual table that is based on the Cities table.
    Virtual table.
  3. Create a KPI that is based on the [dbo].[Cities] virtual table:
    • Measure: Population
    • Instant dimension: City
  4. Create a filter on the City instant dimension:
    • Public: Yes
    • (All) and open ranges allowed for value section: Yes
    • The filter allows single value selections: Yes
  5. In the second step of the Data Filter Setup wizard, choose [dbo].[Cities] as a look-up table. This will allow the filter values to be loaded dynamically from the virtual table.
    Choose the look-up table.
  6. Check in the KPI.
  7. Create a new dashboard:
    • Drag the KPI to the canvas.
    • Create a dashboard parameter and connect it to the KPI's filter. In the last step of the wizard, ensure that the checkbox, Load names and values dynamically from the external virtual table, is selected.
      Load names and values dynamically.
    • Drag the parameter to the canvas. The parameter appears as a dropdown list control.
  8. Preview and test the completed dashboard.
    Completed dashboard 1.
  9. Add a new record to the Cities database table, then preview and test the dashboard again. Observe that the chart data and parameter dropdown list values are automatically updated to reflect the changes to the Cities table.
    Completed dashboard 2.

Apply filter on column or aggregation

The following feature is available in Dundas Dashboard 5.0.3.3 or later.

If the column you want to filter has an aggregation (⪚ SUM), there is an additional checkbox option The filter is applied on aggregation which lets you decide whether to apply the filter to the column before aggregation, or apply it to the aggregated column values.

This option is available for virtual table column filters, dataset metric column filters, and KPI measure filters.

By default, the checkbox is unchecked which means the filter will be applied on the column and not on the aggregated column. The resulting output query will have a WHERE [column=?] clause.

If the checkbox is checked, the filter will be applied on the column aggregation. The resulting output query will have a clause like this: HAVING [AGGREGATION(column)=?]

Option to filter on column or aggregated column of a virtual table.

Option to filter on column or aggregated column of a virtual table.


Note: If you are using an instance of Dundas Dashboard prior to version 5.0.3.3, apply a filter to an aggregated column, and then transfer the containing project to version 5.0.3.3 or later, the filter will be applied to the column by default, which means your dashboards will see different results. The workaround is to use the new checkbox option in 5.0.3.3 or later and make sure it is checked.

Related topics


Click to return to: Documentation | Virtual Tables | Datasets | KPIs

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.