Retrieving a Dimension Member From a Dataset

[former_member239696]

Hi All,

I have the following scenario. I need to retrieve the last comment entered to be displayed in a text box. This can be found using a unique ID number in a dimension and getting the largest number. There is a prompt on the BEx query which is filled in by a scorecard which precludes me using this as a prequery.

So the data looks something like this:

Fiscal Period (single value here, only getting the current FP)

Notification ID (multiple values)

Current Comment (multiple values)

Notification ID Key Figure.

What I need to do is to retrieve the current comment dimension from the latest notification ID. I tried converting the notification ID to a key figure and then applying a condition, but that isn't applied when working with the datasource. It works fine when displaying everything in a crosstab, but I need it to display in a text box.

Any ideas on how to loop through the retrieve the dimension value?

We are using Design studio 1.6 SP3 with BEx queries from BW 7.4.

thanks in advance for any assitance provided.

[former_member239696]

We ended up using a crosstab and then using CSS to strip away gridlines and add formatting. It is not ideal, but it does the trick.

Thanks for the suggestions.

[MustafaBensan]

Hi Michael,

There is no possibility to loop through a data result set with standard Design Studio functionality but I will offer a few alternatives that may meet your requirement.

I assume you tried to use getMembers() to retrieve the comment dimension member with your condition-based query. The reason why this does not normally work is because a condition is treated as a filter which impacts the data result set, which is why you see the desired result in the crosstab, whereas getMembers() applies to the master data, which in this case does not consider the filter.

Here are my suggestions:

1. This is a bit of a long shot but worth trying to see if you can take advantage of a cascading filter effect with getMembers() using your BEx Query with Condition. Apply the same approach you did before but make sure the Members for Filtering option for the Notification ID and Current Comment dimensions is set to "Posted Values Only" in the data source initial view;

2. If you require a solution that uses standard functionality only, the following blog describes how to take advantage of conditions with getMembers() by feeding an additional query with a corresponding BEx Variable: Tips & Tricks: Using getMembers() in SAP BusinessObjects Design Studio for a BEx Quer...

3. If you don't mind implementing an SDK solution then I recommend you use the Design Studio SDK Data Iterator component in combination with your BEx Query with condition. In this case, the Data Iterator should only return the required row just as you experienced with the crosstab. You can then access the comment dimension from this row and set the Text component accordingly. This approach will be more efficient from a performance perspective compared to Option 2 because it does not require an extra query and does not rely on BEx variables.

Regards,

Mustafa.