cancel
Showing results for 
Search instead for 
Did you mean: 

Lumira Designer 2.2: Filtering records in crosstab by key figure value

0 Kudos

Hi everybody,

I would like to know how could I filtering/hide records in crosstab by values of a key figure via scripting in Lumira Designer 2.2. For example, I created a crosstab (data source = BEx query) with 70 entries, 50 records with values 2-10, another 20 entries with value 1. After "On Element Clicked" of a created button the result in crosstab should only show all 20 entries with value 1. Any idea?

Best regards,

Steffen

View Entire Topic
0 Kudos

UPDATE: I found a way to script it while messing around with the answer to this blog. Replace anything in <> with your own datasource/dimension/measures/etc.

In my example script, I used an input parameter to get the number to match. In your case, you could use a "1" in the input field, or not use the input variable and change the "== float" to "== 1." Ultimately, you're filtering the data source that is set to your crosstab as shown below.

//create an array with all members of a chosen dimension (NOT a measures dimension)
var array = <DATASOURCE>.getMembers("<dimension>", 100);

//create an empty string for filtering your data source
var listFilter = "";

//create variable to hold the input value
var input = <INPUTFIELD>.getValue();

//change the value from a string to an integer	
var float = Convert.stringToFloat(input);
	
//for each member of the array of your dimension, check if it matches the input value
array.forEach(function(element, index) {
	
	if(<DATASOURCE>.getData("", {"(MEASURES_DIMENSION)":"<measure_ID>","<dimension>":element.internalKey}).value == float){
		 listFilter = listFilter + element.externalKey + ";"; 
         }
});
	
//filter the datasource by the updated listFilter list of external keys
<DATASOURCE>.setFilterExt("<dimension>", listFilter);

Happy Coding!

** Note: if you have a crosstab with a lot of rows, this will be very slow since it's a giant loop, and my previous answer might be a better way to go.