SAC Scripting – Part 1: Array Handling
1 General
In the ever-evolving landscape of business intelligence, SAP Analytics Cloud (SAC) stands out as a robust tool empowering organizations to make informed decisions through intuitive analytics and compelling visualizations.
While SAC offers a user-friendly interface for building dashboards, there are instances where scripting become crucial to meet specific business needs.
In this blog series, we embark on a journey to explore some of these challenges and provide practical workarounds and improving performance.
2 Scenario
This real-life scenario describes the users need to have the ability to select and deselect a certain number of dimensions and have their table defined accordingly. Additionally, the just want to have a specific set of dimensions to choose from and always want to have them displayed in the defined order. While there is a possibility to slide out the expanded Navigation panel via scripting, we do not have a way to limit the dimensions available for navigation, but also cannot force the user to maintain the correct order.
3 Overview
Within this simple example we have a date selection, a table and a chart with the “BestRunJuice_SampleModel” available on all tenants. BestRunJuice_SampleModel
By opening the dimension selection icon, a dedicated set of dimensions is available to choose from. The selected dimension will be added to the table and chart, while keeping the dimension order. Now we want to get into the details on how to achieve this by scripting.
3.1 Scripts
Script1: Define the order of the dimension in the initialization script ( Run with the initialization of the story ) : ( Performance: 1400ms -> getDimension() takes here the most time )
In this script, we populate a string array with dimensions in the correct order as presented in the Checkbox.
Moreover, we gather all table dimensions into a DimensionInfo array. ( Performance can be optimized by also adding the description to the svar_string_A_DimOrder array, and save on filling the dimensionInfo array )
The IDs of these dimensions will then be appended to a string array.
This string array of dimension IDs is essential for achieving the accurate sorting of dimensions, because we cant use indexOf with DimensionInfo arrays ( used in Script2 ).
function initScriptVariables(): void
/* Define the order of the Dimensions for the Checkbox and analysis appearance*/
svar_string_A_DimOrder =
["Location_4nm2e04531",
"Store_3z2g5g06m4",
"Product_3e315003an",
"Sales_Manager__5w3m5d06b5",
"Date_703i1904sd"
];
/* Retrieve all dimensions of the datasource into a DimensionInfo array */
svar_DimInfo_A_AllDimensions = svar_table.getDataSource().getDimensions();
/* Fill string array from DimnensionInfo array*/
for (var t = 0; svar_DimInfo_A_AllDimensions.length > t; t++) {
svar_string_A_AllDimensions[t] = svar_DimInfo_A_AllDimensions[t].id;
}Script2: Initialize Checkboxgroup ( OnClick Event on DimensionSelection Button ): ( Performance: 400ms )
In this script we are appending the dimensions in the defined order in “svar_string_A_DimOrder” into the checkboxgroup.
function initHideDimensions(): void
CheckboxGroup_DimensionSelection.removeAllItems();
// In this loop we are initially adding the Dimension's to the CheckboxGroup in the desired order.
// Loop over the dimension ordered array variable ( svar_string_A_DimOrder ) and get the index of the dimension array and add it to the Checkbox.
for (var s = 0; s < svar_string_A_DimOrder.length; s++) {
var desindex = svar_string_A_AllDimensions.indexOf(svar_string_A_DimOrder[s]);
if (desindex !== -1) {
CheckboxGroup_DimensionSelection.addItem(
svar_DimInfo_A_AllDimensions[desindex].id,
svar_DimInfo_A_AllDimensions[desindex].description
);
}
}Script3: Remove and adding dimensions to the drill down in chart and table ( OnClick Event after pressing OK in the dimension selection Checkboxgroup ): ( Performance: 220 ms )
function updateDrillDown(): void
var selectedKeys = CheckboxGroup_DimensionSelection.getSelectedKeys();
// Defining Order of Dimensions to appear within the table
var reorderdkeys = ArrayUtils.create(Type.string);
// Reordering the selected keys into new array reorderedkeys
for (var s = 0; s < svar_string_A_DimOrder.length; s++) {
var desindex = selectedKeys.indexOf(svar_string_A_DimOrder[s]);
if (desindex !== -1) { // -1 meaning not found in the selected keys array
reorderdkeys.push(selectedKeys[desindex]); // add keys to the reorderkeys array
selectedKeys.splice(desindex, 1); // removing the added key for performance reasons
}
}
// Remove all dimensions but not measures
for (var i = 0; i < svar_string_A_AllDimensions.length; i++) {
switch (svar_DimInfo_A_AllDimensions[i].id) {
case "@MeasureDimension":
break;
default:
svar_table.removeDimension(svar_DimInfo_A_AllDimensions[i].id);
svar_chart.removeDimension(svar_DimInfo_A_AllDimensions[i].id, Feed.CategoryAxis);
break;
}
}
// Adding reorderdkeys into table
for (var j = 0; j < reorderdkeys.length; j++) {
svar_table.addDimensionToRows(reorderdkeys[j]);
svar_chart.addDimension(reorderdkeys[j], Feed.CategoryAxis);
}
svar_chart.setBreakGroupingEnabled(false); // Set breakgrouping to false in order to have the same display setting as in the table4 Final Thoughts
In this example we took a look in array handling in SAC Scripting.
In the next blog we will look into optimizing performance when looking up values in a datasource – stay tuned!