Last date in sap bi bex query

[Former Member]

Hi Experts.

I have display only the latest reocrds in my report.See my below example.

I have material anc calday in my rows and amount in columns.

Ex:

Material            Date               amount

101                   01.03.2012     1000

101                    02.03.2012     2000

101                    03.03.2012     3000

In my report i need to display only the latest or last recordsi.e below one

101            03.03.2012       3000

How can i achive ?Please help.

Regards

Laxman.

[Former Member]

Hi Laxman,

In Bex query designer on Key Figure in Column/Rows--> Calculation Tab--> Select Calculate Result As--> Last Value--> OK.

Hope this will show result as you want.

thanks,

Arvind

[dkle]

Hi,

this will only differ the result line to display the last key figure entry instead of totals.

Your output would be:

Material            Date               amount

101                   01.03.2012     1000

101                    02.03.2012     2000

101                    03.03.2012     3000

                          Result           3000

102                   ...                    ...

102                   ...                    ...

But this does not fit the requirement of Laxman.

There is no way around keyfigure aggregation.

[Former Member]

Hi Laxman,

In Bex query designer please open a query --> properties--> rows and columns tab --> In supress zeroes --> select Active.

It will give you the result as you want.

thanks,

Anand

[dkle]

no, it won't.

Requirement is to display the LAST record and not to hide rows where result-column = 0.

[former_member202684]

Hi Laxman,

If your report is on a cube, you could try the below approach for the code of the Customer Exit variable.

DATA: MAX_DAY TYPE /bi0/oicalday,
         temp TYPE RSSID.
   SELECT SINGLE MAX( SID_<INFOOBJECT_NAME> ) FROM /BIC/D<XXXXX> INTO temp. 
     " XXXXX = Cube name
   SELECT SINGLE DATE0 FROM /BI0/SDATE INTO MAX_DAY WHERE SID = temp. 
     "/BI0/SDATE = SID table of the Date Info Object 
     "(the SID table for all dates is normally common as specified here)

I havent tried it though but just an approach to get the latest date from the cube through a Customer Exit variable.

Regards, Joe

[anshu_lilhori]

Hi,

Kindly refer the below thread.It might be helpful.

http://scn.sap.com/docs/DOC-25492

Regards,

AL

[former_member202684]

Hi Laxman,

We can achieve this by restricting the CALDAY characteristic with a Customer Exit Variable.

Scenario 1: If your loads happen everyday and you have the max value for CALDAY as Current Date, We can restrict the CALDAY by using any SAP Exit variable to fetch current day.

Scenario 2: If we are not aware what would be the last day, then we will have to use a Customer Exit variable with code similar to below. We will have to read the lastest day from you InfoProvider in the CMOD code.

You can do this as below:

1) Creating a Customer Exit variable

2) Write the Code in CMOD:

DATA: L_S_RANGE TYPE RSR_S_RANGESID.
 WHEN 'variable_name'.
 
 IF I_STEP = 1.
   DATA: MAX_DAY TYPE /bi0/oicalday.
   SELECT SINGLE MAX( CALDAY ) FROM /bic/aDSO_NAME00 INTO MAX_DAY.
   L_S_RANGE-SIGN = 'I'.
   L_S_RANGE-OPT  = 'EQ'.
   L_S_RANGE-LOW  = MAX_DAY.
   APPEND L_S_RANGE TO E_T_RANGE.
 ENDIF.

Try this, it should work out for the requirement that you are looking at.

Regards, Joe

[skrathore303]

Hi Laxman,

This functionality can not be achieved in BEx. You can get this done using DSO but I fear you will loose old data.

The solution is You can build this report on top of a DSO and make MATERIAL as the key of that DSO with Date and Amount as the data fields.

Now every time there is a new record or change, the old date will be over written with the new date.

Thanks

Shubh

[Former Member]

Hi Laxman

This can be achieved by using key figure aggregation LAS, with reference characteristic Date.

Last value (LAS): The last value in relation to the reference characteristic is displayed in the results row.

You may have a look at the link for more details on this: http://help.sap.com/saphelp_nw70/helpdata/en/47/607899dcce6834e10000000a421937/frameset.htm

Regards

Jhansi

[dkle]

Hi,

this solution is the only one working for me.

I created a calculated key figure for this.

But it only works if the reference field (here: Date) is NOT in the rows.

Otherwise there is no aggregation.

Regards