Blogs / SAP / SAP BusinessObjects / SAP Design Studio / Tips and Tricks / Tips & Tricks: Using getMembers() in SAP BusinessObjects Design Studio for a BEx Query with condition

Tips & Tricks: Using getMembers() in SAP BusinessObjects Design Studio for a BEx Query with condition

Aug 6, 2015

SHARE

Have you ever tried using the getmembers() function in SAP BusinessObjects Design Studio in the query that has a condition? If yes, you would have faced an issue in retrieving “ALL “of the members instead of the set of members that satisfies the condition.

This blog will help to guide you in overcoming this problem with the getMembers() function when the Query  has a condition. Additionally, you can also refer to this recent discussion on SCN for further insight.

Scenario

To help understand the solution, let us first consider the following situation:

  • You need to display Top 10 members of a dimension in a chart from one datasource ( DS_1)
  • You want to see the performance of these 10 customers across various years, and so you will need to pass the 10 members shown below to another datasource (DS_2) as a filter.

How to do it

First, add the Query with the condition as a datasource (DS_1) and assign it to the chart. The chart will show Top 10 members and their respective key figures.

Tips & Tricks: Using getMembers() in SAP BusinessObjects Design Studio for a BEx Query with condition

In order to pass these 10 members to another datasource, you must use getMembers() function-

DS_1.getMembers(Dimension_Name,MaximumNumber);  

The above statement will return an array of members.

In our example, DS_1 is the Query with condition Top 10; hence the statement shown above should return a member array with 10 members.

However, the array will contain “all” the members instead of 10 and will not be restricted to the condition (Top 10) specified in BEx Query.

How to verify it

Enter the following code in the ‘on startup’ field of the application property

 var getMembersArray=DS_1.getMembers("DimesionName", 100000);
 APPLICATION.alert("The array contains "+getMembersArray.length+" members");

When you run the application, the following alert will be displayed. This alert shows that it does not return the array of 10 members, but all the members.

Tips & Tricks: Using getMembers() in SAP BusinessObjects Design Studio for a BEx Query with condition

Let me explain how to achieve this correctly in detail:

Step 1

Create a Query – Query1 with Condition (Top 10)

Tips & Tricks: Using getMembers() in SAP BusinessObjects Design Studio for a BEx Query with condition
Tips & Tricks: Using getMembers() in SAP BusinessObjects Design Studio for a BEx Query with condition

Step 2

Create a copy of Query1 and save it as Query2. Remove condition in this Query 2.

Step 3

In the query 2, add the dimension in characteristic restriction and restrict it with a variable.

Tips & Tricks: Using getMembers() in SAP BusinessObjects Design Studio for a BEx Query with condition


Step 4

  1. In the General tab of the ‘Change Variable’ dialog box, define the variable description and technical name as required. Select Replacement Path under Processing By option.
Tips & Tricks: Using getMembers() in SAP BusinessObjects Design Studio for a BEx Query with condition


2. In the Replacement Path tab, select Query option under “Replace variable with” and then provide the technical name of Query 1.

Tips & Tricks: Using getMembers() in SAP BusinessObjects Design Studio for a BEx Query with condition

Step5

Save the Query and add this Query as a datasource (DS_2)  in your SAP BusinessObjects Design Studio application. The datasource DS_1 may be removed now.

Step 6

1. Assign this datasource DS_2 to the chart which had DS_1 as datasource.

Tips & Tricks: Using getMembers() in SAP BusinessObjects Design Studio for a BEx Query with condition


2. To check whether this datasource contains only 10 members, try the same code as mentioned above for DS_2 in the ‘on startup’ field of the application property:

 var getMembersArray=DS_2.getMembers("DimesionName", 100000);
 APPLICATION.alert("The array contains "+getMembersArray.length+" members");

Tips & Tricks: Using getMembers() in SAP BusinessObjects Design Studio for a BEx Query with condition


Step 7

To pass these 10 members as filter to another datasource, type the following code:

var getMembersArray=DS_2.getMembers("ZR_CUST", 100000);
 var referenceString="";
 getMembersArray.forEach(function(element, index) {
 referenceString=referenceString+element.internalInternalkey+"/";
 });
 DS_3.setFilter("ZR_CUST", referenceString.split("/"));

Explanation

The internal key of element1 to element10 are appended in reference string, separated by “/”.

referenceString= internalkey1/internalkey2/internalkey3/internalkey4…….internalkey9/internalkey10

This string is split using “/” and passed as a filter to DS_3.

By using Query as replacement path, the members of the Query that satisfy the condition can be retrieved in SAP BusinessObjects Design Studio. I hope the above mentioned steps would have solved the problem of using getMembers() function in a query that has a condition.

Got questions? Click here to reach out to us.


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!