DS GetMembers() and BEx condition - Do they work without conflict ?

[Former Member]

Hi Experts

I have a Query with one Row - Customer and One Column - Sales . I have a condition in the Query for Top 3 Customers Based on the Sales Amount.

When I bring this Query to Design Studio , My initial View shows only 3 Customers ( as expected ). When I assign it to a Crosstab - it shows 3 customers ( as expected) .

But when I populate the customers using getMemberList() to a Listbox - I get all the customers.
When I write a script with getMembers() - I get all the Customers.

Question -

1.When the Query is restricted to only 3 customers , Shouldnt DS only fetch 3 customers all time?

2.If this is how getmember behaves, then the actual question is - For some purpose I want to pass this Top 3 customers from Design Studio to another Query as separate variables - How to achieve this without using getmembers() .

(I am using DS 1.5)

Thanks in advance for your valuable time,effort and answers

[MustafaBensan · Accepted solution]

By the way, in addition to my previous comments, if your requirement is to allow selection of one of the top 3 customers from a list in order to pass to another query, then why not just use the Crosstab for the same purpose as the List Box?

[Former Member · Accepted solution]

Hi Mustafa

Thanks for your comments.

Yes, Getmembers() ignores the condition whereas it works properly for the filters.
We just tried it out for a POC to make sure it works this way.

Thanks again for conforming the same.

[Former Member · Accepted solution]

Thanks for the observations here. I have found the same thing trying to get a condition to work with script in design studio.

Sjoerd

[MustafaBensan · Accepted solution]

Hi Sjoerd,

Wouldn't you think this behaviour qualifies as a bug?

Mustafa.

[Former Member · Accepted solution]

Hi Mustafa,

I would absolutely think of this as a bug, expecially since the condition works fine on a crosstab related to the same DS..

Sjoerd

[MustafaBensan · Accepted solution]

Yes, exactly.  Agreed

[Former Member · Accepted solution]

Hi Mustafa Bensan and S. van Middelkoopoo

First many thanks for your comments.  Before we confirm this as a bug.. I have a small doubt which may contradict my own question ...

I read these pages few days ago

Definition of Conditions - Business Intelligence - SAP Library

The key observations are

A condition in a Query just hides the members which does not satisfy the specified condition

so it brings all the data but just hides them

Ranked List Condition: Top 5 Products - Business Intelligence - SAP Library

The result row shows total of all the members unless it is altered

Now on this perspective if we have to address the question doesnt this work as expected , because it is mentioned that the condition just hides the irrelevant members but brings in all data.

I am still in a dilemma to consider this behavior as bug or not.

Thanks for your time and efforts.

[Former Member · Accepted solution]

Only SAP support can declare a behavior as bug or not when they receive the inquery in form of a ticket. So I would encourage you to open a ticket and have a support engineer take a look at this.

best regards,

Victor

[Karol-K]

Hi Karthik, all,

this what you describe is the correct behavior. You need to distinguish between functionality which is applied on Result Set and Members. Conditions are on Result Set. This is why the members are still there (just not displayed in result set). The members are read separately, independent of the result set - but through the query access.

GetMembers are respecting only the settings you have in your query. The most important one are:

Posted Values - members which are available in current result set, but before all other functions which are applied on the Result Set.

Master Data - all members from master data (eg. used for filtering)

With "Posted Values", there is another special case - in case you have an active filter on the dimension which you ask for members, this filter will be ignored. you can try it out.. here short recording.

you can see, when I filter on the other dimension (pers) - the list (1.5 - data binding to dimension PROD) is changing (eg.. member 4 is gone). but when I filter on the used dimension, the members are still there (so the filter is ignored). This is, because in other case you would be not able to change any filter in this situation (as all members would disappear, only the filtered once would stay).

It means, when you want to ask for members which are in current result set, you need to use a trick:

in short - ask for members, loop on it, ask for data. if no data, nothing is in result set.

// assure sums are shown

DS_1.setTotalsDisplay("0BC_PROD1", TotalsDisplay.SHOW);

// remove temporary the other from result set

DS_1.removeDimension("0BC_PERS1");

var members = DS_1.getMembers("0BC_PROD1", 999);

LISTBOX_2.removeAllItems();

members.forEach(function(element, index) {

  var data = DS_1.getData("4FW8C4WXM3HULQ4M1YPFT79EF", {"0BC_PROD1":element.internalKey});

  if(data.formattedValue != "") {

  LISTBOX_2.addItem(element.internalKey, element.text + " [" + element.externalKey + "]");

  }

});

then you can see the members which are in result set (and unfortunately a lot of warnings..).

In addition, two more preconditions..

* the code above is working only if sums are shown for the dimension (if the dimension is the first one)!

     DS_1.setTotalsDisplay("0BC_PROD1", TotalsDisplay.SHOW);

* if the dimension is not the first one, you need to remove all other from resulset (to make the selection simple)

     DS_1.removeDimension("0BC_PERS1");

Result:

Helps?

Karol

[MustafaBensan]

Hi Karol,

Thanks for the detailed explanation and workaround.  Given the current getMembers() functionality, I think it would be useful if a new API method could be considered in future that allowed direct access to members from the result set after filters, conditions and other restrictions have been applied.  The Top N customers example given in this question is a legitimate use case.  I'm concerned that there could be a noticeable performance impact of looping through the entire result set when there are a large number of rows if only a small fraction of those rows are needed to be retrieved.

Just a thought,

Mustafa.

[MustafaBensan]

Hi Karthik,

That's an interesting observation.  Here are my thoughts regarding your questions:

1.  I was able to replicate the behaviour you've described.  There is a known limitation of getMembers() not respecting sort order because it retrieves from the master data and not the result set.  Perhaps the condition not being respected is related to this.  Nevertheless, I agree that any active conditions should be respected by getMembers(), otherwise, as you've noted, the members returned are out of sync with those in the result set;

2.  As for other options, I tried applying the new data binding feature in DS 1.5 to populate a List Box and this also ignores the condition, as shown below:

There doesn't seem to be any other way to easily access the data with condition applied.  Given that the Crosstab binding works as expected yet the List Box binding does not, I am inclined to think this is a bug and would suggest opening an SAP Support Ticket in the absence of any further suggestions to achieve the requirement.

I suppose a workaround could be to somehow write 3 separate BEx Queries, each returning the 1st, 2nd and 3rd customers if possible but this is not a very elegant solution.  I think the preferred option would be to log this as a bug to have it reviewed accordingly.

Regards,

Mustafa.