How to reset variable to be reused in crosstab measure?

ccoutsouve · December 3, 2018

I have a crosstab that i want to calculate the ratio of the two values that display per group.
i created a global variable on the report onprepare event to capture the data in an array:
var ascsil = new Array();
reportContext.setPersistentGlobalVariable("ascsil", ascsil);

Then, on the measure in the crosstab i added the following in the script to capture the 2 values that display to be calculated:
var ascsil = reportContext.getPersistentGlobalVariable("ascsil");
ascsil.push(measure["Tot Cnt3"]);
reportContext.setPersistentGlobalVariable("ascsil", ascsil);

Last, i added grand total to create a column so i can add a dynamic text element to calculate the ratio. The dynamic text has the following script:
var ascsil = reportContext.getPersistentGlobalVariable("ascsil");
if (ascsil == null) "Ratio: No values found.";
else if (ascsil.length < 2) "Ratio: Only one value found.";
else if (ascsil[1] == 0) "Ratio: Division by zero: " + ascsil[0] + " / " + ascsil[1];
else (ascsil[0] / ascsil[1]).toFixed(2);

Problem is now that the same ratio displays in all the rows of the crosstab.
How can i have the scripting reset the array to capture and calculate the ratio for the next row/group in the crosstab?

jfranken · December 3, 2018

Global variables must be String data type. They can't be arrays. The options are:

concatenate the values to a string when saving variables and parse them to get the values
create a variable for each column (ascii1, ascii2, ...)
instead of using a global variable, create the variable in the "initialize" event of the report (var ascii = [])

The last option is the easiest, but there can be issues with variables going out of scope or getting re-initialized, so test carefully.

How to reset variable to be reused in crosstab measure?

Comments

Categories