SAP Analytics Cloud is an extensive cloud platform that provides many necessary tools in the field of Business Intelligence. An example of such a tool is Analytic Designer - a module that allows you to create advanced analytical applications that can be enriched with scripts written in a simplified version of JavaScript.
In this article, we will introduce getResultSet– an example of an interesting built-in script function. It returns all data cells in a given object –it’s like an "explorer" that browses through each element of a table and lists information about it.
There are many possible use cases for this function - we can read specific rows, columns, copy and paste cell values to other items. In this article we will try to briefly present such examples and possible uses of the function.
We will operate on a simple example - a small employee absence table, broken down over the years.
Using the menu at the top of the interface, insert a button and position it above the table (Insert > Button)
Using the Styling pane on the right side of the interface, we can rename it (in our case, "Odczytaj")
Analytic Designer executes scripts using Events. For example, to run a script by pressing a button, we need to put it in the button's onClick event. To do this, select the buttonin the application, and then click on the icon with three dots , Edit Scripts i onClick.
Using getResultSet , we want to read the data from the employee absence table and save it to a variable in JavaScript. To do this, enter the following code into the script editor:
var elementy_tabeli = Table_1.getDataSource().getResultSet();
Where :
Var - variable initialization
Elementy_tabeli – the name of the variable in which we want to store the data
Table_1.getDataDataSource() – data source retrieved from the table
At this point, after pressing the button, the data from the table will be loaded into the variable. Now we will try to display that data in the console. As Elementy_tabeli is an Array type variable, to read its values, we need to use a For Loop (as presented below):
for (var i = 0; i < elementy_tabeli.length; i++)
{
console.log(elementy_tabeli[i]);
}
The entire script should look like this:
Console.log() allows you to display specified items in the browser console.
Save the changes and run the application. Open the console with the F12 button (in the example we use Google Chrome) and then press the buton we added. The result should look like below.
Note that each retrieved item can be expanded to see its information in more detail. By examining the elements, we can see that getResultSet traverses the table from left to right, top to bottom.
We can also count the number of elements in the table, script example below:
var elementy_tabeli = Table_1.getDataSource().getResultSet();
var ilosc_elementow = 0;
for (var i = 0; i < elementy_tabeli.length; i++)
{
console.log(elementy_tabeli[i]);
ilosc_elementow++;
}
console.log("Ilość elementów to : " +ilosc_elementow.toString());
Please note : We get the value of 23 even though the table consists of 24 cells becase getResultSet only returns objects that have values.
Now we will try to "extract" and display the value of the last element in the table in a text object. In our example, we changed the data source:
We insert a Text object (Insert > Text) to the application and place it next to the button.
To save the last element from the table to a variable, to the previous script add the following entry:
var ostatni_element = elementy_tabeli[elementy_tabeli.length-1];
(we insert [table length - 1] as the element number because it is indexed from 0)
To be able to use the values collected by GetResultSet, we must first understand how they are stored. For example, let's take a look at the last entry of our script in the console:
We can see that a single entry consists of 3 elements that constitute the technical names of the objects used in the table -
– @MeasureDimension (measure)
– Date_703i1904sd (date)
– Location_4nm2e04531 (location).
From a technical point of view, a single entry is nothing more than a small table. We can see that the cell value (18.66) is stored in @MeasureDimension element for property called formattedValue. Let's use this knowledge in the script.
var wartosc_ostatniego_elementu = ostatni_element["@MeasureDimension"].formattedValue;
As the table index we give the value @MeasureDimension, and after the dot, we choose the property that interests us (formattedValue).
We enter this value into the text element:
Text_1.applyText(wartosc_ostatniego_elementu);
The entire code should look like this:
var elementy_tabeli = Table_1.getDataSource().getResultSet();
for (var i = 0; i < elementy_tabeli.length; i++)
{
console.log(elementy_tabeli[i]);
}
var ostatni_element = elementy_tabeli[elementy_tabeli.length-1];
var wartosc_ostatniego_elementu = ostatni_element["@MeasureDimension"].formattedValue;
Text_1.applyText(wartosc_ostatniego_elementu);
We save and run the application. Pressing the button should return a similar result as below:
This concludes our article on using GetResultSet in applications. For more information on GetResultSet and other useful Analytic Designer features, please refer to the official documentation -
https://help.sap.com/doc/958d4c11261f42e992e8d01a4c0dde25/2020.21/en-US/index.html