Create Interdependent filters using Independent Hierarchies

Contents[Hide]

1. Overview

This article shows how to create interdependent/linked filters using hierarchies that are independent of each other. When the user selects a member value from the first filter, the second filter shows values for selection that are relevant to the selected member in the first filter. This is a similar functionality to the cascading member filters but uses separate hierarchies instead of a single multi-level hierarchy.

Prerequisites: All hierarchies bound to the filters should be Custom User Hierarchies and based on one data cube with explicit public parameters.

The goal in the example below is to choose a country from the first filter, such that the second filter shows only the SalesPersonId's in the country selected.

This sample works only for two interdependent filters.

2. Create a Data Cube

On the Data Cube designer canvas, drag and drop [Sales].[SalesOrderDetail] from the Adventure Works data connector in the Explore panel.

Drag and drop [Sales].[SalesOrderHeader] and create a connection link between both the SQL Select nodes. A Join will be formed.

Configure the join by using SalesOrderID as the Unique Key between both the tables.

Drag and drop another table [Sales].[SalesTerritory] from the Data Connector and create a connection link between this SQL select and the Join node.

A new join is created. Configure this join byusing TerritoryID as the unique key between both the tables.

Data cube for custom user hierarchies
Data cube for custom user hierarchies

Right Click on the [Sales].[SalesTerritory] SQL select node and select Configure.

From the Configure SQL Select transform panel, define a Transform Parameter. The name of the transform parameter should be corresponding to the column name (in the case 'Name' is the country name we want to filter on). Make the parameter public and choose the value type Single.

Note
A single value type is chosen when the parameter accepts a single value only. Choose Collection value type to allow the parameter to accept multiple values. Choose Range value type if the parameter should accept a range of calendar values.

Define the parameter to pass on to the Filter
Define the parameter to pass on to the Filter

Note
This data cube is used as a base for creating custom user hierarchies. Another data cube has to be created for dashboard datasource.

3. Create Hierarchies

Using the Main Menu, create a new user hierarchy.

From the data cube created above, drag and drop the Name dimension on the hierarchy designer. Remove any levels that are added by default and check in the hierarchy.

Create a new hierarchy with the country name dimension.
Create a new hierarchy with the country name dimension.

Create another hierarchy, and this time drag and drop SalesPersonId from the data cube on the hierarchy designer.

Create a new hierarchy with the Sales Person ID dimension.
Create a new hierarchy with the Sales Person ID dimension.

Check in the hierarchy.

While still in the SalesPersonId hierarchy designer, open the Browser developer tools.

Click on the hierarchy and in the Network tab, find the path "/api/hierachyentity"

Select the path and click on Preview tab.

Under parameters, find the public parameter that was created at the data cube level and copy its ID.

Use the browser developer tool to find the parameter ID.
Use the browser developer tool to find the parameter ID.

4. Create another Data Cube

Create another data cube similar to the data cube created above. This data cube will act as a data source for the dashboard.

You can either follow the steps for the first data cube or you can make a copy of the first data cube.

To make a copy, open the Explore panel. Find the data cube that was created above, right click and select Copy.

Right click on the Data Cubes folder and click Paste. This will create a new data cube with the same settings as the previous one.

Paste the copy of the data cube in the Data Cubes folder.
Paste the copy of the data cube in the Data Cubes folder.

Open the new data cube in the data cube designer.

Click on Process Result and promote the hierarchies to explicity hierarchies created above.

Promote the dimensions to Explicit hierarchies.
Promote the dimensions to Explicit hierarchies.

5. Create the Dashboard

Create a data control with the required measures and hierarchies created above on the dashboard designer.

From the toolbar menu, add two new member filters, one bound to the Name hierarchy and the other bound to the SalesPersonId hierarchy.

Create a dashboard with a table control and two filters.
Create a dashboard with a table control and two filters.

Click on the country Name filter and go to the Properties panel.

Under the Main tab, find the event Parameter Value Changed and add a script to this event.

//Get ViewParameter1 <- Need to get the parameter that is bound to this filter
var vp = this.getViewParameters()[0];
var currentValue = vp.parameterValue;
//Take only the first value from the viewParameter
var currentMember = currentValue.values[0];

//take a unique value till the first dot(in case of multi level hierarchy)
var specialValue = currentMember.uniqueName.substring(0, currentMember.uniqueName.indexOf('.'));

var newParameter = new dundas.data.SingleStringValue({
  "parameterId": "8ba78dbe-eb83-47e1-ab11-220e24fd3301",
  "value": specialValue
});

//apply inherited filter values to the second member filter(Sales Person ID)
parameterHierarchy2.control.setInheritedFilterValues([newParameter]);

If the parameter defined in the first data cube is of Value Type - Collection (i.e. allows multiple values to be selected from the filter), then the following script will be used in the Parameter Value Changed event.

//Get ViewParameter1 <- Need to get the parameter that is bound to this filter
var vp = this.getViewParameters()[0];
var currentValue = vp.parameterValue;

//Get number of chosen elements
var vLength = currentValue.values.length - 1;

//Take only the first value from the viewParameter
var specialValue = getSpecialValue(currentValue.values[0]);

//Use CollectionString to be able to pass several elements from the filter
//if all token has been selected
if (specialValue == "\\A") {

    var myToken = new dundas.data.ParameterToken({
        "caption": "All",
        "id": dundas.constants.ALL_TOKEN_DEFINITION_ID
    });
    var newParameters = new dundas.data.CollectionStringValue({
        "parameterId": "8ba78dbe-eb83-47e1-ab11-220e24fd3301",
        "values": [specialValue],
        "token": myToken
    });
} else {
    var newParameters = new dundas.data.CollectionStringValue({
        "parameterId": "8ba78dbe-eb83-47e1-ab11-220e24fd3301",
        "values": [specialValue]
    });
}

for (vLength; vLength > 0; vLength--) {
    specialValue = getSpecialValue(currentValue.values[vLength]);
    newParameters.values.push(specialValue);
}

parameterHierarchy2.control.setInheritedFilterValues([newParameters]);

function getSpecialValue(member) {
    var specialValue = member.uniqueName.substring(0, member.uniqueName.indexOf('.'));
    return specialValue;
}

In case the parameter is a DateTime parameter and uses the Datepicker filter or the Calendar filter, the steps required are the same as above, however the new parameter is created using the following code snippet:

var currentValue = vp.parameterValue;

// For a DatePicker
var newParameter = new dundas.data.RangeDateTimeValue ({
  "parameterId" : "8ba78dbe-eb83-47e1-ab11-220e24fd3301",
  "lowerBoundaryValue" : currentValue.lowerBoundaryValue ? currentValue.lowerBoundaryValue.toISOString() : null,
  "upperBoundaryValue" : currentValue.upperBoundaryValue ? currentValue.upperBoundaryValue.toISOString() : null
});

// For a Calendar Hierarchy Filter
var newParameter = new dundas.data.RangeDateTimeValue ({
  "parameterId" : "8ba78dbe-eb83-47e1-ab11-220e24fd3301",
  "lowerBoundaryValue" : currentValue.lowerBoundaryValue ? currentValue.lowerBoundaryValue.memberTime.toISOString() : null,
  "upperBoundaryValue" : currentValue.upperBoundaryValue ? currentValue.upperBoundaryValue.upperBoundaryTime.toISOString() : null
}); 

6. Test the dashboard

From the dashboard designer, click on View/Sandbox view mode.

Choose a country name from the Name filter. Click on SalesPersonID filter. You will only see the Sales Persons available in Australia.

Test the filters.
Test the filters.

7. See Also

 

 

 

 

 

 

 

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri