Cannot group a formula field

[rmttbone]

I am using crystal 10, and I am having trouble grouping the results of a formula. I need to count the number of occasions that an event takes place, and I need to group it in the following groups:

1,2,3,or 4

I used this select case statement to create a value:
numbervar tests;

select count({UAOrders.OrderID},{UAOrders.Client_rsn})
case 1 to 1 : tests:=1
case 2 to 2: tests:=2
case 2 to 3: tests:=3
case 4 to 1000000: tests:=4

default: tests:=0

OrderID is the unique ID and is created when a new sample is obtained. Client_rsn is the unique ID for the client that gives the new sample.

I named the formula Frequency, and it will display in the detail section of my report when I drag the formula from the Field Explorer. But, the Frequency formula is not visible/accessible in the group expert or sort expert.


Thanks in Advance

 

[lbass]

You could do a group sort on the count, but you won't be able to group on the count. Also note that your cases statement doesn't make sense--the cases are not distinct from one to another. It should look something like:

select count({UAOrders.OrderID},{UAOrders.Client_rsn})
case 1 : tests:=1
case 2 to 3: tests:=2
case 4 to 5: tests:=3
case 6 to 1000000: tests:=4

There is a way you could create fake groups using a group sort, but you could also create an actual group on a formula like yours if you used a command to return the count, as in:

select count(`UAOrders`.`OrderID`) as cnt, `UAOrders`.`Client_rsn`
from `UAOrders`
group by `UAOrders`.`Client_rsn`

Then you could link the command to your UAOrders table on the client field. You could then change your formula to:

select {command.cnt}
case 1 : 1
case 2 to 3: 2
case 4 to 5: 3
case 6 to 1000000: 4

...using the ranges you want, and then you will be able to insert a group on the formula. I removed the variable--don't see the need for it. You can call the formula {@tests}.

-LB

 

[rmttbone]

LB, I tried it, and I got an "unknown query engine error

 

[synapsevampire]

Since LB didn't ask nor know the type of database used, it was speculation, and you would need to adjust the syntax to suit your database, more importantly. post your database and connectivity in every post ratehr than making people waste time guessing at your environment.

-k

 

[rmttbone]

SQL Server 2000 and OLE DB (ADO)