get value by latest doc then by latest revision

[Former Member]

Hey Team,

Here are my records

Material# Doc# rev# qty

1 1 0 2

1 1 1 3

2 2 0 1

2 3 0 5

2 3 1 7

2 3 2 6

I want a report with material and qty with the latest doc# and latest Rev#

Mat qty

1 30

2 6

Please let me know if this is possible....

Thanks

tb

Edited by: travis bickle on Oct 14, 2008 3:54 PM

[Former Member]

Sorry guys..

I would like the report to have...doc# and Rev# too

Material# Doc# rev# qty

1 1 1 3

2 3 2 6

thanks.

tb

[Former Member]

In the characteristic properties for doc# and Rev # in the query, set them to be sorted by Descending according to Key..which will have the latest doc # and Rev # on the first row.

Then create a condition to display only the top N ( 1 ) for each material.

This will display only the latest doc # and rev # in the report.

Ashish.

[Former Member]

hey ashish,

When I put the condition of topN(1)

what the report is giving is

for each material , it is pulling the top qty and oulling the relevant doc# and rev #.

this is not what we want...

we want for a given material give me the qty from latest doc# and rev*

hope i'm clear....

thanks n any more suggestions..

tb

[Former Member]

Looks like you have put the condition on the quantity KF...

You can create a Constsnt KF with Value 1 and try and put your condition on this KF...Hope it works..

Ashish..

[Former Member]

Hi there,

You can try two options:

1. Try to use a new key-figure with SUM as agregation but with an exception aggregation of last value.

2. Use an ODS that receives those values and has as a key the Mat, and overwrites the qty as a data field, therefore for the same material it overwrites the last value.

Diogo.

[Former Member]

Thanks Diogo.

on 1) I will try that option what if the order of the records is not sorted?

on 2) I cannnot use overwrite because i want to keep a record of all the transactions for that material.

any more ideas.

Thanks

tb

Edited by: travis bickle on Oct 14, 2008 4:43 PM

[Former Member]

Ok,

In the first option the key figure is used with summation, but as an exception of using last value. Here when you define the last value you define it based on another characteristic, usually a time characteristic, so for example you'll have a 0calday in it you can define the key figure as sum with the exception of last value based on 0calday, this way the last day it will have the last value. (it could be 0calday or any other characteristic).

In the second option you can have an ODS to be feed from the first InfoProvider (whether it is an ODS or a cube) to have only that data and feed it with deltas, and then build the query over this new InfoProvider. The problem is that you'll have one more InfoProvider with one more load...

Diogo.

[Former Member]

Set the display for doc # and Rev # to be sorted descending according to key and then use aa condition of top 1 record based on a constant calc KF.

Hope it works..

Ashish..

[Former Member]

Diogo,

1)i think I'm confused....I'm not getting the date..for one...and I need it by latest doc and by latest Rev..? 2 fields?

2)I do want to keep the history...

thanks

tb

[Former Member]

Ashish...

can you elaborate your idea a bit?

Thanks

tb

[Former Member]

Hi travis,

With the last value, this is defined in the key figure, so you define a new key figure equal to qty but as a summation with an exception aggregation, this exception aggregation should have the last value, therefore it should retrieve in the query instead of summation the last value for qty. But to "say" to the system that it should retrieve the last value, you have to "say" based on what do you have the last value, therefore normally one choose a time characteristic.

So imagine you have this:

Material# Doc# rev# qty #fiscper

1 1 0 2 01.2008

1 1 1 3 04.2008

2 2 0 1 01.2008

2 3 0 5 03.2008

2 3 1 7 02.2008

2 3 2 6 04.2008

If you have your fiscyear to be displayed in the query in columns or rows, then the system will aggregate the new key figure qty instead of summation as the last value, and for the example the last value for material 1 is 3 (because the last value of 2008 is 3) and the last value for material 2 is 6 (because the last value of 2008 here is 6).

I guess you get the picture.

Diogo.

[Former Member]

Thanks Ashish and Diogo for your suggestions and time.

i will keep you posted ..how it goes...

-tb