cancel
Showing results for 
Search instead for 
Did you mean: 

Filtering a data source by string variable

Former Member
0 Kudos

Hello everyone,

We have two data sources which share the attribute "location". Both are based on BEx-Queries.

Data Source 1 contains all available locations, while data source 2 contains only a subset of locations. Our goal is to filter data source 1 by its location attribute so that it contains the same number of rows with the same locations as data source 2 (like an inner join).

As it is possible to filter a data source like this

DS_xy.setFilter("Dimension", ["member1", "member2", "member3", ... ]);

our idea was to use the following method:

1. Declare a string variable and an array of the members

var text = "";                     //we also tried to use a global scripting variable

var array = DS_2.getMembers("Location", 1000);

2. Use a nested for-loop to read all the locations of data source 2 into the string

array.forEach(function(element, index) {

      text = "\"" + element.externalKey + "\"";

      array.forEach(function(element, index) {

           text = text + ", \"" + element.externalKey + "\"";

      });

});

3. Filter the data source by the generated string (which contains: "location1" , "location2", "location3", ...)

DS_1.setfilter("Location", [text]);

It doesn's work. We have checked the externalKey-format, which corresponds to the format of members in data source 1.

When I copy the string returned by variable "text" into the code like this

DS_1.setFilter("Location", ["location1", "location2", "location3"... ];

it works perfectly.

Can anyone help? Thank you in advance!

Christiane

Accepted Solutions (1)

Accepted Solutions (1)

Karol-K
Advisor
Advisor
0 Kudos

Hi,

you build up the content with use of external key:

   text = text + ", \"" + element.externalKey + "\"";

then, you use the filter method which expects internal key.

DS_1.setfilter("Location", [text]);


you should use instead:

   text = text + "; " + element.externalKey + ""; // the output will be a string: "a; b; c"

   DS_1.setfilterExt("Location", text);


btw, I am not sure (would need to test it) if this statement will work at all:

DS_1.setfilter("Location", [text]);

as the API is expecting an array, and not sure if [<string>] (a string in brackets) will be accepted as real array.


also, if you want to work with arrays and internal keys, you can use the component Collection Utility - SCN Design Studio Community.


hope this helps.

Karol

Former Member
0 Kudos

Hey Karol,

thank you very much. Using the setFilterExt API with the altered string works fine!

Best Regards

Christiane

Answers (1)

Answers (1)

MustafaBensan
Active Contributor
0 Kudos

Hi Christiane,

Just out of curiosity I performed an experiment which worked for me with BEx data sources.  If you are able to use the internal key for your Location dimension, then you can reduce the script to two lines of code as follows, without the need for building a selection string via looping:

var array = DS_2.getMembers("Location", 5000);

DS_1.setFilter("Location", array);

Here is my working example:

Code for On Click event script of button:

var myAirlines02 = DS_2.getMembers("ZAIRLINID", 100);

DS_1.setFilter("ZAIRLINID", myAirlines02);

Application before filtering:

Application after filtering (by clicking filter button to execute script):

Regards,

Mustafa.

Karol-K
Advisor
Advisor
0 Kudos

this is true, of course if in the loop there is no IF statement which is again kinking off some members.

Also, using the version from Mustafa is better, as on some members you can get troubles on external key with keys which are containing characters which need to be esxaped (eg. + or \)