How to visualize ‘Others’ in SAP BusinessObjects Design Studio?

[Former Member]

We always get requirements from our clients to display data in their dashboards by order of rank.  For example, many clients would like to display Top N customers in a chart based on their profitability. Additionally, many business users are more interested in looking into the share of Top performers when compared to collective performance of others

In this blog, I will explain the steps to achieve visualization for Top Customers and Others within the same chart.

Our Scenario

We need to visualize the Top 5 profitable customers as well as the profitability from all other customers in a chart.

Currently, there is no direct method in SAP BusinessObjects Design Studio to do this: modify and visualize the data for ‘Others’. However, there is a very simple workaround to achieve this scenario.

Workaround Steps

1. Create a Query (Q1) in SAP BusinessExplorer (BEx) and define the condition as Top 5 for profitability Keyfigure.
2. Create a Query(Q2) in SAP BusinessExplorer (BEx) with customer dimension restricted for a variable and with replacement path of Query( Q1)
3. Create a Query (Q3) in SAP BusinessExplorer (BEx) with 6 selections for customers dimensions restricted with input variables – The values for these variables will be passed from SAP BusinessObjects Design Studio.
4. Import Q2 and Q3 in SAP BusinessObjects Design Studio and get Top 5 members using getmembers() function from Q2.
5. Pass the Top 5 members values to Q3 using setVariable() function.
6. Assign a chart / crosstab to Q3 to visualize Top 5 and Others

Since there is a conflict of retrieving the members of a query using condition, to resolve this, we will have to use another query Q2 which does not have a condition and has a replacement path variable from the query Q1. For more information on how to resolve the conflict, please refer to my previous blog on this subject.

Steps for achieving the logic

Step 1

Create a Query (Q1) in SAP BusinessExplorer (BEx) with Top 5 condition Rows – Customers

Columns – Profitability

Define a condition for Profitability with Top N Operator and Value as 5.

Step 2

Create a copy of the Query (Q1) and remove the condition.

Restrict the customer’s dimension using a variable with the replacement path from Q1.

In the General tab, give Replacement path under the Processing By option.

In the Replacement Path tab, select Query under the ‘Replace variable with’ option and type the technical name of query Q1.

Step 3

Follow the same procedure for all the 5 selections.

Name the 6th selection as “Others”. Exclude the 5 input variables for Customer dimension.

Step4

Add Q2 and Q3 as datasources in SAP BusinessObjects Design Studio. Assign Q3 to a chart and a crosstab.

Step 5

In the startup field of application property in SAP BusinessObjects Design Studio, give the following script:

var topcustomers=DS_1.getMembers("ZR_CUST", 1000); // Getting top 5 members from Query 2

topcustomers.forEach(function(element, index) {

if(index==0) // First Customer

{

APPLICATION.setVariableValue("ZKAR_VAR_C1", element.internalKey); // Passing this Customer

to the input variable 1 we have in Selection 1

}

if(index==1)

{

APPLICATION.setVariableValue("ZKAR_VAR_C2", element.internalKey); // Passing this Customer

to the input variable 2 we have in Selection 2

}

if(index==2)

{

APPLICATION.setVariableValue("ZKAR_VAR_C3", element.internalKey); // Passing this Customer

to the input variable 3 we have in Selection 3

}

if(index==3)

{

APPLICATION.setVariableValue("ZVAR_KAR_C4", element.internalKey); // Passing this Customer

to the input variable 4 we have in Selection 4

}

if(index==4)

{

APPLICATION.setVariableValue("ZVAR_KAR_C5", element.internalKey); // Passing this Customer

to the input variable 5 we have in Selection 5

}

});

/* Selection 6 (Others) gets all 5 input variables and excludes them */

Step 6

Now run the Application.

Conclusion and Considerations

You have now successfully achieved the ability to visualize‘Others’ data in a chart in SAP BusinessObjects Design Studio using this workaround.

Some limitations to consider:

1. If the number of Top customer changes dynamically, this method cannot be implemented as we are defining the number of selections in Query Q3.
2. If the number of Top customers is as big as 20, this approach will work but passing 20 variables from Design Studio to BEx will drastically slow down the performance of the application.

To summarize, if the number of Top customers are less and does not change dynamically, you can use the above-mentioned steps to visualize Others in the chart.

I hope this blog was helpful to you :smile:

.