Grouping by a ranged of sums

[DJWheezyWeez]

I'm using CR XI.

I have a TxnQty field summed up by a JobCode field. I created a formula (shown below) to tell me if the total is between 0-5000, 5001-10000, and 10001-15000. I'm trying to group by this formula but I'm not able to. Is there something I'm doing in the formula that's not letting me group by it or can I not group by formulas in general? Is there a way around this?

Any help is greatly appreciated.

if Sum ({table.TxnQty}, {table.JobCode}) <= 5000.00
then "0-5000"
else if Sum ({table.TxnQty}, {table.JobCode}) > 5000.00 and Sum ({table.TxnQty}, {table.JobCode}) <= 10000.00
then "5001-10000"
else if Sum ({table.TxnQty}, {table.JobCode}) > 10000.00 and Sum ({table.TxnQty}, {table.JobCode}) <= 15000.00
then "10001-15000"

-DJWW

 

[lbass]

You can't group by formulas that contain summaries. Try creating a SQL expression {%sumqty}:

(
select sum(`TxnQty`)
from table A
where A.`JobCode` = table.`JobCode`
)

You would also have to build in selection formula elements into the expression.

Then you could use this expression in your formula instead of the summary and it would be available for grouping.

Another approach might be to use running totals that evaluate based on the value of the summary (one for each "group") and then reference them in the report footer, using text boxes to label them. Depends upon what your goal is in grouping.

-LB