Grouping by a formula

[mgerard802]

I have the following formula:
if (sum ({@BV-Current}, {tblClients.ClientName})= 0) then "New"
else if sum ({@BV-Prior}, {tblClients.ClientName})= 0 then "Lost"
else if sum ({@BV-Current}, {tblClients.ClientName}) > sum ({@BV-Prior}, {tblClients.ClientName})
then "Increase"
else if sum({@BV-Current}, {tblClients.ClientName}) < sum ({@BV-Prior}, {tblClients.ClientName})
then &quot;Decrease&quot;
else if sum ({@BV-Current}, {tblClients.ClientName}) = sum ({@BV-Prior}, {tblClients.ClientName})

then &quot;Unchanged&quot;
I want to group by the results (New, Lost, etc..) but when I go to grouping this formula is not available, all other formulas are available. Anybody know why it is not available? I am on version 9, Access DB.

 

[Reebo99]

's not available because you are using :
SUM ({@BV-Current}, {tblClients.ClientName})

The item in italics is a summary function,
The item in bold denotes summary by group.

Therefore you cannot create a group based on the summary of a group. For this to work you would require the created to be totally outside of the {tblClients.ClientName} group.

In this case, I would advise you to explain exactly what you are trying to achieve, with sample data.


Reebo
UK

 

[mgerard802]

I have a table with client transactions which I am summing the total for a range of dates for the current year and prior year.
I then want to compare the totals for each year. I then want to group all the clients where the total amount has increased. Then group the clients where the total has decreased and so on.

Client Date amount
1 1/1/02 10
1 5/1/03 20
2 3/1/02 50
2 4/1/02 10
2 6/1/03 30
3 1/1/02 40
3 1/1/03 70

Results-Detail
Client Current(03) Prior(02)
1 20 10
2 30 60
3 70 40

Results-Summary
Summary Current Prior
Increase 90 50
Decrease 30 60

 

[kenhamady]

It sounds like you want to categorize the groups based on one of these conditions. As Reebo99 pointed out, you can't group on a value that is calculated after the groups occur.

One option is to create a separate report for each of your categories, and make them subreports of a master report. Each would have a different group selection formula. The first one - for the NEW subreport - would be:

sum ({@BV-Current}, {tblClients.ClientName})= 0

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

http://www.kenhamady.com/

- tek@kenhamady.com