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

Using-getMembers-in-SAP-BusinessObjects-Design-Studio-for-a-BEx-Query-with-condition.png

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

Query-in-SAP-BusinessExplorer-BEx.png

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

Conditions-in-SAP-BusinessExplorer-BEx.png

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.

Variables-in-SAP-BusinessExplorer-BEx.png

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



  

Replacement-path-in-Variables-in-SAP-BusinessExplorer-BEx.png

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.

Selections-in-SAP-BusinessExplorer-BEx.png

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.

Visualizing-Others-in-SAP-BusinessObjects-Design-Studio.pngVisualizing-Others-in-SAP-Design-Studio.png

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 🙂

.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Sebastian Rothmund

    Thanks for this nice tutorial!

    There is an easier way without any Query conditions. In Design Studio 1.5 in advanced properties of the pie chart there is an option “Data Transform”. Click Enable and set the “Bin Number” which is representing the Top N value (including others). The others are grouped together in the chart.

    Best Regards,

    Sebastian

    (0) 
    1. Karthik Swaminathan Post author

      Hi Sebastin

      Thanks alot for the information! .“Data Transform” option on Pie chart – definitely a good learning.

      As you have mentioned we can use this technique in other scenarios except pie chart or where we wish to see data for other conditions except top N.

      Thanks

      karthik S

      (0) 
  2. Naveen Chand Yalamanchili

    Hi Karthik,

    I have gone through your blog and let me first say thanks to you since this fits our requirement but I am confused a little bit. I see Step 3 was missing in your document. I followed the same procedure as you told but having some doubt on the procedure I followed. Could you please clarify whether am doing the right way?

    1. I created a query Q1 which displays the Top 10 customers.
    2. I copied the same Q1 as Q2 and removed the condition and in Characteristic restrictions I am restricting the Customer with Variable of replacement path using Q1 since it doesn’t allow to add it anywhere else.
    3. I created another query Q3 in which I am creating 10 selections for Customer in Columns and restricting them with Characteristic variables of type Manual Input/Default value and in 11th selection I am excluding the above 10 variables to display OTHERS. These variables are being used in the scripting in Design studio.

    Is the above approach correct or do we need to do any changes? Kindly help me in clarifying the above confusion. Also a bit detailed steps on approach to be followed in Design Studio.Your help is much appreciated

    Regards

    Naveen Y.

    (0) 
    1. Karthik Swaminathan Post author

      Hi Naveen

      Thanks and happy to hear that this solution fits your requirement.

      In Q2 you need do restrict the customer dimension to the variable ( replacement path from Q1) in Characteristics Restriction.

      If you want to display them in a chart you would want to show the name of the customer in the chart so you will have to use Text Variables as the labels of each selection in Q3. Each of this text variable will be a replacement path text variable from the input variable you have restricted inside the selection.

      The values of these variables has to be passed from design studio as mentioned in Step 5

      var topcustomers=Query2.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

      }

      });

      Thanks

      (0) 
  3. Shlomi Weiss

    Hi

    Thanks for the post it is very useful

    I do have an issue,

    When I add the new data source to D.S it asks for values to the 5 variables, saying they are mandatory

    Capture99.PNG

    It seems as the variables in the query are mandatory, I’ve checked and they are not

    What am I doing wrong?

    Shlomi

    (0) 
    1. Matthias Feldle

      Hi Shlomi,

      did you already fixed your problem?
      I have the same problem with the variables and no idea how to fix the problem.

      Thanks for your feedback and best regards
      Matthias
       

      (0) 

Leave a Reply