Grouping Based on SubTotals

[LostInHouston]

Hello, I'm new here so hopefully I won't commit any violations of the local netiqutte.

I'm working with CR XI and am trying to place customers into category buckets.

Specifically a customer will have many charges from many points in the company that I roll up into 1 total charge. I then want to take the value in the total charge and assign them to a category (e.g. <10,000,10000-49999,50000+) I then want to create a group based on that category.

I can subtotal the charges and I can get the charges into the proper category but when I try to create a group on that category it won't let me.

If I haven't given enough information please let me know. Thanks for your help.

 

[lbass]

There is a way to create faux groups by clustering the summaries together using a topN sort and then creating fake group headers/footers.

Or you could use "Add command" as your datasource where you return the sum of the charges, e.g.,:

Select
sum(table.`charge`) as totalchg, table.`customerID`
From
`table`table
Group by
table.`customerID`

Then you can use {command.totalchg} in a formula like:

if {command.totalchg} < 10000 then "<10000" else
if {command.totalchg} in 10000 to 49999 then "10000 - 49999" else //etc.

You can then group on this formula.

-LB