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:
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
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
11 | |
10 | |
7 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 |