How to use Arrays in SAP BusinessObjects DesignStudio for Trailing and Following N Months

One classic requirement while developing dashboards is to visualize a particular KPI trend. In most cases, the trend analysis view would be depicted as a Trailing Period from the current month or Quarter or day.  At times, there are scenarios where customer wants an option to change the N in Trailing N months/quarters/days at run time (N being the number of months).

In this blog, I will explain my particular scenario and how I implemented a solution to change the Trailing N months (this concept can also be used to calculate for following N period such as year, quarter, month, date)

Scenario

The requirement was to display a chart with Trailing 12 Months of data from the current month and the end user should have an option to change the Trailing months to 6,12,18,24 etc., during run time.

How this is generally done

This scenario can be solved in various ways. Here are a couple of ways that is often used:

Option 1 – Using Multiple Queries

To show Trailing 6 Months of data, you have to create a query in SAP Business Explorer with Month dimension restricted to a current month variable.

This variable should be defined as a range [currentmonth variable -6 to current month variable].

Since the offset specified for a variable cannot be changed dynamically at run time, you would end up using multiple Queries for every trailing N Months, which in turn will increase the load on the dashboard.

Option 2 – Create One Query with two variables

Create a query in SAP Business Explorer and restrict the month dimension with two variables: 1.Period_From and 2.CurrentMonth Variable

Calculate the N^th month in SAP BusinessObjects Desgin Studio using an if-else logic and pass the value of the N^th month to Period_From Variable .

Although this method uses only one Query, it triggers the variable for every selection made in the radio button for Trailing N month Selection. This increases the render time of the charts used in the dashboard.

How this can effectively be done using Arrays in SAP BusinessObjects Design Studio

In my dashboard, I adopted an approach that uses the Arrays in SAP BusinessObjects Design Studio to effectively change the numbers of trailing months to be displayed. Here is what I did:

Using One Query without any variables

First Step was to determine the current month from SAP BusinessObjects Design Studio using the following code –

1. APPLICATION.getInfo().dateNowInternalFormat.substring(0,6);

The output for this script was the current month displayed in the format “YYYYMM” – “201505”.

In my dashboard, I have a column chart that has an SAP Business Explorer query as the data source assigned to it. Above this chart, I used a radio button group to provide users an option to choose the number of Trailing Months. I added the number items to the radio button in SAP BusinessObjects Design Studio directly as shown below:

In the Onselect() event of the radio button, I used the following script:

/*Array_1 to find the index of the current month*/

var montharray=DS_1.getMembers(“0CALMONTH”, 10000);

var find=””;

1. montharray.forEach(function(element, index) {

if(element.internalKey===current_month)

{

            find=index+””;

}

});

/*Calculate the difference between the index and the N value*/

var from=””;

var indexdifference=Convert.stringToInt(find)-Convert.stringToInt(RADIOBUTTONGROUP_1.getSelectedValue());

/*Array_2 to find the trailing Nth month */

1. montharray.forEach(function(element, index) {

if(index==indexdifference){

            from=element.internalKey;

}

});

DS_1.setFilter(“0CALMONTH”, {“low”:from,”high”:current_month});

Keep reading for a more logical explanation of the script.

Logic

  Step 1 – In the script, I used an array – Array_1  to find the index of Current Month

Step 2 – I found difference between the index and the N value selected. Then I used the result as a new index.

Step 3 – Using another array – Array_2, I obtained the Trailing N^th month using the new index.

After obtaining the two months, I used the setFilter API to filter the data for the selected value range.

DS_1.setFilter(“0CALMONTH”, {“low”:from,”high”:current_month});

Output

On executing the application, I was able to change the Trailing N months by clicking on the desired radio button during runtime. The output for my selection of ’18 Months’ is shown below:

Conclusion

I believe this method is more effective for any dashboard design as it reduces the number of queries to one and also eliminates the Query interactions for every selection made to change the N value. You can also try the same logic to achieve the same results for following N Months / Quarters / Days. I really hope this blog was useful for you!