SAC Analytics Designer: Setting and Removing Table Filters via Scripting

[carljschmidt]

Hi there,

I am in a situation, where I want to filter a table in SAC via its onSelect() event with the following code:

// The variable `selection` may have two dimensional columns `KeyColumn` and `ValueColumn`.<br>var selection = Table_1.getSelections();
Table_1.getDataSource().setDimensionFilter('KeyColumn', selection[0].KeyColumn);

As the values in KeyColum are unique in my case, the code filters the table on the entry the user clicked on leaving the user with only a single line in the table after the execution of the code. E.g., Table_1 could look like this:

| KeyColum | ValueColumn |
|----------|-------------|
|        1 |           A |
|        2 |           B |
|        3 |           C |
 ...          

To allow the user to get back to the full table after the filtering, I added a Button and implemented its onClick() event as follows:

// Show the active filters on the data source.
var activeFilters = Table_1.getDataSource().getDimensionFilters('KeyColumn');
console.log(activeFilters);

// Option 1 | Resetting the table
Table_1.getDatasource().removeDimensionFilter('KeyColumn');

// Option 2 | Resetting the table (alternatively; assuming that `dimensionsInfo[0]` refers to the column `KeyColumn`)
var dimensionsInfo = Table1.getDimensions();
Table_1.getDataSource().removeDimensionFilter(dimensionsInfo[0])

However, I am struggling to reset the table via the button as neither option 1 nor option 2 have any effect on the filtered table (i.e. the table remains filtered on a single line) and the array activeFilters turns out to be empty.

I know, that there are some posts in the context of this issue, e.g.:

• SAC Analytics Designer getDimensionFilters()/setDimensionFilter() problem with BW hierarchy
• Get Filter Dimension in Analytic Application with a filter line in Group Mode (see answer regarding Group Filter and Individual Widget Filter)
• Use getDimensionFilters API (The first line of the provided sample code already returns `undefined` in my case)

Since none of these posts helped me to resolve this issue, I would be grateful for any support as to how to reset a table via scripting or any explanation of the observed behavior of the two methods getDimensionFilters() and removeDimensionFilter().

Edit 1:
In the described scenario Table_1 is part of a Linked Analysis and the changes made to Table_1 are set to propagate down to other widgets in the story.
While setting the filter for Table_1 works fine in either case, removing the filter only appears to work if Table_1 is on its own and not linked to any other widget in the story. Please see the comments on eeddggaarr's reply below.

Edit 2 (Solution):
The issue has been solved by using an additional InputControl as an agent to propagate the information about the filtering down to the widgets. In contrast to the initial situation of this question, the widgets are linked to the InputControl rather than Table_1 in this approach, which also allows to use the Link Dimensions feature to also propagate the information of the filtering to other models in the story. Please see the comments on eeddggaarr's reply below.

Thanks for your help!

[eeddggaarr · Accepted solution]

Hi Carl,

please try code below & replace "ZLRECH" with your dimension.
Assumption: only 1 Dimension in table.

Table onSelect:

var temp = Table_1.getSelections()[0]["ZLRECH"];
Table_1.getDataSource().setDimensionFilter("ZLRECH", temp);

Button onClick:

Table_1.getDataSource().removeDimensionFilter("ZLRECH");

regards
edgar

[carljschmidt · Accepted solution]

Hi eeddggaarr,

based on your reply I created a MWE which seems to work perfectly fine with the code you provided.
I can apply the filter using its onSelect() method and can remove the filter again via the button and its onClick() method.

If I am not mistaken, the code you provide is quite similar to the code I used in the question, so I am still wondering why it does not in the initial situation. Maybe that is due to the greater complexity of the full story compared to the MWE, but knowing that the scripting works as intended in the MWE is very helpful.

If I am missing some essential differences in your code compared to the code I posted above, please feel free to point them out.

Thank you & kind regards
Carl

[eeddggaarr · Accepted solution]

Hi Carl,
when higher complexity refers to having dynamic dimensions (but only 1) in your table than this is no problem => you could include:

var dim = Table_1.getDimensionsOnColumns();

and use this variable value to get rid of the filter:

Table_1.getDataSource().removeDimensionFilter(dim);

regards
edgar

[carljschmidt · Accepted solution]

Hi eeddggaarr,

thank you again for your reply. I will keep that in mind.

Meanwhile, I took my MWE a bit further and closer to my initial situation.
In my initial situation, I have a linked dimension from the model used for the table to another model used in the story.

In essence, the button the reset the filtering stops working as soon as I introduce the linked dimension and set up the table accordingly. The onSelect() method to filter the table still works fine and also propagates the filtering down to the other model. But resetting the filtering remains an issue.

Even without the additional model, resetting the filter via the button only works if the first option is selected in the Linked Analysis settings and stops working as soon as I select All Widgets in the Story. Please see the screenshot below for clarification:

I am still wondering if this is the intended behavior.
For me, it is a bit counter-intuitive, that you can set the filter in any of the described cases, but removing the filter apparently only works if the table is not linked to any other widget.

[eeddggaarr · Accepted solution]

Hi Carl,

have a look here.

Linked analysis and optimized mode needs some updates from SAP.
I had a similar usecase ( 4 tables from same model which should be linked to each other). As this was not working with linked analysis I decided not to use the in-build linked analysis function for now.
Instead I used the tables 'onResultChanged()' functions and whenever a user sets a filter on one of the tables then this filter value is retrieved and pushed to the other tables if the new filter value/s is different from its value/s before the results changed.

However, if you prefer to let user select rather than filter a table you could use InputControls as 'intermediaries'. Whenever a user selects a cell in a table this selection is pushed into the respective InputControl which in turn will filter all relevant charts/table if configured in that way.

there might be one or several other ways to get to the desired result which I am not aware of. Maybe other users have better ideas.
br
edgar

[carljschmidt · Accepted solution]

Hi eeddggaarr,

thank you again for your explanation and the link to the supported and unsupported features.

I think using InputControls as intermediaries as you suggest is a neat workaround in this situation to propagate the changes made to the table down to other widgets and the table itself.
I now changed the onSelect() of Table_1 to

var selection = Table_1.getSelection();

// Make sure that the user selected a valid line in `Table_1`.
if (selection.length > 0) {
    var temp = selection[0]['KeyColumn'];
    // Set the filter on `InputControl_1` that has been configured to act on the dimension `KeyColumn` via the UI.
    InputControl_1.getInputControlDataSource().setSelectedMembers(temp);
}

As InputControl_1 controls the model (Model A) of Table_1, Table_1 (and any other widget using Model A) will be affected by the code above.
To also propagate this change down to the other widgets using another model (Model B), I am still using the Link Dimensions feature linking Model A and Model B.

This approach works perfectly fine, so with our conversation here I am more than happy to consider this issue solved and to accept your initial answers as the solution.

Thank you for your support and your thoughts and ideas on this issue, eeddggaarr !

Kind regards
Carl