modelling design question - report requirement

[former_member182467]

Hi All,

We have a requirement to get the PO number and line items and the scheduled quantity for each line item along with the PO header amount value in a single row. We are getting these values in 3 different datasources right ? How can I get all the three in a single line ? I mean there are different aggregations in all the 3 datasources. Can you please advice how to do the modelling for this scenario below. I appreciate it.

We are getting PO number and PO line item from 2LIS_02_ITM DS

We are getting the schd qty and issued qty from 2LIS_02_SCL Datasource

We need the PO HDR amount from 2LIS_02_HDF DS, no problem if we can get the same HDR amount for all the line items of that PO.

Can you please advise the modelling scenario... I what key to consider while loading 2LIS_02_ITM ,2LIS_02_SCL into a DSO's and how do we get the PO HDR amount for each of the line item and how to aggregate the scheduled line item's for a PO line item.

Do we need to load 2LIS_02_ITM  & 2LIS_02_SCL into a DSO where PO number and PO line item will be the key, wont the KF get jumbled up ? How do we summarize this data with PO HDR data ?

Thank You

DR

[Former Member]

Well, in my opinoin, you need to bering all the three DS in BW via DSO and Cube. Then, you have to build your report on Multi-provider that will consist all the three cubes. Just make sure that your report contains only those characteristics which are present in all the the three targets (else, you will see your record getting split and # values will appear.). I have implemented HDR and ITM like this and guess it can be extented to SCL as well.

Rgds..

Shambhu

[former_member186399]

Dear Daniel,

Below are my thoughts

1) You need to use only 2lis_02_scl to give you the scheduled quantity or PO quantity. 2lis_02_scl has all the fields covering 2lis_02_item  including the GR and invoice quantities.

You need to create a DSO which has key PO number and line item and map it from 2lis_02_scl using the relevant process keys.

2) 2lis_02_HDR doesn't provide you the PO value and for that you might need to create a generic datasource on EKKO. Create the PO value as attribute of PO number and map the EKKO data source to this.

In the query you can use PO value as an attribute of PO number. You can formula variable if you want to show it as a separate column

Please let me know your feedback

Regards

Gajesh

[former_member182467]

Hi Gajesh,

Thanks for the reply. But we don't want the Schedule level item's on the report right, when we need the item level then how can we get the count from SCL Datasource ? I believe we can get the PO amount field from some or other datasource and no need to create a generice datasource for the same.

for example if we want only the Line item number and quantity, then we need to find out how many of them were issued right, which is not available in 2lis_02_itm.

PO1    line item 1         20     5      15      total po amount 1000$

PO1    line item 2         10     7       3       total po amount 1000$

in the above case, if we want the delivered quantity for all the items in Line item 1, then we have to combine 2lis_02Itm & 2Lis_02_scl right ? because we will not get the GR & IV qty in ITM datasource. PO amount will be there in HDR DS I believe or cant we sum up all the items in a respective PO and add the sum amount for each of the respective PO ?

Is there any alternative of stamping the PO hdr value in the Infocube instead of adding as an attritube value as its a KF.

Thanks

DR

[former_member186399]

Dear DR,

Please find my observations

1) You can get the count in SCL aslo. If you keep PO and line item in the key and in the data field you have a count keyfigure with constant 1 and which always overwrite, you will get the desired result. You neednot even map the scheduline or else you can keep them in the data field.

We had the same requirement and was met with only SCL.

2) Yes, SCL will give you the GR and Inv details.

3) PO amount, I doubt whether it is provided by HDR. Now in the query level if you bring in the item then you cannot get the summation of total PO Amount or else you will have to try exception aggregation at the query level using the PO number.

Or another method is stamping the PO amount in the cube or dso using routine. But then you run a risk of PO amount/quantity being changed later.

Regards

Gajesh