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:

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:

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.