crosstab groupings

[princessinthetower]

I want to have a crosstab with names in rows and $ amount groups in columns but I want the columns to be ranges of $ amounts

$1000-$2499 | $2500 - $4999 | $5000 - $9999
etc.

I know it's possible to write a formula to assign a $ range to a name based on a summary, but the cross tab won't allow this kind of calculating formula to be a column. A top N report will sort by amount but I don't see any way to group the amounts so that every different interim $ amount is not displayed. I'm interested in just the ranges. Is there any clever way to accomplish this?

 

[KelleyO]

Infobabe,

I made a formula for the column and named it "sortbalances". This is the formula:

if {armast10.balance} < 0 then &quot;negative&quot; else
if {armast10.balance} < 1000 then &quot;1-1000&quot; else
if {armast10.balance} < 2500 then &quot;1001-2500&quot; else
if {armast10.balance} < 5000 then &quot;2501-5000&quot; else
if {armast10.balance} < 10000 then &quot;5001-10000&quot; else &quot;10001+&quot;

And then summed that formula for the summarized field.

I originally had the customer number in the row, but that was too big (one line per customer) so I just took it out. Now I have a nice one-row crosstab with columns for the ranges as above. If you wanted to actually SEE which group each name fell into, put them in for the rows.

Because I wanted the columns to appear in a certain order, I used the &quot;specified order&quot; in the group options for the column, and then just listed them in the order I wanted.

Hope this helps.

Kelley O

ps: LOVE your name....

 

[princessinthetower]

Sorry, I didn't fully explain this. The names generally have more than one $ amount within a given date range so it is not a range of a single amount. It is a range of a sum for each person and the crosstab doesn't handle this. Something like this:

$1000-$2499 | $2500 - $4999 | $5000 - $9999

Carol $500 | |
Carol $500 |
Joe | $2500 |
Fred | | $4000
Fred | | $5000

 

[HowardHammerman]

Dear Infobabe:
I don't think you can do this using the crosstab expert. You will have to create a 'manual crosstab'. Group by name. Create X number of formulas where X is the number of columns. Each formula will be of the form:

If {table.amountfield} in 1000 to 2499 then 1 else 0

put this in the details section and sum to the group footer. suppress the group header and details.

See the FAX on manual cross tabs in this forum.

-- Infoguy Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[kenhamady]

infobabe,

I think what everyone is missing is that you are trying to categorize based on a person's SUBTOTAL, not based on a single field value. You certainly can't use a crosstab, and you can't really use a manual cross-tab because once you categorize based on the subtotal you can't then total the values in each column.

You can make something like a manual cross-tab using a separate formula for each column. Each would be something like this:

If Sum ({table.amountfield}, {person}) in 1000 to 2499
then Sum ({table.amountfield}, {person})
else 0.

This would put each subtotal into the appropriate column. The problem would be totalling the columns. You would need to use running totals with variables to total these formulas. If this is heading in the correct direction, use the 3-Formula technique described in faq149-182. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com