Need to sort on a variable or command field used in a formula 1

[pl1101]

I have read through the threads and see that I can not sort on a variable that I pass to the main report from the subreport, but can I sort if I use the command select feature?

Let me explain what I am trying to do:
I am having a hard time creating a report the uses a summed field from 1 table and detail that I have to sum from the other. I need to take these 2 numbers and create a formula and then sort on it. Does anyone have any suggestion on how I can accomplish this?

More detail:
Main report: quantity on hand (value is already summed)
subreport: open sales quantity (individual numbers that need to be summed)--Tried using a variable or command to get the sum of this value.
Main report: quantity on hand/open sales---Fomula. This is the value that I need to sort by.

 

[lbass]

Your best bet is to create a SQL expression that does the sum of the qty on hand. Let's say you want the formula per item #. Create a SQL expression {%sumqty} like this:

(
select sum(`qty`)
from opensales A
where A.`itemno` = opensales.`itemno`
)

You might have to build in selection criteria used in the main report. Then you can reference this in a formula:

{mainrpt.ohqty}/{%sumqty}

The opensales table must be added to the main report so that the exprssion can compile. Then insert a group on item# (if that's the level of summary in the main report onhand qty field), place the new formula in the detail section and insert a maximum on it at the group level. Then use report->group sort to sort the report.

-LB

 

[pl1101]

Great it worked! wow you are the best!!! Thank you so much for your help. This forum has saved me so much grief and aggrevation.

Thank you again for all of your help...

 

[pl1101]

When I added another sql expression to the formula, it has a null value for one of the items and it does not add the other values. How do I get it to ignore the null. I tried putting "not isnull" in the sql expression, but it gives me an error.

Example:
formula: sql_exp1 + sql_exp2 +sql_exp3
sqlexp2 contains a null value
Result is blank

I need it to still add sql_exp1 + sql_exp3

Thank you!

 

[lbass]

Change your formula to:

(
if isnull({%sql_exp1}) then
0 else
{%sql_exp1}
) +
(
if isnull({%sql_exp2}) then
0 else
{%sql_exp2}
) +
(
if isnull({%sql_exp3}) then
0 else
{%sql_exp3}
)

-LB

 

[pl1101]

If I need to use these sql expressions in a calculation, how do check for null and create a final equation? and allow for division by zero?

for example: If I need to use ({%sql_exp1/({%sql_exp2 +({%sql_exp3)

 

[lbass]

if
(
(
if isnull({%sql_exp2}) then
0 else
{%sql_exp2}
) +
(
if isnull({%sql_exp3}) then
0 else
{%sql_exp3}
)
) > 0 then
(
if isnull({%sql_exp1}) then
0 else
{%sql_exp1}
)/
(
(
if isnull({%sql_exp2}) then
0 else
{%sql_exp2}
) +
(
if isnull({%sql_exp3}) then
0 else
{%sql_exp3}
)
)

-LB