Grouping a Sum

[slickp007]

Crystal XI

Hi

I'm trying to group my data into premium bands, ie 0-1000, 100-2000, etc, etc.

In my details i have all the transactions for each client, this grouped by client and the transactions are summed in this group. From this sum i've created a formula to put them into bands with the following:

//{@Prem Band}
if Sum ({ANALYSIS.GrossPremium}, {CLIENT.ClientNo}) < 5000 then "0 - 5k" else
if Sum ({ANALYSIS.GrossPremium}, {CLIENT.ClientNo}) in 5000 to 9999 then "5k - 10k" else
if Sum ({ANALYSIS.GrossPremium}, {CLIENT.ClientNo}) in 10000 to 14999 then "10k - 15k" else
if Sum ({ANALYSIS.GrossPremium}, {CLIENT.ClientNo}) in 15000 to 24999 then "15k - 25k" else
if Sum ({ANALYSIS.GrossPremium}, {CLIENT.ClientNo}) in 25000 to 49999 then "25k - 50k" else
if Sum ({ANALYSIS.GrossPremium}, {CLIENT.ClientNo}) > 50000 then "50k+"

This is fine, however, i would now like to group by this formula, but it doesn't give me the option in the insert group menu. Any help or ideas? Hope this makes sense

Cheers

 

[lbass]

Insert a SQL expression {%sumprem} like:

(
select sum(`GrossPremium`)
from ANALYSIS A, CLIENT B
where A.`ClientNo` = B.`ClientNo` and
B.`ClientNo` = CLIENT.`ClientNo`
)

This assumes that the tables are linked on ClientNo. The punctuation will be specific to your datasource/connectivity.

Then in the main report you can substitute the SQL expression in your clustering formula and you will be able to group on it.

-LB

 

[slickp007]

Sorry me just being dumb, when you say substitute the SQL expression in your clustering formula and then i'll be able to group on it, what exactly do you mean?

 

[slickp007]

Also when i create the sql expression like from above, i've inserted this:

(
select sum("GrossPremium")
from ANALYSIS A, CLIENT B
where A."ClientNo" = B."ClientNo" and
B."ClientNo" = CLIENT."ClientNo"
)

i get the compile error "A column by the name 'CLIENT.ClientNo' was not found.

Any help?

 

[lbass]

I simply used the field you showed in your original formula--wasn't that a real field name?

-LB

 

[slickp007]

yeah it is a real field name, but it still comes up with an error. Do you think i should replace the table name CLIENT with the letter B?

 

[slickp007]

the analysis and client table are linked by ClientNo and CompanyNo, will this make a difference to the SQL expression?

 

[lbass]

Yes to your second question. Change it to:

(
select sum("GrossPremium")
from ANALYSIS A, CLIENT B
where A."ClientNo" = B."ClientNo" and
A."CompanyNo" = B."CompanyNo" and
B."ClientNo" = CLIENT."ClientNo"
)

Note that both tables must be used in the main report for the SQL expression to work, and at least one field from each should be present on the report.

-LB