Grouping By Sum 1

[databuilt]

I want to group report results by a category code related to the sum of a field. Specifically, the query returns sales at a detail level. GF2 grouping is by Customer (with sum[sales]) I would like to have a GF1 grouping by volume category (0-100, 100-500, etc.). I have a formula which associates each customer with the range group:
IF Sum ({t1.sales}, {t1.customer})<100 then 1 else
IF Sum ({t1.sales}, {t1.customer})>=100 and Sum ({t1.sales}, {t1.customer})<=500 then 2 else
IF Sum ({t1.sales}, {t1.customer}) >500 and Sum ({t1.sales}, {t1.customer}) <=1000 then 3 else
etc....
But I can't use this to set up a grouping.

Is there anyway to do this?

Doug

 

[dgillz]

Your formula should work just fine.

Whan you say &quot;I can't use this to set up a grouping&quot;, what do you mean by that statement?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[databuilt]

When I use the formula, CR9 wont let me group by the formula. It's not available on the list of fields I can group by. It seems as though the field must be valid at the record level.

To get around this, I created a subquery in the SELECT statement which returns the total period sales for the customer (for every record returned). I think I can use formula on this value and then group by that. This seem like a long way around what should be a simple solution and consumes a lot of processing time.

Doug

 

[kenhamady]

You can't group on a formula that relies on a subtotal.
Grouping happens before subtotals, so by the time the subtotals are in your formulas the grouping is long passed.

You might be able to fake this by using TopN to rank all groups in order based on their subtotals. Then use a conditionally suppressed GF that appears at the appropriate places to be your pretend group footers. You might even be able to use running totals that reset based on a formula to give pretend subtotals. There are quite a few limitations to this technique.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[databuilt]

The subquery appproach mentioned above seems to work OK without undue delay in processing time (~10 min for a year's worth of data). Using runing totals for the for the group footers and a sum for the report footer works well.

Thanks for your help.