Filter metric set on dashboard by other metric set

#1

My use case is to have two metric sets on a dashboard and when the row dimension on table 1 gets filtered (by a different slicer), that it filters the same row dimension on table 2 (which doesn’t have slicer).

I have two data cubes:

Orders (list of orders, concerned with who ordered it and who was supplier on order)
OrderUser
SupplierName

Metrics (list of suppliers with % On Time Measure)
SupplierName
% On Time

Both cubes also have a date dimension that would be used as a slicer.

Goal is to be able to select a user and see all suppliers that were on orders for that user. I also want to see the % On Time metric for ONLY those suppliers. I’m fine with this being in a separate table on the dashboard.

I set up a dashboard with a filter on OrderUser and two tables, table 1 showing Orders data, table 2 showing Metrics data.

Filtering on OrderUser gives me a list of suppliers in table 1.
Table 2 shows all the suppliers and % On Time but I only want the suppliers from the filtered table 1 to show.

So, if raw data is:
Orders data
User A Supplier A
User A Supplier B
User A Supplier C
User B Supplier A
User B Supplier Y
User B Supplier Z

If User A is selected in filter on dashboard, table 1 only shows Supplier A, B and C but table 2 should also only show Supplier A, B and C.

I tried using Dependent filters where I created a second filter on Supplier and connected it to table 1 Supplier and table 2 Supplier. When I select a user in the first filter, it did indeed change the supplier filter dependent on it to where it only showed the suppliers showing in table 1 (A,B,C) but even though it was connected to a view parameter on Supplier on table 2, it did not actually filter the table data. I assume it is because it is set to All and the filter 2 must just be hiding the unselected suppliers but they are still getting included in table 2.

I tried using a formula visualization that added % On Time from table 2 to a dummy zero measure from table 1 to get both metric sets included and it showed the % On Time correctly and blank when the supplier wasn’t in table 1, but I could not figure out how to filter out the lines that had the blank measure from table 1. I tried filtering on the measures but it would not exclude any records in table 2.

I know that I could make the user click on a supplier in table 1 to see their % OTD in table 2 but was wondering if it was possible to view them all automatically.

I know that I could also set up a relationship between Supplier on data cube 1 and data cube 2 if I had to but I’d rather not since the % On Time is an aggregate measure already for each supplier.

Thanks for any advice.

Kelly

#2

You could do something like this:

https://www.dundas.com/support/learning/documentation/create-view-content/how-to/modify-a-filter-/-view-parameter-using-scripting#h3-4-collectionmember

A script on table 1’s data changed (or its corresponding filter’s value changed) where you can retrieve in the suppliers from table 1, load their member value objects and then push those as values for filter 2.

#3

Thanks, @christian.pervan. I set up some test data to try your suggestion and it is close but I must be missing something. I can push all the members of table 1 (tblQuoteSupp, contains letters A - Z) onto the view parameter for my second table (vpOTDSupp), but even when I filter tblQuoteSupp, it still pushes all the members. The dataset in the first table contains Supplier B and the second dataset doesn’t, so I know the values in the view parameter is coming from table 1, but when I filter to “Kelly”, the values still consist of every member in table 1, not just those linked to Kelly. Here is the script triggered on the filter change where I reference the cellset for those values. Can you help with what I am missing?

var viewParameter = this.parentView.control.getViewParameterByName(“vpOTDSupp”);

// Remove all values and tokens from the parameter value
viewParameter.parameterValue.clearTokens();
viewParameter.parameterValue.clearValues();

// Get the cellset from a visualization’s metric set binding
var cellset = tblQuoteSupp.metricSetBindings[0].dataResult.cellset;

// Get the hierarchy from the cellset based on its unique name
var hierarchy = cellset.hierarchies.filter(function (hierarchy) {
return hierarchy.uniqueName == “Supplier”;
})[0];

var hierarchyService = window.dundas.context.getService(“HierarchyService”);

// getMembers will return all the members from the specified hierarchy.
// analysisStructureId in this case is the origin cube id
var deferred = hierarchyService.getMembers(hierarchy.analysisStructureId, {
// specify the unique name since there’s likely more than 1 hierarchy in the cube
“hierarchyUniqueName”: hierarchy.uniqueName,
// specify the deepest hierarchy level unique name
“levelUniqueName”: hierarchy.levels[hierarchy.levels.length - 1].uniqueName
});

deferred.done(function (members) {
// loop through all the members
members.forEach(function (member) {
viewParameter.parameterValue.values.push(member.loadMemberValue());
});

// Set the last modified time so this parameter takes precedence over any others
viewParameter.invalidateParameterValueLastModifiedTime();

// Update all the connected adapters with the newly modified values
// Includes data visualizations and filter controls
viewParameter.refreshAllAdapters();

});

image
image.png476×560 4.24 KB

#4

Ok, I got it to work with the help of this documentation:
https://www.dundas.com/support/support-center/support-articles/scripting/read-data-from-a-visualization-by-script

I realized that in my script above I was only using the cellset to get the hierarchy and then retrieving ALL values, not just the ones filtered in the visualization which is what I wanted. The script below walks through the cellset to load only the members I wanted. Thanks for the initial help to point me in the right direction.

var viewParameter = this.parentView.control.getViewParameterByName("vpOTDSupp");

// Remove all values and tokens from the parameter value
viewParameter.parameterValue.clearTokens();
viewParameter.parameterValue.clearValues();

// Get the cellset from a visualization's metric set binding
var cellset = tblQuoteSupp.metricSetBindings[0].dataResult.cellset;

for (i = 0; i < cellset.rows.length; i++)
 {

  viewParameter.parameterValue.values.push(cellset.rows[i].members[0].loadMemberValue());
}

// Set the last modified time so this parameter takes precedence over any others
viewParameter.invalidateParameterValueLastModifiedTime();

// Update all the connected adapters with the newly modified values
// Includes data visualizations and filter controls
viewParameter.refreshAllAdapters();
1 Like