Using getMembers() in SAP BusinessObjects Design Studio for a BEx Query with condition

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.

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.

Let me explain how to achieve this correctly in detail:

Step 1

Create a Query – Query1 with Condition (Top 10)

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.

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.

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

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.

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”);

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 thathas a condition.