BEx Get Latest Record

[Former Member]

Hello Everyone,

I have posted similar thread earlier but did not get correct response. So trying my luck again.

I have some queries to be created to get latest records, for example, to get latest document number (can be based on date).

A B C

1 1 10/10/2008

1 1 11/10/2008

1 1 12/10/2008

2 2 10/01/2008

2 2 11/01/2008

2 2 12/01/2008

I just need records 3 (for A =1 and latest C) and 6 (for A = 2 and latest C).

Did anyone implement same scenario? If yes, please tell me how you were able to do that. Any help in this regard is appreciated.

Regards,

Madhav

[former_member184494]

Try using exception aggregation for A - set exception aggregation to maximum on calday - it should come out but then query performance will suffer - else define the excpetion aggregation on calday in the key figure on the aggregation tab.

Arun

[Former Member]

Hi Arun,

Point here is none of them is a key figure. A is a characaterstic which is a document number and other is date. So I dont think exception aggregation is possible here. Any ideas??

Regards,

Madhav

[Former Member]

Depending on Scenario , Volume of Data and Frequency of your Report Execution,

1. you can create another DSO with Key combination of A and C as per your Example. and Develop a report on that DSO.

2. If Data volume is more u can Create an APD T.code RSANWB with Routine .

ABAP Code will be llike

SORT A C ACSENDING.

[Former Member]

I have certainly used this in my other reports with KFs. Try it for Characteristics not sure about it.

For e.g. if you needed the latest Document number on a particular record.

Step-1 Define a Char ZDOC_NUM with exception aggregation as the last value and assign time characteristics to it.

Step-2 Add this Char in your cube and assign the value of 0DOC_NUM. It will always store the last value.

Step-3 Create a query and use the time characteristics to bring the data. It should work.

JM

[Former Member]

Hi Saikrishna,

Thanks for your reply, I thought of options you specified. I am looking for option that can be done in BEx. I have several such scenarios and I believe creating APD or another DSO for all of them is not a viable solution.

Regards,

Madhav

[Former Member]

Hi,

Thanks for your reply. Aggregation and exception aggregation can only be specified for key figures, characteristics dont have aggregation tab. So I dont think I can follow same process in my case.

I have to change data target whenever there is a change in requirements. Also, if I need latest records of A based on some other date field D, I have to keep adding new field in data target.

Instead, I am looking for an option to get latest value of characteristic based on some date value in BEx which is much more flexible.

If anyone has any ideas, please let me know.

Regards,

Madhav

[former_member181964]

Hi,

I think you have 0CALDAY in your report, so right click and properties and you can have SORT ORDER. try with this.

Thanks

Reddy

[Former Member]

Hi,

How can I get latest record in report after soring?

Regards,

Madhav