Purchase register report problem

[Former Member]

Hi experts - please help

we have a requirement to create a purchase register for material from PO- to Invoice.

we have PO number -> GRnumber -> Invoice number in our rows . In our keyfigures we have PO Quantity, PO value , GR quantity,GR Value, Invoice quantity and invoice amount .

Becoz of invoice and GR number relationship which is m: m in our report we see that the key figure values are showing up like

for 1 purchase order multiple line items , with multiple gr's, with multiple invoices , becoz of which we see PO quantity field showing like say if it is 30 for 1 PO it is showing 30 for all the invoice numbers in the report which not want , the same case is with Gr quantity and invoice quantity

PO.no / po item / gr no / inv number / po quanitity / po value

1000/10,20,30,40/1000/2000,3000,4000,5000/ 30,30,30,30,30/ 120,120,120,120,120

this way for 1 PO the report is showing same po quantity based on say if 10 invoices it is showing same P0 Quantity 10 times which is not want and the result row shows up (POvalue *10).

how to get rid of this situation..

please suggest any way if anyone has done a similiar report..

any documents related to such purchase register report is highly appreciated.

thanks & regards

krishna

[Former Member · Accepted solution]

Hi Krishna,

You could create separate DSOs for PO , GR and Invoices.

In PO DSO, you can store PO value, GR value and IV value at the granularity of PO and Item.

in GR DSO, you can store GR document and item wise GR Value along with PO and Item.

Execute the first report in PO DSO and create 'Jump to' report in the second DSO.

If the user wants GR details at document level, they can jump to second report and if they are interested only in aggregated KPIs they can be happy with first report.

Thanks,

Krishnan

[Former Member · Accepted solution]

hi krishnan

Can you please explain it in detail.

thanks & regards

Krishna

[Former Member · Accepted solution]

Hi Krishna,

Your PO DSO will have records like this.

PO Item POquantity GR Quantity

PO1 1 100 35

Your GR DSO will have information as shown below.

GR Doc Item Posting date GR Quantity PO PO Item

DOC1 1 01.07.2011 20 PO1 1

DOC2 1 10.07.2011 15 PO1 1

If the report is based on PO, ( Document date of PO ), then you can show information from the first DSO. If the user wants to have the details of GR documents that constitute GR quantity of 35, you can create a 'Jump to' report on the second DSO and in the RRI Connection map the PO and Item numbers.

This way you will be able to store the information at different granularity and combine them in reporting.

Hope this explains,

Thanks,

Krishnan

[Former Member · Accepted solution]

thanks for the reply

I also need to have invoice number , quantity and value . from PO -> GRN-> INVOICE.

regards

krishna

[Former Member · Accepted solution]

Yes, you can create another DSO that has invoice document number and item in the key with following information in the data fileds.

Posting date,

Quantity,

Value,

GR Doc,

Item,

PO,

PO Item.

Thanks.

[Former Member]

hi,

create a aggregation object in the cube, the object should be a concatenation of PO & PO item (or if the qty is coming at some other detail level then aggregation object should be concatenation of those objects) .

The PO qty is coming at a PO item level, in the report create CKF on PO qty and define its exception aggregation on aggregation object.

regards,

Arvind.