Sorting by Calculated Field from Totals (That is also calculated)

[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.

I'm running this on crystal 8.5

DT

 

[lbass]

First, you don't need to use a formula to get the price--assuming you have a group on item, you can insert a maximum on the price field.

If you are able to use SQL expressions, this is the way to go. Create two SQL expressions:

[{%price}:]
(select max(A.`price`) from table A where
A.`item` = table.`item`)

[{%cost}:]
(select sum(A.`cost`) from table A where
A.`item` = table.`item`)

In each expression, substitute your exact field names for cost, price, and item, and replace "table" with your table name. Leave "A" as is, since it represents an alias table name. If you are limiting your data by date or other criteria, you would need to build these into the SQL expressions also.

Then create a formula in the formula expert {@GP%}:
({%price}-{%cost}) % {%price}

Place this in the detail section and right click on it and insert a maximum. (The value will be the same as in the details, but you need to do this so that TopN sort becomes available.) Then go to report->topN/group sort and choose "maximum of {@GP%}" as your group sort field.

-LB

 

[Barock21]

Thank you for your reply. I really hate to make this so complicated, but the price I referred to was calculated field not from the database. The $1000 scenario came from this:

1st Formula - Extended Price:
({wbw_line.UT_PRICE} / {wbw_line.SALES_SIZE}) * {wbw_line.MULTIPLIER} * {wbw_line.INV_QTY}

2nd Formula - Current Year Sales:
if {wbw_line.INV_DATE} in {?Start Invoice Date} to {?End Invoice Date} then {@Extended Price} else 0

So I am not sure how to apply your suggestion in this situation.

 

[lbass]

You could still do this, I think, except that SQL expressions do not accept parameters--you would have to hard code them in the expressions.

I don't know of any other way in 8.5 to do a sort based on a row-type percentage. Basically, for a group sort, you have to be able to insert a summary on the formula you want to do a group sort on--if you have another way of returning the summaries contributing to the formula, that is what you should do.

-LB