(SDK) Pulling a Design Studio Result Set into a SAP UI5 Table
I recently came across a customer who wants to use a Design Studio result set into a custom table component, but wanted the result set as an array, instead of a JSON. Those of you who have played around with the Design Studio SDK have probably encountered the two different kinds of result sets before. The first kind comes into play when the designer is asking for a subset of the result set (e.g. a single column), she gets two files back; one with the metadata structure and the second with the actual data. The second kind of result set comes when the designer asks for the entire result set. It combines both into different forks in the same JSON structure.
This is a bit more complex than a simple array. 😉 So I created a small github project with a javascript library for doing this kind of transformation.
davidhstocker/SAPDSResultSetTransformer · GitHub
The centerpiece of the project is a javascript file containing a single function. tuplesformatTransform.js contains the reformatIntoSimpleTuples() function. reformatIntoSimpleTuples() transforms the single JSON result set into a list of tuples for easier consumption in some cases.
reformatIntoSimpleTuples() takes two parameters:
- zenResultSetJSON = the result set that you wish to transform. It must be of the single JSON result set type as described in the Design Studio developers’ Guide.
- displayType = the display type (‘key’ or ‘text’) that you wish to use. You fill your result set tuple list with either key or text values.
reformatIntoSimpleTuples() holds most of its variables in the local scope. It creates two in the global scope for use in other places:
- headerTuple compresses the (potentially) multi-leveled header into a single header line, with the stacked text concatenated into each cell.
- rowTuples contains each row with the following format: [row measure members1, rmm2, …, rmmN, row dimension members1, rdm2, …, rdmN, data1, data2, …, dataN]
So a the headers of a result set that looks like this:
| Q1 2014 | Q2 2014 | |||
| Sales | Cost | Sales | Cost | |
| Boston | ||||
| New York | ||||
| Singapore |
Would be transformed to this headerTuple: [“”, “Sales Q1 2014”, “Cost Q1 2014”, “Sales Q2 2014”, “Cost Q2 2014”]
Also included in the github repo are two sample files to demonstrate the function in action.
- entureResultset.js contains an example result set; what resultset being fed to a Design Studio SDK component might look like.
- html5.html is an example html file, with an SAP UI5 table. The html file’s script calls reformatIntoSimpleTuples(), using the sample data from entureResultset.js and puts that data into the UI5 table.
Thanks, David.
I ended up writing a similar utility library to "flatten" out the tuples into a more of a 2D array usage, but I'm sure yours takes a much more coherent approach 😉
I especially like the simpleTuples example where you are concatenating a more complex multi-leveled columns scenario. I've always called this "concatenating excess dimensions 😉 ".
Great stuff. I hope you do not mind if I take your example code and implement into a complete Design Studio extension example?
Go right ahead. I put it on Github, so that people will use it. 🙂 I do think you might need to make a small change. There is a small bug in the "concatenating excess dimensions" step. It should insert a space between the concatenated dimension/measure names, but it is not. I forgot to fix it, but cringe every time I see it in action.
Hey, David.
I think I may have run into a bug in your function. Let me explain:
Everything works great when there is only one dimension in rows, however, if I'd like to include a 2nd or more, it looks like you are only returning the dimension's member list values by index, where I think you mean to use the tuple's index that refers back to that member index.
Somewhere here in your code:
//Add the leading row labels
for (var j = 0; j < rowDimensions.length; j++) {
if (typeof(rowDimensions[j][i]) != "undefined"){
rowTuple.push(rowDimensions[j][i]);
} else{
rowTuple.push("");
}
}
Here's 2 screenshots which may help illustrate the current problem:
Initial View setup, note 2 dimensions in rows:
At runtime, note the collapsed 2nd dimension values because of this issue:
I think this can be corrected with a line or 2 of code, and if I end up with some free time, I'll post the change.
Hi Michael,
I'll have to fix that. I'd not tried it with a dataset that had more dimensions in the rows, unless you've already fixed it. 😉
Cheers,
dave
Hi Michael,
Can you help me with the code to bind the dataset to the Table UI5 control.
I realized that this works differently then the usual afterUpdate function in design studio.
Regard's,
Vipul
I had a chance to track down the bug that was causing the problem that Mike noticed and updated the GITHub project to reflect the algorithm changes in formatTransform.js. There is a new file there, called multiDimensionResultset.js, which contains a resultset json that I grabbed off of a query on one of our demo systems. Yeah, I'd not suppressed totals before grabbing the json. 🙂