Question:
Hi
I have a reporting scnario where we would like to know the cost per tonnes for each line and for the sales order in total.
The costs are created for different legs and one leg could be split into multiple deliveries (costs could come from different vendors (like leg B in this example)).
The quantity for the sales order is 1 TNE – the transportation is split into 3 different legs.
Summation of Cost/t (correct sub-totals – wrong total):
Cost (EUR) Delivery quantity (TNE) Cost/t
Leg A 200 1 200
Leg B 50 0,5 100
Leg B 50 0,5 100
Leg C 100 1 100
===============================================
Total 400 500
However we could add a ratio (Delivered quantity divided by sales order quantity), and this would give a correct total, but wrong sub-totals for the lines (cost/t = Cost / delivered quantity x ratio):
Cost (EUR) Delivery quantity (TNE) Ratio Cost/t
Leg A 200 1 1 200
Leg B 50 0,5 0,5 50
Leg B 50 0,5 0,5 50
Leg C 100 1 1 100
===============================================
Total 400 400
Is there any std. feature that you can use to handle this requirement or do you have to use two seperate colums in the report?
Appreciate all creative input
Cheers,
Tom
[Note from the "cleaning lady", please don't press "submit" several times in a row. Ch]
Answer:
I prefer to use VB macros for anything not in the standard feature of BEx.
_________________
Keep smiling
Answer:
This looks (guessing- as I don't understand your example) like a 'before aggregate' problem. As you are doing a divide at the lower line level so total will always be wrong.
When using a divide or multiplication you need to be very careful with the totals.
If I am right then look at before aggregates in the BW online help and use a constant where possible. This will force BW to take the line into consideration when calculation a total.
BW default is to aggregate then calculate ..this does not give correct answers when multiplying/dividing
You need to calculate then aggregate.
Hope this helps
R
Answer:
I solved the problem using LEAF funtionality in the Boolean functions. Defined 3 different keyfigures (one showing the correct sub-totals and one showing the correct total - both hidden). The third keyfigure will (using the LEAF functionality) show the correct sub total and totals.
Thanks to both of you for taking the time
Cheers,
Tom
Answer:
Thanks, something new learnt!
_________________
Keep smiling