.getDataSource().setDimensionFilter

[Noel_Munday]

Problem definition

Your dimension filter in SAP Analytic Cloud (SAC) Analytic Application is not returning any values when you call .getDataSource().setDimensionFilter.

 

Overview

The issue I'm going to focus on in this blog is how to set the filter value for a hierarchy node.

If you've built an application with a dimension which does not have a hierarchy then it is fairly simple to perform the filter because there are only 2 parameters:

1. The dimension = string


2. The filter value = string which is the member id or multiples of id's etc as you have various options here

The issue usually arises when you introduce a hierarchy and you assume that using the member id, which could either be the root of the hierarchy or a hierarchy node, should be sufficient to perform the filter in the same way that it worked on a flat structure...but then it fails. So let's see why.

 

Dimension filter for hierarchy nodes

The mistake that can easily be made is this:

1. Let's say I have a dimension called "Task" and each task line item belongs to a project so I structure my dimension so that I have a hierarchy within the dimension "Task" for Project 1 = P01T is the root of project 1 tasks, P02T is the root of project 2 tasks and P03T is the root of project 3 tasks with tasks P0?T01, P0?T02 and P0?T03 for each root.

2. Without the hierarchy if I selected "P01T" in the filter I would return the single member line item but as soon as I introduce the hierarchy the filter fails and regardless of any valid member value provided to the filter it still fails.

In other words if your filter looks like this it will NOT work:

Table_1.getDataSource().setDimensionFilter("Task", "P01T");

 

So the best way to figure out what the member value setting should be is to create a new button on the canvas and add these two lines of code to it:

var sel = Table_1.getSelections();

console.log(sel);

 

Now execute your application and make sure you select the hierarchy node that you want to use as your selection value in your filter. Press the button that you added to your canvas (GetSelection) and view the output of the console (on a Mac that's "command+option+j"). In my case it resulted as follows:

 

 

 

This is the value that you are looking for. Now add this as your parameter to your filter and it will work.

In my example it is added to buttons "P01 Tasks", "P02 Tasks" and "P03 Tasks" and for "P01 Tasks" it looks like this:

Table_1.getDataSource().setHierarchy("Task","H1");

Table_1.getDataSource().setDimensionFilter("Task", "[Task].[H1].&[P01T]");

 

Take note that if you are setting the filter on the ACCOUNT dimension then the same rules apply but you will notice that you will not be using the dimension name as the first parameter for the filter but rather "@MeasureDimension". This will show up in the console using the above method but don't assume you can use the dimension name. In other words if the dimension above, "Task", was an ACCOUNT dimension then the filter will look slightly different:

Table_1.getDataSource().setDimensionFilter("@MeasureDimension", "[Task].[H1].&[P01T]");

 

I hope this will save you some time problem solving and thanks to sathyanarayanansr for his contribution here.