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.

Associating a Dimension with External Filters

Hide navigation
RSS
Modified on Thu, 20 Mar 2014 04:40 PM Categorized as Dimensions, Filtering, Level-Advanced, Level-Expert, Parameters, Scripting
Click to return to: Documentation | Defining KPIs


This is an advanced topic for analysts who are familiar with DundasScript and creating stored procedures at the database-level.

Overview

When you create a KPI or dataset based on a virtual table that exposes public filters (on columns, stored procedure parameters, or manual parameters), those public filters appear as external child structure filters in the KPI/Dataset Preview tab.

These external filters in the Preview tab can be replaced with an existing full dimension, giving you the following benefits:

  • If your virtual table is based on a stored procedure, an ODBC data provider with the Is Restricted flag enabled, or a non-relational data provider, Dundas Dashboard loads all of the data into temporary tables first. If there is a large number of records, this could be a performance bottleneck. However, by using external filter replacement, you can force the virtual table to return only the relevant records instead of all of the data.

  • When you simply expose stored procedure parameter filters to the dashboard-level without using external filter replacement, those parameter filter values are ignored when creating user annotations because they are not tied to any dimension.

  • If your stored procedure accepts start and end date parameters, the script-based approach gives you the flexibility to format dates exactly as required by your stored procedure. For example, your stored procedure may be expecting dates formatted in a non-standard way. Or, your stored procedure may be using a different convention than Dundas Dashboard for specifying the end date of a date range. (In Dundas Dashboard, the end date of a date range is interpreted as an exclusive value - for example, the end of June is stored as July 1, 00:00).

  • Multi-selection values for stored procedure parameters are made possible by passing in a string which contains multiple values separated by delimiter characters.

Replacing an external filter with a standard dimension

The following example shows how to replace an external filter with a standard/regular dimension.

Install the sample database

Install the AdventureWorks 2008 or 2012 Database.

Add a new stored procedure

Create a new stored procedure for the sample database as listed below. This stored procedure returns sales and product ID values given two inputs:

  1. levelNumber - An integer which can be 0, 1, or 2, representing the hierarchy level.
  2. filterId - This can be a Category ID, Subcategory ID, or Product ID for filtering.

USE [AdventureWorks2012] GO

/****** Object: StoredProcedure [dbo].[uspGetSalesForProduct] ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE PROCEDURE [dbo].[uspGetSalesForProduct] @levelNumber int, @filterId int AS BEGIN SET NOCOUNT ON;

IF (@filterId IS NULL) BEGIN SELECT [ProductID], [LineTotal] FROM [Sales].[SalesOrderDetail] END IF (@levelNumber = 0) BEGIN SELECT [ProductID], [LineTotal] FROM [Sales].[SalesOrderDetail] WHERE [ProductID] IN (SELECT [ProductID] FROM [Production].[ProductCategory] c INNER JOIN [Production].[ProductSubcategory] s ON c.ProductCategoryID = s.ProductCategoryID INNER JOIN [Production].[Product] p ON s.ProductSubcategoryID = p.ProductSubcategoryID WHERE (c.ProductCategoryID = @filterId)) END ELSE IF (@levelNumber = 1) BEGIN SELECT [ProductID], [LineTotal] FROM [Sales].[SalesOrderDetail] WHERE [ProductID] IN (SELECT [ProductID] FROM [Production].[ProductSubcategory] s INNER JOIN [Production].[Product] p ON s.ProductSubcategoryID = p.ProductSubcategoryID WHERE (s.ProductSubcategoryID = @filterId)) END ELSE IF (@levelNumber = 2) BEGIN SELECT [ProductID], [LineTotal] FROM [Sales].[SalesOrderDetail] WHERE [ProductID] = @filterId END END

GO


Create a data connector

Create a data connector AW DC for the sample database and view the GetSalesForProduct stored procedure.

Viewing the GetSalesForProduct stored procedure.

Viewing the GetSalesForProduct stored procedure.


Create a virtual table for the standard dimension

Create a virtual table which will serve as the base for a standard dimension:

  1. Create a new virtual table ProductVT.
  2. Select the standard virtual table option.
  3. Select the AW DC data connector.
  4. Select the data structures:
    • [Production].[Product]
    • [Production].[ProductCategory]
    • [Production].[ProductSubcategory]
  5. Go to the Data Sources tab and verify that data relationships have already been added automatically.
  6. Go to the Columns tab. Hide, re-order, and set the friendly name of columns so that only the following columns are visible:
    • Product Category ID
    • Product Category Name
    • Product Subcategory ID
    • Product Subcategory Name
    • Product ID
    • Product Number
    • Product Name

This virtual table is only needed to set up the standard dimension. It will not be used to create the KPI.

Create the standard dimension

Create a standard dimension called Product which is based on the ProductVT virtual table.

Preview of Product dimension.

Preview of Product dimension.


This is the dimension that will be used for external filter replacement.

Create a programmatic virtual table

Create a virtual table that is based on the GetSalesForProduct stored procedure:

  1. Create a new virtual table SalesForProductVT.
  2. Select the programmatic virtual table option.
  3. Select the AW DC data connector.
  4. Select the data structure [dbo].[uspGetSalesForProduct].
  5. Go to the Preview tab.
  6. Click the Set value filtering button for each of the native parameters listed and make them Public.

Preview of SalesForProductVT virtual table.

Preview of SalesForProductVT virtual table.


Create a KPI

Create a new KPI Total Sales which is based on the SalesForProductVT virtual table:

  1. Select SalesForProductVT as the base data structure.
  2. Choose Line Total as the measure.
  3. Click Finish without choosing any dimensions.

Replace the external filter

Next, go to the KPI Preview tab and click the Replace the external filter with an existing Regular Dimension button for filterId.

KPI Preview tab.

KPI Preview tab.


In the Standard Dimension Setup wizard, select the Product dimension. Click Next.

Select Existing Dimension step.

Select Existing Dimension step.


Preview the chosen dimension hierarchy. Click Next.

Preview Dimension Hierarchy step.

Preview Dimension Hierarchy step.


In the Configure Standard Dimension step, accept the default settings and click Next.

Configure Standard Dimension step.

Configure Standard Dimension step.


In the Configure External Filters step:

  1. Select the checkbox for levelNumber and filterId. These are the parameters of the stored procedure.
  2. Set a value for each of the parameters based on the current dimension value, using scripts. A number of helper properties and methods are provided to make it easier to write these scripts. In many cases, you simply need to return the appropriate property value. For more complex scenarios, scripting gives you the flexibility to adapt to the precise format expected by your stored procedure.
    • Click the Script button for levelNumber and enter this script:


      return dimensionValue.SingleValueHierarchyLevel;
    • Click the Script button for filterId and enter this script:


      return dimensionValue.SingleValue;
  3. Click Finish.

Configure External Filters step.

Configure External Filters step.


See also: DimensionFilterValue Class

Set up the dashboard

Create a new dashboard as follows:

  1. Drag a Radial Gauge control from the toolbox to the canvas.
  2. Drag the Total Sales KPI from the toolbox and drop it onto the gauge.
  3. Add a dashboard filter parameter and connect it to the Product filter of the KPI. Choose the Hierarchy parameter control type, and then drag the parameter from the toolbox to the canvas.
  4. Save and preview the dashboard.

The resulting dashboard shows the total sales figure which is filtered by the hierarchy control. Using the hierarchy control passes in the correct parameter values (levelNumber and filterId) to the GetSalesForProduct stored procedure.

The completed dashboard.

The completed dashboard.


Replacing an external filter with a time dimension

The following example shows how to replace an external filter with a time dimension. The steps are similar to the standard dimension case with just a few differences.

Add a new stored procedure

Create a new stored procedure for the sample database as listed below. This stored procedure returns sales, product ID, and modified date values given two inputs:

  1. startDate - The start date of a date range for filtering.
  2. endDate - The end date of a date range for filtering.

USE [AdventureWorks2012] GO

/****** Object: StoredProcedure [dbo].[uspGetSalesForDateRange] ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE PROCEDURE [dbo].[uspGetSalesForDateRange] @startDate datetime, @endDate datetime AS BEGIN SET NOCOUNT ON;

SELECT [ProductID], [ModifiedDate], [LineTotal] FROM [Sales].[SalesOrderDetail] WHERE (@startDate IS NULL OR [ModifiedDate] >= @startDate) AND (@endDate IS NULL OR [ModifiedDate] < @endDate) END

GO


Create a data connector

Create a data connector AW DC for the sample database and view the GetSalesForDateRange stored procedure.

Create the time dimension

Create a time dimension called Time with the following settings:

  • Start date: 2005-01-01
  • End date: 2009-12-31
  • Time Periods: Year, Quarter, and Month
  • Calendar: Gregorian

This is the dimension that will be used for external filter replacement.

Create a programmatic virtual table

Create a virtual table that is based on the GetSalesForDateRange stored procedure:

  1. Create a new virtual table SalesForDateRangeVT.
  2. Select the programmatic virtual table option.
  3. Select the AW DC data connector.
  4. Select the data structure [dbo].[uspGetSalesForDateRange].
  5. Go to the Preview tab.
  6. Click the Set value filtering button for each of the native parameters listed and make them Public.

Preview of SalesForDateRangeVT virtual table.

Preview of SalesForDateRangeVT virtual table.


Create a KPI

Create a new KPI Total Sales which is based on the SalesForDateRangeVT virtual table:

  1. Select SalesForDateRangeVT as the base data structure.
  2. Choose Line Total as the measure.
  3. Click Finish without choosing any dimensions.

Replace the external filter

Next, go to the KPI Preview tab and click the Replace the external filter with an existing Time Dimension button for startDate.

KPI Preview tab.

KPI Preview tab.


In the Configure External Filters step:

  1. Select the checkbox for startDate and endDate. These are the parameters of the stored procedure.
  2. Set a value for each of the parameters based on the current dimension value, using scripts. If you have a stored procedure that expects dates in a custom format, you can modify these scripts however you like to produce the expected format.
    • Click the Script button for startDate and enter this script:


      return dimensionValue.DateRangeStart;
    • Click the Script button for endDate and enter this script:


      return dimensionValue.DateRangeEnd;
  3. Click Finish.

Configure External Filters step.

Configure External Filters step.


See also: DimensionFilterValue Class

Set up the dashboard

Create a new dashboard as follows:

  1. Drag a Radial Gauge control from the toolbox to the canvas.
  2. Drag the Total Sales KPI from the toolbox and drop it onto the gauge.
  3. Add a dashboard filter parameter and connect it to the Time filter of the KPI. Choose the Calendar (Range) parameter control type, and then drag the parameter from the toolbox to the canvas.
  4. Save and preview the dashboard.

The resulting dashboard shows the total sales figure which is filtered by the calendar range control. Using the calendar passes in the correct parameter values (startDate and endDate) to the GetSalesForDateRange stored procedure.

The completed dashboard.

The completed dashboard.


Multiple selection

External filter replacement lets you handle multi-selection scenarios such as the following:

  • You have defined a stored procedure that accepts a string parameter. This stored procedure expects the parameter value to be a concatenation of multiple string values separated by a delimiter character, such as a comma. It is up to your stored procedure to tokenize or split the string and use the resulting values to retrieve the appropriate data.

  • During replacement of the external filter, you have enabled the option The filter allows multiple value selections. Also, when configuring the external filter, you use the following script to set the stored procedure parameter value. The ToString method is a helper function which concatenates all of the selected dimension values into a single string, using a comma as a delimiter by default. An overloaded version of this method lets you specify the delimiter character.

    return dimensionValue.ToString();

  • When you configure a Hierarchy parameter control on your dashboard, you have enabled the option Allow multiple selection. This will result in checkboxes appearing beside items displayed in the hierarchy control, which allows you to select multiple items.

The figure below shows a dashboard that corresponds to the above multiple selection scenario. When multiple items are selected via the hierarchy control, a string concatenation of the selected Product IDs is passed in to the stored procedure. In this case, the stored procedure simply returns its own parameter value, which is displayed in the data grid in the form of a KPI annotation.

Multiple selection example.

Multiple selection example.


For further reference, here is the stored procedure:

USE [AdventureWorks2012] GO

/****** Object: StoredProcedure [dbo].[uspDisplayParameter] ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE PROCEDURE [dbo].[uspDisplayParameter] @dispParam nvarchar(200) AS BEGIN select 'Param' = @dispParam END GO


Related topics


Click to return to: Documentation | Defining KPIs

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

Copyright © 2009-2014 Dundas Data Visualization, Inc.