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

[former_member380198]

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

[former_member519891 · Accepted solution]

Hi Steffen,

I've been trying to figure this out for a while. So far, I haven't been able to figure out the scripting, but the best workaround I've found is to filter at run time. Once the crosstab is rendered on the screen, you can right mouse click (or double tap on mobile) the key figure heading you'd like to filter on, and choose "filter by measure." Here I right clicked on the "Target" measure.

I then created my filter of "greater than" and "1000," which returned the below results, filtering out the other 4 targets.

In your case, you could filter on "equal to" and "1" to return only those with the value "1." If you wanted to remove the filter, you simply right mouse click on the measure again, click on your filter, and choose "delete."

Hope that helps!

[former_member380198]

Hi Emily,

I'm impressed, thank you for your quick response. I tested your code. Unfortunely the crosstab shows still all 70 entries. Do you see any wrong part in source code at first appearance? Kind regards.

//create an array with all members of a chosen dimension (NOT a measures dimension)
var array = DS_15.getMembers("ZI_KNROPP", 200000000);

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

//for each member of the array of your dimension, check if it matches the input value
array.forEach(function(element, index) {

	if(DS_15.getData("", {"(MEASURES_DIMENSION)":"00O2TMGPPF4ASYPCUWEZQ1WIK","ZI_KNROPP":element.internalKey}).value == 1){
		 listFilter = listFilter + element.externalKey + ";"; 
         }
});

//filter the datasource by the updated listFilter list of external keys
DS_15.setFilterExt("ZI_KNROPP", listFilter);

[former_member519891]

Hi Steffen,

Sorry for the delayed response. One issue I ran into when I tried to run this on a different crosstab was that it didn't work with more than one dimension in the data source. I ended up adding the script to remove the other dimensions before running the above script, and then adding the dimensions back to the rows when I was done.

<DataSource>.removeDimension(<dimension_1>);
<DataSource>.removeDimension(<dimension_n>);

//add the previous script

<DataSource>.addDimensionToRows(<dimension_1>);
<DataSource>.addDimensionToRows(<dimension_n>);

[former_member519891]

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.