Sorting by Calculated Field from Totals

[Barock21]

I have created a sales report that is quite complicated from the beginning. I grouped the report by branch and then by item. I had to calculate the sales of those items within certain time period using "WhilePrintingRecords" running total. Here's the reason:

I sold 1 item at $1000 for qty of 2. 1st qty costed my company $250 and 2nd qty costed my company $500 (FIFO layers). So in the detail it looks like:
Item A $1000 $250
Item A $1000 $500
Thus, I couldn't simply sum up the sales price (since it's actually $1000 for both of them not $2000 and the total cost is $750) and had to do the "WhilePrintingRecords" above on the item footer group.

I got everything working well except, I need to sort by the GP% from the item footer group and it is not allowing me. Any advice will be greatly appreciated. Thx.

DT

 

[blom0344]

I may be looking at it from a (too) simple perspective, but from your example it looks like you could use the average() function instead of the sum() function on the column?

Ties Blom
Information analyst

 

[Barock21]

Thank you for your reply.

I believe you are referring to have the sales price on AVG. Yes that actually may work for that one specific transaction. However, it is not going to work when I would like to subtotal all these transactions since each transaction may show different profit. Thus, it will throw off my profit on that level.

 

[blom0344]

I think that the problem lies in the way the data is fetched.
From what you wrote it looks like the price data is already summarized and the cost is at transaction level.

There is one typical occurence when this happens (and it always takes an effort to explain)
In case amount come from both header and lines (OLTP schema) then amounts from header are multiplied by the numbers of lines fetched.

Could this be the case ?


Ties Blom