Using Arrays in a Script to Enhance a Story

Before we use the result set in script, we must ensure to understand the elements of the result set.

• The Dimension Header contains dimension names.
• In Column Header and Row Header, you find members that usually form a tree. The combination of all members on one axis is called axis tuple.
• At the crossing point of a row and a column axis tuple, you find a data cell in the Data area.

You could combine the two tuples from rows and column to a larger tuple with the same effect: select a single data cell. Such a "large tuple" selection also survives some changes, for example, if a dimension is moved from rows to columns or if axes are swapped.

Such a "single tuple" approach is used in story design to select data cells.

You can reference a single data cell with the getData({selection}) function of a data source. To specify the cell, you pass a selection to the function. The selection is written in the common JSON format.

For each cell, you can get either of the following values:

• Formatted value, for example, 10,803,914.00
• Raw value, for example, 10803914

If you must work with more than one single data cell, you can work with selections. For example, you can read a set of selections into an array and then, to generate your desired visualization, loop over the array.

Step 1: Read the desired selections into an array.

Step 2: Loop over the array and use a function. For example, use the getData({selection}) function to visualize/react on the data values.

Here is the code used in the example above:

1234567891011
var selections = Table_1.getDataSource().getDataSelections({
      [Alias.MeasureDimension]:"00O2SOEHXWHF3AU4LBEW8WZSX",
      "0D_NW_BP__0D_NW_CNTRY":"US"
});
console.log(selections);

for (var x=0; x<selections.length; x++ ){
      var cell = Table_1.getDataSource().getData(selections[x]);
      console.log(cell.formattedValue);
}

It's possible that only working with the values isn't enough because your users want to know the element to which the values belong.

You can do this with the getResultMember(dimension, {selection}) function, as shown in the following figure.

Here is the code used in the example above:

123456789101112
var selections = Table_1.getDataSource().getDataSelections({
    [Alias.MeasureDimension]:"00O2SOEHXWHF3AU4LBEW8WZSX",
    "0D_NW_BP__0D_NW_CNTRY":"US"
});
console.log(selections);

for (var x=0; x<selections.length; x++ ){
    var cell = Table_1.getDataSource().getData(selections[x]);
    var year = Table_1.getDataSource().getResultMember("0CALYEAR", selections[x]); 
    var country = Table_1.getDataSource().getResultMember("0D_NW_BP__0D_NW_CNTRY", selections[x]);
    console.log(country.description + " / " + year.description + " / " + cell.formattedValue);}

As an alternative to what is shown so far, you can read the whole (or parts) of the result set into an array at once. For each cell, you get a full list of properties that you can reference in further script processing.

For example:

123
var resultset = Table_1.getDataSource().getResultSet();
console.log(resultset);

To read the value in the example above, you would use:

123
console.log(resultset[0][Alias.MeasureDimension].formattedValue);

The syntax for the getResultSet offers three optional parameters. If you don't specify any of these parameters, you retrieve the whole available result set.

1
Table_1.getDataSource().getResultSet(selection?, offset?, limit?: integer) 

• selection Can be used to restrict the returned result set, for example, to a measure or dimension member.
• offset Skips the offset cells before beginning to return the cells.
• limit Constrains the number of returned cells.

The scripting environment offers an Alias object that can be used to reference different standard objects. The available objects are as follows:

• Alias.DefaultTheme
• Alias.FlatHierarchy
• Alias.MeasureDimension
• Alias.NullMember
• Alias.TotalsMember

To reference the result member in script you can use the alias Alias.TotalsMember.

To reference the measure dimension in script you can use the alias Alias.MeasureDimension.