Dynamic Apps in Lumira 2.1 – Iterating Over a Resultset
In the first part of my little blog series on dynamic Lumira apps, I have shortly explained the Components API that allows to create components dynamically. However, this doesn’t help a lot if you don’t have a way to create components based on your analytical data.
Therefore, we have also implemented a new feature to iterate over a resultset. But what is a resultset and what does “iterate” mean?
Resultsets
A resultset is more or less what you see in a crosstab:
It consists of four quadrants:
Number | Name | Contains |
1 | Dimension Header | Dimension names |
2 | Column Header | Combination of Members (“Tuples”) from Columns axis |
3 | Row Header | Combination of Members (“Tuples”) from Rows axis |
4 | Data | Data Cells |
Note that Measures are members of the “Measures” dimension – but the Measures dimension usually does not show a label in quadrant 1.
Summary: quadrant 1 contains dimension names. In quadrant 2 and 3 you find members that usually form a tree. The combination of all members on a axis is called axis tuple. And finally: at the crossing point of a row and a column axis tuple you find a data cell in quadrant 4.
You could combine the two tuples from rows and column to a big tuple with the same effect: select a single data cell. Such a “big tuple” selection also survive some changes, e.g. if a dimension is moved from rows to columns or if axes are swapped.
Data Selections
Such a “single tuple” approach is used in Lumira to select data cells. If you have created Lumira or Design Studio apps before, you will probably know the APIs getDataAsString and getData, which return a data cell (quadrant 4). You pass in a single tuple in a JavaScript JSON notation (key-value pairs in angle brackets).
Here is an example:
var cell = DS_1.getData("", {"gender":"F","(MEASURES_DIMENSION)":"store_cost","marital_status":"M","store_city":"9606 Julpum Loop"});
var formattedValue = cell.formattedValue;
If we apply the color coding from above, the script looks as follows:
var cell =
DS_1.getData(“”, {“gender”:“F”,“(MEASURES_DIMENSION)”:“store_cost”,“marital_status”:“M”,“store_city“:”9606 Julpum Loop”});
Iterating
Simple Resulset

var selections = DS_1.getDataSelections({
"store_city": "*",
"(MEASURES_DIMENSION)":"store_sales"
});
selections.forEach(function(selection, index) {
var cell = DS_1.getData("", selection);
TEXTAREA_1.setValue(TEXTAREA_1.getValue() + cell.formattedValue + "\n");
});
Inside of the forEach loop you will receive a data selection JSON for each row of the Store Sales column – or to be more precise – for each member of the “store_city” dimension. The measure member is repeated in each selection:
index | selection | cell value |
0 | {“(MEASURES_DIMENSION)”:”store_sales”, “store_city”:”1077 Wharf Drive”} | 28,328.56 |
1 | {“(MEASURES_DIMENSION)”:”store_sales”, “store_city”:”1501 Ramsey Circle”} | 52,896.30 |
… | ||
10 | {“(MEASURES_DIMENSION)”:”store_sales”, “store_city”:”9606 Julpum Loop”} | 566.50 |
11 | {“(MEASURES_DIMENSION)”:”store_sales”, “store_city”:”(RESULT_MEMBER)”} | 276,540.40 |
If you replace “*” with “?”, the totals member is skipped an thus the line of the table above would not exist.
Adding Member Information
The selection JSONs in the sample above contain internal keys for all members. In my data source they are quite understandable, but for most other data sources such a key it not what a human reader would understand. Lumira runtime has the concept of “presentations” for each member, e.g. internalKey, externalKey and texts with different length. Moreover each member could contain several display attribute members.
To access those member-specific information we have another new script API getMember. To getMember you pass a data selection and the ID of a dimension. As a result you receive a nice “Member” object that gives you access to all presentations and display attributes. Let’s extend the sample with some dimension labels:
var selections = DS_1.getDataSelections({
"store_city": "?",
"(MEASURES_DIMENSION)":"store_sales"
});
selections.forEach(function(selection, index) {
var cell = DS_1.getData("", selection);
var member = DS_1.getMember("store_city", selection);
TEXTAREA_1.setValue(TEXTAREA_1.getValue() + member.text + ":\t" + cell.formattedValue + "\n");
});
For JavaScript exerts: The effect of getMember is similar to selection.<dimId> or selection[“<memberId>”], and taking the returned internal key as lookup for member details object.
I will write more about getMember and the older getMembers in another blog.
Repeater Pattern
The full power of dynamic apps you achieve if you combine resultset iteration with Components-API: you iterate over the result set and create components for each data selection. We call this concept the “repeater pattern”, because you repeat some components for each selection from the resultset. Typically you would not iterate over all cells of the resutset. As I already mentioned, you can select bigger blocks – and create one or more component for each block.
The next sample uses the same HANA view as before but has the “customer_id” dimension on the rows axis. We iterate over this dimension and thus get one data selection per row in the form {“customer_id”:”188″} etc. From this selection we get a member and some “birthday” attribute. We also do some simple math to calculate a trend icon. The result is a table without using a table component – simply from Text and Icon components.
var s = DS_1.getDataSelections({
"customer_id": "?"
});
s.forEach(function(sel, index) {
var pos = index * 25 + 40;
var m = DS_1.getMember("customer_id", sel);
var tText = COMPONENTS.createComponent(ComponentType.Text);
tText.setText(m.text);
tText.setWidth(150);
tText.setTopMargin(pos);
tText.setLeftMargin(10);
var tBirthdate = COMPONENTS.createComponent(ComponentType.Text);
var birthdayString = m.getAttributeMember("birthdate").text;
tBirthdate.setText(birthdayString.substring(0, birthdayString.length - 9));
tBirthdate.setTopMargin(pos);
tBirthdate.setLeftMargin(150);
tBirthdate.setWidth(200);
var sales = DS_1.getData("", {"(MEASURES_DIMENSION)":"store_sales","customer_id": m.internalKey});
var cost = DS_1.getData("", {"(MEASURES_DIMENSION)":"store_cost","customer_id": m.internalKey});
var icon = COMPONENTS.createComponent(ComponentType.com_sap_ip_bi_Icon);
icon.setTopMargin(pos);
icon.setWidth(20);
icon.setHeight(20);
icon.setLeftMargin(300);
if (sales.value / cost.value > 2.5) {
icon.setIconUri("sap-icon://trend-up");
icon.setBackgroundColor("green");
} else {
icon.setIconUri("sap-icon://trend-down");
icon.setBackgroundColor("red");
}
var tSales = COMPONENTS.createComponent(ComponentType.Text);
tSales.setText(sales.formattedValue);
tSales.setTopMargin(pos);
tSales.setLeftMargin(400);
tSales.setWidth(50);
tSales.setCSSClass("bold right");
});
Here is how it looks:
Repeating Charts
If you iterate over block containing many resultset cells, it makes sense to create a chart for each block. The result is a kind of hand-made trellis chart. Key for this feature is the setDattaSelection API that our chart component has. Also many other builtin and SDK component have such and API.
For a sample let’s go back to the very first resultset, having gender and measures on columns and marital status and store city on rows.
For the following simple script we can create one chart per marital status:
var selections = DS_1.getDataSelections({
"marital_status":"?"
});
selections.forEach(function(selection, index) {
var chart = COMPONENTS.createComponent(ComponentType.com_sap_ip_bi_VizFrame);
chart.setDataSource(DS_1);
chart.setDataSelection(selection);
chart.setTopMargin(index * 300);
});
The result shows two charts, one with all data for married customers, and one for singles:
But we can also iterate over multiple dimensions together. If we iterate over both, marital status and store city, we create one data selection per row of our resultset. This is a genera rule of thumb: If you want to iterate over all rows, iterate over all dimensions that you currently have on you rows axis.
var selections = DS_1.getDataSelections({
"marital_status":"*",
"store_city": "*"
});
selections.forEach(function(selection, index) {
var chart = COMPONENTS.createComponent(ComponentType.com_sap_ip_bi_VizFrame);
chart.setDataSource(DS_1);
chart.setDataSelection(selection);
chart.setTopMargin(index * 300);
chart.showTotals(true);
});
This time we also include subtotals and overall totals and configure the chart to accept also totals.
The result is a long page with many charts – one per line of the resultset. But each chart only shows the distribution of sales and costs for a single store city. Here is the start of the screen:
Some Expert Facts
If you iterate over multiple dimensions, the data selections are returned in “English reading order”, first left-to-right and then top-to-bottom. If there are duplicates (two data selections are identical), they are removed automatically.
If you need some special behavior, you can also nest iterations. The following script does almost the same as the one before, but is much harder to understand 🙁 .
var selections = DS_1.getDataSelections({
"marital_status":"*"
});
// outer: iterate over marital status
selections.forEach(function(selection, index) {
var selectionsInner = DS_1.getDataSelections({
"marital_status": selection["marital_status"],
"store_city": "*"
});
// inner: iterate over store city
selectionsInner.forEach(function(selectionInner, indexInner) {
var chart = COMPONENTS.createComponent(ComponentType.com_sap_ip_bi_VizFrame);
chart.setDataSource(DS_1);
chart.setDataSelection(selectionInner);
chart.setTopMargin(index * indexInner * 300);
chart.showTotals(true);
});
});
And I did lie a bit so far about data selections: as you could have multiple members selected for a dimension, the data selection JSON contains the members wrapped in arrays. During iteration you typically have exactly one member in a data selection, but if you loop over the content you will see the difference. Here is a not 100% correct script to convert a selection to a string and show it in a dialog. Maybe in a future release we could provide such a JSON.stringify as a builtin feature.
var selections = DS_1.getDataSelections({
"marital_status": ["M", "S"],
"store_city": "*"
});
selections.forEach(function(selection) {
var s = "{";
selection.forEach(function(members, dim) {
s = s + dim + ": [";
members.forEach(function(member) {
s = s + member + ",";
});
s = s + "],";
});
s = s + "}";
APPLICATION.alert(s);
});
Conclusion
I have only touched the possibilities that you get with the new feature. Some are shown in the excellent Dashboard Sample that my collegue Christina Mast have created and which comes as a template in Lumira Designer 2.1. E.g. you can combine the repeater pattern with the Adaptive Layout container. You find this in the app “SAP_DEMO_CITY_ANALSIS”. This app also uses COMPONENT.copyProperties to apply several properties from a “template” chart to the newly created chart.
Or you could create a composite and repeat instances of it. A sample can be found in “SAP_DEMO_CITY_ANALSIS_ADVANCED”. That app needs some preparation, because the component type of the repeated composite depends on the document name/cuid. Instructions are shown when you open the app in Designer.
Again I’m looking forward for your comments, questions, or suggestions.
I plan a few more blogs, maybe you have some ideas or requests.
Thanks for the great examples.
Dynamically creating components in Lumira 2.1 is a true game changer!
These two components are really game changers are Mike said 😀
One of best blog I read recently. Very neatly presented in a step by step manner. Great job Reiner.
Thanks 🙂
Great post, Reiner! I’m looking forward to trying this out.
Excellent information . Thanks for sharing.
Very interesting indeed! In the past, we've used the Data Iterator SDK component in combination with 'Bring your own datasource', in order to do calculations on our data and store the results into a new dataset.
Hello Roy,
"normal" data source like BW queries and HANA views are not intended to be updated or modified. Exceptions are of course planning scenarios where data is entered interactively or via a planning functions or planning sequence. The only API which would generically make sense to those data sources is something like a setDataAsString() - but there could be many error situations that scripts would need to handle. Thus I think it makes more sense to keep such features in specific SDK data sources.
Best regards,
Reiner.
Thanks for the response Reiner.
The underlying need here is for a front-end design time calculation engine that could do at least basic calculations on the data (division, multiplication, count distincts etc). I know that there is already an option to do simple calculations in the initial view, but sadly, this doesn't work when obtaining the data through a universe.
I guess for now, we'll stick with the SDK component and the newly added iterating possibilities (which is already a big step forward).
Hi,
we really appreciate these features released with Lumira 2.1. But we miss commands like for loops to iterate defined times... Could we expect something like that in the near future?
Thanks!
Great Post!
is it possible to set the chart type i.e. if you have a combined chart of Bars and Lines?
thank you
Very interesting Blog!
One function that i miss however is changing the dimension. For example:
DS_TAB1_3_1.getDataSelections({"(MEASURES_DIMENSION)":"00O2THETEZYSRZW7I5P442C6T","0COMP_CODE":"*"});
works fine as long as the datasource always has the Company Code. If I want it truely dynamic i need to replace "0COMP_CODE" by a string variable. This however is not accepted by the script engine. I can put any string there but no variable. Is to possible to bypass that limitation?
Thanks in Advance!
Best Regards,
Dominik Herrmann
Hello Dominik,
you are right: The JSON syntax of our data selection expressions does not allow a dynamic dimension name.
Therefore in 2.2 I have added two things:
If you need the feature urgently in 2.1, you could create an SDK component with an ZTL API like
Regards,
Reiner.
Hi Reiner
I have tried to use the new Convert.stringToDataSelection() in 2.2. I had no such luck writing the code. So how would you rewrite the code string provided by Dominik?
This is the one I did have any luck writing
var mes = GLOBAL_SCRIPTS_1.measure(DS_2, "Profit");
var dim = "DS:2,DIM:id_46";
var dimValue ="West";
var dataSelectionMes = Convert.stringToDataSelection(); dataSelectionMes["(MEASURES_DIMENSION)"] = [mes];
var dataSelectionDim = Convert.stringToDataSelection(); dataSelectionDim[dim] = ["?"];
var selections = DS_2.getDataSelections({
dataSelectionDim,
dataSelectionMes
});
selections.forEach(function(selection, index) {
var cell = DS_2.getData("", selection);
var member = DS_2.getMember("DS:2,DIM:id_46", selection);
TEXTAREA_1.setValue(TEXTAREA_1.getValue() + member.text + ":\t" + cell.formattedValue + "\n");
});
But it does not work
Br René
Hello René,
I didn’t try it, but something like the following snippet should work in 2.2.
Regards,
Reiner.
Hi Reiner
Worked perfectly - thanks
Hi Reiner
I tried to create a function
GLOBAL_SCRIPTS_1.measure (DS_2, “Profit”);
but I did not succeed.
Help with the script code, please.
Regards, Mikhail
Hi try and make a global script
var measure_id ="";
var measure = DS.getMembers(DS.getMeasuresDimension().name,999);
measure.forEach(function(element, index) {
if(element.text == Name)
{measure_id = element.internalKey;}
});
return measure_id;
where
DS is an input parameter - datasourcealias
Name is an input parameter - string
It's just to find the KF techname via the description (remember that change in description and language versions can have a consequence)
Br
René
Hi,
actually I try to build up an dynamic kpi cockpit with tiles. I have designed my tile as a composite and now I try to populate my cockpit with some tiles.
I am using the adaptive layout container and there I would like to create each tile in a block.
Isn't it possible to add blocks dynamically to the adaptive layout container?
I can only find the moveComponent function: ADAPTIVE_LAYOUT_1.moveComponent(7, tile);
But then I have to create many blocks manually.
Thanks in advance!
Christoph
Hello Christoph,
as far as I remember some of the Feature Samples that come with Designer also does something similar - adding KPI Tile composites into an Adaptive Layout. There you should see how my colleague did it.
Regards,
Reiner.
Hello Reiner,
Appreciate the post and the knowledge you post.
Can this be used with Planning functionality of Lumira Designer? We are building a Marketing Planning dashboard in which users will select the Sub Category they want to add to Campaign. Below is what we are trying to do
Can this be achieved using Dynamic Apps?
Please see the attached picture may be it will help.
Helo Gaurav,
what I explained in my blog is to dynamically create visual components from an existing result set. What you seem to want is the opposite: you want to modify the result set dynamically - in your case you probably want to add so-called "new lines" by scripting and prefill some dimension members - also by scripting.
This is not possible - there are no script APIs so far to modify a (planning-enabled) result set.
However our Crosstab in planning mode has nice support for new lines including value help for all member combinations, thus I think it should be good enough.
Regards,
Reiner.
Hi Reiner,
I get a query, composed only of characteristics (AUTHOR, TITLE, DOC, PAGE). The result of the query give a list of publications.
Initially, I displayed the result in a crosstab, in the Lumira dashboard:
But I was asked to display the list as a list of publications, i.e.:
Author 1, Title 1, Doc 1, Page 1
Author 2, Title 2, Doc 2, Page 2
Author 3, Title 3, Doc 3, Page 3
Author 4, Title 4, Doc 4, Page 4
…
How can I do that?
Thanks a lot
Joris
Version of Lumira: SAP Lumira Designer - Release 2.4 SP0 Patch 4 (Version: 24.0.4)
Name of the datasource in Lumira: PUBLICATION
Name of the Text component in which I want to put the publications list: TEXT_PUBLI
I tried a lot of things but the one I thought work is:
2. Author 1, Title 3, Doc 2, Page 2
3. Author 2, Title 1, Doc 1, Page 3
4. Author 3, Title 2, Doc 3, Page 1
I solved it!
Firstly I was obliged to add a KF in my query because DS.getDataSelections() doesn't work without KF.
Then my code is:
It works perfectly.
Please note, there is a warning with the "?" ("TITLE":"?") in the script but no issue with it.
The warning is "Value "?" does not exist for "TITLE" in connection "my_Connection"".