Get last Injury date at Bex from infocube

[former_member657893]

Hi

I have calculate no of days = sy-datum- last injurt date at bex .

In cube I am getting Iast injurt date at company code level. But at bex i wnat to use last injurt date. I am not using any key figures here?

example

comp code  last injurt

1000    20th jan

1000  23rd jan

In bex  no of days = 30th jan ( sy-date) - 23rd jan

please let me know what is best possible solution?

[former_member182470 · Accepted solution]

Hi,

Creating Condition will not help here.

Convert Last Injurt date as KF by creating a Formula Variable(Replacement Path)

You have to use Exception Aggregation as Maximum on above KF with reference char as Last Injurt Date. This will consider the latest date.

Finally create a new CKF by using 0F_ADAY Formula variable - above KF.

Regards,

Suman

[former_member657893 · Accepted solution]

Thanks Suman.

I will check and come to you. by the way wher is option to assign points. If I click helpful means points will assign automatically?

[former_member182470 · Accepted solution]

Yeah, Helpful means 5 points..I am sure my approach will be useful..

[former_member657893 · Accepted solution]

I posted query in sap bw forums sap standard end routine for ehsm dso's, please let me know if u have experience in that area?

[former_member182470 · Accepted solution]

Condition is only to display the required record in the report. You can toggle it to On/Off. That means it is not actually restricting the records.

But exception aggregation will get you the latest record as per your example of data. I believe you want second record and the date has to be picked from that record. That's why I feel my suggestion would work.

You can try with either Maximum  or Last Value in exception aggregation. You can try all others sugestions as well.. Our ultimate goal is to provide solution to you.

[anshu_lilhori · Accepted solution]

Exception aggregation does not hide the record..It just changes the aggregation behavior.

Let me clarify if in your cube you have records are like this:

Comp Code   Date
1001            20th jan
1001            22th jan.

In report you expect :

Comp code  date
1001            22 jan

If you apply exception aggregation on  the date kf with maximum or last value reference char--date.it will display the max date in result.so basically it did not hide any record.

Whereas the approach which i mentioned is apt in your case and same has been shared in the blog.

You can test on the same on the system.

Regards,

AL

[former_member182470 · Accepted solution]

Mr.Suneel,

Try with both approaches and see whether you will be able to fulfill your requirement.

If you try with condition :--

Make sure you have to keep "Last Injury Date" in Rows or in drilldown to enable the condition active. Otherwise it won't be applied. This is where I am not getting convinced as we don't know whether you can put this field in rows?? Toggling can make your report weird. Moreover conditions are not supported in BO.

What is your report layout? Can you show a excel screen shot???

If you try with Exception Aggregation :--

All my replies can be referred here.

Regards,

Suman

[anshu_lilhori · Accepted solution]

Moreover conditions are not supported in BO.

User has not posted anything related to BO..I do not understand how you have assumed it..If offline requirement have been shared then its a different case altogether.

If date is of type char and if we convert it with the help of replacement path then also it is mandatory to keep it in rows otherwise does not work..

Regards,

AL

[former_member182470 · Accepted solution]

User has not posted anything related to BO..I do not understand how you have assumed it..If offline requirement have been shared then its a different case altogether.

I have observed author's other posts today. He is making all his Bex queries for BO. So I guessed it..

[former_member657893 · Accepted solution]

will update u tomorro
w to ur gmail id?

[former_member657893 · Accepted solution]

Hi

in Row I am using Month and YTD

remaining columns are key figures for Month and YTD

bottom added extra 2 key figures, They want to get last injurt date at company code level, not data based on month or ytd from cube. I am not sute to use in rows or column as last in jurt date, will be possible solution like this.

calculation: sy-date-last injury date/ no of days.

please let em know how best we can achieve?

[former_member657893 · Accepted solution]

Hi Sumna/Anshu

any answer for my report layout calculate last injurt date?

its additional key figure add at bo as extra line and need to fill value?

[former_member657893 · Accepted solution]

The other way use 1 extra dso only comp code and last injury date and use in MP to use at bex? I am not sure how its impact for transactions from other cubes?

[anshu_lilhori · Accepted solution]

Your layout is not clear to me atleast..Please explain and rephrase the requirement in detail then we can think of the approaches possible..

Also state clearly whether this query will be used as input in BO tools or not so that we can tell what is supported there and what is not.

Hope you got my point.

Regards,

AL

[former_member182470 · Accepted solution]

You may try to raise a new post with clear cut requirement.

[former_member657893 · Accepted solution]

Hi Suman

I tried  calday scenerio based on location

I have same location for multiple record, with different dates. I converted calday as formula and max value with respect to location. I am getting X value when i execute the report?

[former_member657893 · Accepted solution]

I am geting

incident no  Incident location   Injured person     injury date   no of days lost

above char and kf are at bex rows and columns.  at bottom excel sheet they are used calculation as last injurt date = sy-date- last injurt date. I am creating bex as input and BO is output.

1    1000     rama  Jan 1st 2013  10

  2    1000  jan       jan 25th 2014  20

2      1000  ram     Jan 25th 2014  30

3      1000  gan      jan 29th 2014   40

Report I need to get last Injurt date as: Jan 29th 2014 at location level. present data location as 1000, for other location may be like 2000  I need last date . when user will execute at location level. they need to get last injurt date for each location level, then the calculations required ?  please let me know still information required

[former_member182470 · Accepted solution]

Hi Suneel,

Your records are differentiated with Date field only, right? So you have to use date field only as reference char. You may refer below wonderful doc on Exception Aggregation concept. You may try with Anshu's suggestion as well.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/f0b8ed5b-1025-2d10-b193-839cfdf73...

Regards,

Suman

[anshu_lilhori · Accepted solution]

Why don't you try to fix this at modeling level by taking this data into dso..

Make location as keyfield and rest all as data fields.

So when you will load the data then only the last record will get update for a particular location and rest will get overwritten..Same will happen for other location as well.

Hope this makes sense.

Regards,

AL

[former_member657893 · Accepted solution]

So

I can connect that DSO in my MP and use at bex.

this is another solution my end.

is there any way to get at bex level from virtual char or any code?

[former_member182470 · Accepted solution]

Have u tried with the given doc for maximum?

[former_member657893 · Accepted solution]

Hi

I used max and last value with repect location. I am getting x.

[former_member657893 · Accepted solution]

when i am using formula with replacement for just date and selected propert as date , when I am executing my query date getting x. when I am using as key figure?

[anshu_lilhori · Accepted solution]

For replacement path to work you need to keep the reference characteristic in rows otherwise it does not work and same is happening in your case as well..

So if you need to keep the date char in rows and show last value then it does not serve your purpose.

So best is to handle this at modeling level only.

Regards,

AL

[former_member657893 · Accepted solution]

Thanks Anush.

I am not using any key figures, this is problem with exception aggregation also. Ideally exception aggregation u can use for key figures with respect characters? am i correct?

[former_member182470 · Accepted solution]

The fact that you are getting X because, you have not used date field in rows or in drill down. Please use that to avoid X.

[former_member657893 · Accepted solution]

Suman

What I am going to achieve If I will keep date fields again in Rows, So in columns as per ur logic I can get last calday value.  Can I use date at rows alyways Hide?

[former_member182470]

Too much of speculation will confuse you. Try the suggestion and check how data is appearing. If not ok, then you can ask me..

[former_member657893]

Suman

I used calday at roes and used repl path to get calday as formula and exception aggregation max and last value with respect location, I am getting all date instead of last valu

[former_member182470]

Try with "Last Value" and refer char as "Injury date" field and keep Injury Date in rows also.

[former_member657893]

system is not accepting ref char again calday ( here calday injurt date both are same for test).its giving error.

[former_member182470]

First define your Formula variable with refer char as Injury date and apply Last value on that whole KF. After that keep Injury date in rows, it will allow.

[former_member657893]

Hi Suman,

I am not using any key figure my scenerio?  I done as per ur suggestions created 1 formula variable in new formula at column  and create send formula and get date in my 2nd formula and applied last valu with respect calday, system is giving error?

[former_member182470]

Please open a new thread with you complete requirement and give a sample layout of your desired report.

Do you just want to chars without any KF?

[Former Member]

1.use any KF from the cube and dummy Kf and hide it

2.use exceptional aggregation LAS w.r to injury date. Logicaly this will give the same result as taking different of sy-datum & injury date.

-jijo

[anshu_lilhori]

Hi,

For current system date you can use formula variable 0F_ADAY .

To show only the last date you can refer my blog.This way you can get the desired output it seems.

Hope this helps.

Regards,

AL

[former_member657893]

Hi Anshu

I am not using any key figure for calculations like to restrict.

just last injurt date I want to get from cube to bex

by the way aftre 4 years i am using again sdn forums, where is place to assign points.

[anshu_lilhori]

Definitely what i suggested is going to work in your scenario.

Exception aggregation will not work as it will give the latest date in result part.

See you have Comp code and date.

Convert date char into Kf with the help of formula variable with replacement path.Link for it as well mentioned in my blog.

You can try and test both the ways.

Regards,

AL