Bex Report to show only latest record on report and then history

[former_member213525]

Hello Gurus,

Table 1:

Employee	Valid From	Valid To	Hours Perday
1900	01/01/2014	02/28/2014	8
1900	03/01/2014	03/31/2014	6
1900	04/01/2014	12/31/9999	4

Table 2:

I have above in DSO, On report I need to show the latest record without Valid From and Valid To Fields shown below.

Employee	Hours Perday
1900	4

And when user drags Valid From and Valid To field from free charac. it should show history as in DSO. I have leveraged 0VALIDTO customer exit processing variable with variable as "Value Ranges" >= Key Date so when query is executed if date entered is 04/01/2014 it should bring last record with Hours perday=4 and if date is entered as 03/01/2014 should bring two records with hours per day 6 and 4 and so on.

The problem on report is that the record being pulled is always latest but when Valid To and Valid From free characteristics are dragged on the report it still shows the latest record and no history. Is there a way that when key date is entered it should show latest record as per >= of that key date and when Valid from is dragged in to report it should display history prior to that key date?

Thanks,

Sam

[anshu_lilhori · Accepted solution]

As far as i understand if you do not keep valid from and valid to and in rows and run the report without any filter then based on the above data you should get

Employee  Hours/day
1900          18

Assuming that hours/day is a keyfigure so data got aggregated.

Now as you only need to show the latest value.to achieve the same we will apply Exception aggregation on Hour/day kf.

Make a new formula put the above kf inside that--Hit the aggregation tab--Exception aggregation as last value reference characteristic as valid from or valid to date.

This will give the result as below.

Employee  Hours/day
1900           4

So if you drag and drop valid from and valid to from free char.into row then data should get split-ted as desired.

Hope this helps.

regards,

AL

[former_member213525 · Accepted solution]

Hello Anshu,

Thanks for your inputs, I tried as per your instructions and this is what is happening.

If I enter the date 03/01/2014 it pulls one record as below. It should bring one more record which is valid as of 04/01/2014.

1900

03/01/2014

03/31/2014

6

And after I drag Valid From to the report then  the record does not bring the history, It is still shows one single record on the report. I appreciate for your help!

Thanks,

Sam

[anshu_lilhori · Accepted solution]

Sam,I just realized that if you need date as input then this can be achieved with the help of one of my blog which has almost identical scenario to yours.

You just need to create variables on the date as mentioned in the below blog.

Hope this works in your case as well.

Regards,

AL

[former_member182470]

Hi Sam,

You are almost in the right direction. A little fine tuning is required in your process

As you are dealing with Time dependent Master data, the ideal thing to use is "Key Date" in the query. But there is a limitation here. Key Date can show only single date's relevant master data. I mean if you enter 03.01.2014, only second record would be shown. Key date cannot work on Multiple dates or ranges to fetch historical data or multiple records accordingly.

To achieve your requirement, you should not use Key date in your query.

 I have leveraged 0VALIDTO customer exit processing variable with variable as "Value Ranges" >= Key Date

The above idea is good. You should make Valid From as Cust exit(i_Step =2) processed and make it as User Entry enabled. So that user exit code can pick up values as per > = Valid From(User Entry Based). But this user exit will be picking historical records if user enters other than latest Valid from date. I mean no drilldown is required. You need to use Valid from in rows also, I believe.

Try this and let me know if you want any more inputs.

Regards,

Suman

[former_member213525]

Thanks Suman,

You are right, I need to have Valid From on the report or else the report breaks to show aggregated result anyway. This is what I did.

1)Create Variable on Valid From Date--> Processing with Customer Exit--> 0CALDAY as reference Characteristics.

2)Make variable as "Ready for User Input".

3)Change variable to show Value Ranges >= above created variable.

4)Add Valid From to the rows so it shows up by default on the report.

Appreciate your help!

Sam

[former_member182470]

That's fine..test your report and let me know..

[Former Member]

Hi Sam,

One thing I want correct from Suman suggestion, If a Customer Exit variable is User-Input then it will be not available in I_STEP = 2 fro processing. Please correct me  if I am wrong.

So you need to create 2 variables 1 for user input and other for your logic based on the value entered by user that you have mentioned above.

Hope this will help you.

Arvind Venai