416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
Dashboard v5.0

This site makes extensive use of JavaScript.

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

Dynamic Filters

Hide navigation
RSS
Modified on Thu, 01 May 2014 02:30 PM Categorized as Datasets, Filtering, KPIs
Click to return to: Documentation | Virtual Tables | Datasets | KPIs


Overview

With Dundas Dashboard 5 or later, you can create dynamic filters for a KPI or dataset and connect them to dashboard parameters. This gives users the ability to switch measures or dimensions on-the-fly while viewing a dashboard.

Choose the measure for a chart at viewing-time.

Choose the measure for a chart at viewing-time.


Using a dynamic filter to switch measures

The following example shows how to set up a dashboard that allows users to choose the measure they want to see displayed on a chart.

Set up the dynamic filter

For this example, use the Campaign Cost Summary virtual table which is included when you install the Sonatica sample from the Deployment Center.

Preview the Campaign Cost Summary virtual table.

Preview the Campaign Cost Summary virtual table.


Create a new KPI based on this virtual table with the following settings:

  • KPI name: New KPI
  • KPI measure: Downloads
  • KPI instant dimension: campaign_name (appears on visualization axis)

Follow these steps to add a dynamic measure filter:

  1. Go to the Measures and Contextual Metrics tab in the KPI Designer.
  2. In the Dynamic Filter column, click Create or edit dynamic filter for the Downloads measure.
    Create or edit dynamic filter button.
  3. In the Dynamic filter setup dialog:
    • Go to the Available columns grid and select the measures you want to appear as dynamic filter options. You must select at least two measures (e.g. Downloads and campaign_cost). If the measure you are looking for does not appear in the list of available columns, check to see if you've already added this measure to your KPI or dataset. If so, you must remove the measure from your KPI/dataset first.
    • For each measure chosen, set the appropriate Aggregator function (e.g. SUM).
    • You can also set the Display Name for each measure by typing directly in the grid. Display names will appear in the options list of the corresponding dashboard parameter.
    • Set the Default value dropdown to the measure you want as the default for the filter.
    • Enter the Dashboard label text for the filter. This will appear as the name of the filter when you go to set up the corresponding dashboard parameter.
      Dynamic filter setup dialog.
    • Click OK to complete the dialog.

The dynamic filter is now added to the KPI. You can edit the filter settings or remove the filter using the buttons in the Dynamic Filter grid column.

Dynamic filter for measures.

Dynamic filter for measures.


Note: Adding a dynamic measure filter will disable and replace any existing data filter on the measure.

Next, go to the Preview tab to preview the KPI data. You can reconfigure the dynamic filter for the Campaign Metric from this tab, choose a different default measure, and then preview the data again to see updated values in the Downloads column. Note that the name or label of the original Downloads measure column is not changed, only the underlying data is switched dynamically.

Preview the dynamic KPI.

Preview the dynamic KPI.


Create the dashboard and parameter

Next, create a new dashboard and connect the dynamic filter to a dashboard parameter as follows:

  1. Drag the New KPI item from the toolbox to the dashboard canvas. The KPI appears as a bar chart.
  2. Go to the Parameters toolbox and add a new dashboard parameter:
    • In the first step of the Set Up a Dashboard Parameter wizard, select the Filter type.
      Set Up a Dashboard Parameter step 1.
    • In the second step of the wizard, select the KPI in the top grid and then select the dynamic filter in the bottom grid.
      Set Up a Dashboard Parameter step 2.
    • The third step of the wizard tells you the parameter control will be a dropdown list. You can set the default value (measure) here if you like.
      Set Up a Dashboard Parameter step 3.
    • Click Finish to complete the wizard.
  3. Drag the newly created parameter from the Parameters toolbox to the dashboard canvas. The parameter appears as a dropdown list.

You can now preview the resulting dashboard and use the dropdown list to switch measures at viewing-time.

Choose the measure for a chart at viewing-time.

Choose the measure for a chart at viewing-time.


Using dynamic filters to switch measures and dimensions

The following example shows how to set up a dashboard that allows users to choose the measure and dimension they want to see displayed on a chart.

Set up the dynamic filters

For this example, use the Financials - Year Over Year virtual table which is included when you install the Sonatica sample from the Deployment Center.

Preview the Financials - Year Over Year virtual table.

Preview the Financials - Year Over Year virtual table.


Create a new KPI based on this virtual table with the following settings:

  • KPI name: New KPI
  • KPI measure: Revenue
  • KPI instant dimension: MonthTime (appears on visualization axis)

Follow these steps to add a dynamic measure filter:

  1. Go to the Measures and Contextual Metrics tab in the KPI Designer.
  2. In the Dynamic Filter column, click Create or edit dynamic filter for the Revenue measure.
  3. In the Dynamic filter setup dialog:
    • Go to the Available columns grid and select the Revenue and PreviousRevenue measures. If a measure you are looking for does not appear in the list of available columns, check to see if you've already added this measure to your KPI or dataset. If so, you must remove the measure from your KPI/dataset first.
    • For each measure chosen, set the appropriate Aggregator function (e.g. SUM).
    • Set the Default value dropdown to the Revenue measure.
    • Set the Dashboard label text for the filter to Measure Filter.
      Dynamic filter setup for Revenue.
    • Click OK to complete the dialog.

The dynamic filter is now added to the KPI. You can edit the filter settings or remove the filter using the buttons in the Dynamic Filter grid column.

Follow these steps to add a dynamic dimension filter:

  1. Go to the Dimensions tab in the KPI Designer.
  2. In the Dynamic Filter column, click Create or edit dynamic filter for the MonthTime instant dimension.
  3. In the Dynamic filter setup dialog:
    • Go to the Available columns grid and select the MonthTime and PrevYearMonthTime dimensions. Note that the grid only lists compatible dimensions based on data type. If the dimension you are looking for does not appear in the list of available columns, check to see if you've already added this dimension to your KPI or dataset. If so, you must remove the dimension from your KPI/dataset first.
    • For each dimension, optionally set the Display Name by typing directly in the grid. Display names will appear in the options list of the corresponding dashboard parameter.
    • Set the Default value dropdown to the MonthTime dimension.
    • Set the Dashboard label text for the filter to Dimension Filter.
      Dynamic filter setup for MonthTime.
    • Click OK to complete the dialog.

The dynamic filter is now added to the KPI. You can edit the filter settings or remove the filter using the buttons in the Dynamic Filter grid column.

Note: Adding a dynamic dimension filter will disable and replace any existing data filter on the dimension.

Create the dashboard and parameters

Next, create a new dashboard and connect the two dynamic filters to dashboard parameters as follows:

  1. Drag a line chart from the toolbox to the dashboard canvas.
  2. Drag the New KPI item to the line chart.
  3. Go to the Parameters toolbox and add a new dashboard parameter:
    • In the first step of the Set Up a Dashboard Parameter wizard, select the Filter type.
    • In the second step of the wizard, select the KPI in the top grid and then select the Measure Filter in the bottom grid.
    • The third step of the wizard tells you the parameter control will be a dropdown list. You can set the default value (measure) here if you like.
    • Click Finish to complete the wizard.
  4. Go to the Parameters toolbox and add a second dashboard parameter:
    • In the first step of the Set Up a Dashboard Parameter wizard, select the Filter type.
    • In the second step of the wizard, select the KPI in the top grid and then select the Dimension Filter in the bottom grid.
      Set Up a Dashboard Parameter step 2 for dimension filter.
    • The third step of the wizard tells you the parameter control will be a dropdown list. You can set the default value (dimension) here if you like.
    • Click Finish to complete the wizard.
  5. Drag the newly created parameters from the Parameters toolbox to the dashboard canvas. The parameters appear as dropdown lists.

You can now preview the resulting dashboard and use the dropdown lists to switch measures and/or dimensions at viewing-time.

Choose the measure or dimension for a chart at viewing-time.

Choose the measure or dimension for a chart at viewing-time.


Using a dynamic filter to switch splitter dimensions

The following example shows how to set up a dashboard that allows users to choose the splitter dimension for a chart.

Set up the dynamic filter

For this example, assume the availability of a virtual table, ProductDownloadsVT, which has the following relevant columns:

  • Number of Downloads
  • Download Date
  • Product Group Name
  • Product SKU

The latter two columns will be used as options for a splitter dimension.

Preview of the ProductDownloadsVT virtual table.

Preview of the ProductDownloadsVT virtual table.


Create a new KPI based on this virtual table with the following settings:

  • KPI name: New KPI
  • KPI measure: Number of Downloads
  • KPI instant dimension: Download Date (appears on visualization axis)
  • KPI instant dimension: Product Group Name (appears on visualization axis, set as splitter dimension)

Initial axis and splitter setup for the KPI.

Initial axis and splitter setup for the KPI.


Follow these steps to add a dynamic splitter filter:

  1. Go to the Dimensions tab in the KPI Designer.
  2. In the Dynamic Filter column, click Create or edit dynamic filter for the Product Group Name instant dimension.
  3. In the Dynamic filter setup dialog:
    • Go to the Available columns grid and select the Product Group Name and Product SKU dimensions. If the dimension you are looking for does not appear in the list of available columns, check to see if you've already added this dimension to your KPI or dataset. If so, you must remove the dimension from your KPI/dataset first.
    • Set the Default value dropdown to the Product Group Name dimension.
    • Set the Dashboard label text for the filter to Splitter Filter.
      Dynamic filter setup for splitter dimension.
    • Click OK to complete the dialog.

The dynamic filter is now added to the KPI. You can edit the filter settings or remove the filter using the buttons in the Dynamic Filter grid column.

Create the dashboard and parameter

Next, create a new dashboard and connect the dynamic splitter filter to a dashboard parameter as follows:

  1. Drag a line chart from the toolbox to the dashboard canvas.
  2. Drag the New KPI item to the line chart.
  3. Go to the Parameters toolbox and add a new dashboard parameter:
    • In the first step of the Set Up a Dashboard Parameter wizard, select the Filter type.
    • In the second step of the wizard, select the KPI in the top grid and then select the Splitter Filter in the bottom grid.
    • The third step of the wizard tells you the parameter control will be a dropdown list. You can set the default value (splitter dimension) here if you like.
    • Click Finish to complete the wizard.
  4. Drag the newly created parameter from the Parameters toolbox to the dashboard canvas. The parameter appears as a dropdown list.

You can now preview the resulting dashboard and use the dropdown list to switch the splitter dimension at viewing-time.

Splitter dimension set to Product Group Name.

Splitter dimension set to Product Group Name.


Splitter dimension set to Product SKU.

Splitter dimension set to Product SKU.


Setting a Dynamic Filter value by Script

When setting a Dynamic Filter value programmatically, you need to use the Dynamic Filter's column GUID. For example:

DynMeasure.SingleValue = "bbccefb6-40f2-4736-89f0-cc3200f6a135";
DynMeasure.InvalidateFilter();

Tip: To get the Dynamic Filter's column GUID, you can use the #TRACE directive. Detailed steps below:
  1. Preview the dashboard.
  2. Open the Script Console.
  3. Select the desired dynamic filter value from the dropdown.
  4. Add the #TRACE script below.
    #TRACE DynMeasure.SingleValue;
  5. Build/Run the script.
  6. Get the resulting GUID.

Steps to trace/retrieve the Dynamic Filter's column GUID.

Steps to trace/retrieve the Dynamic Filter's column GUID.



Notes and limitations

  • A dynamic filter can only be applied to an instant dimension that appears on the visualization axis. If an instant dimension has been replaced with a full dimension, a dynamic filter cannot be applied.
  • Annotations and notifications are not supported for KPIs/datasets that have a dynamic filter.
  • Dynamic filters are not available for OLAP/virtual cube-based dimensions.
  • In the Dynamic filter setup dialog, if the measure/dimension you want as a dynamic filter option does not appear in the Available columns grid, check to see if you've already added this measure/dimension to your KPI/dataset. If so, you must remove the measure/dimension from the KPI/dataset first.
  • In the Set Up a Dashboard Parameter wizard, if you connect the parameter to two dynamic filters that are incompatible and then go to view or preview the dashboard, the parameter control (dropdown) will only display options corresponding to the last filter in the wizard.

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.