Summary question 1

[Jazztpt]

Hi - I have built a report that groups members and totals the number of attendances for each member. What I normally do then is export to Excel and use a Frequency Array formula and 'Bins' to group the attendances i.e. 10 members came 1-5 times , 20 members came 11-20 times etc.

I would like to do this extra calculation in Crystal, tried several things but I am not sure how to achieve it, the report has to count attendances for each member and then do another calculation to group the results into 'Bins'. Must be an easier way to achieve this. Could anybody point me in the right direction on this one.
Thanks
Jazztpt

 

[lbass]

The solution depends upon your CR version, which you should always identify.

-LB

 

[Jazztpt]

Sorry Crystal 8.5.
Thanks
Jazztpt

 

[lbass]

You can use a SQL expression for this. What tables/fields are you working with? What is your record selection formula?

-LB

 

[Jazztpt]

I created a group on memberNo

Group #1 Name: tblAttendance.MemberNo(number)

and in the group 1 header I added the formula

Count of tblAttendance.Transdate(number)

I Suppressed the detail to give me the grouped MemberNos with the count of the number of transaction dates.

Cheers
Jazztpt

 

[lbass]

Create a SQL expression {%attend} like this:

(
select count(A.`Transdate`)
from tblAttendance A
where A.`MemberNo` = tblAttendance.`MemberNo`
)

Note that the punctuation is specific to your datasource/connectivity. Go to show SQL query to see an example of how you should be punctuating the fields.

Then create a formula {@cnt}:

if {%attend} in 1 to 5 then "1 to 5" else
if {%attend} in 6 to 10 then "6 to 10" else //etc.

Remove your current grouping, and instead insert a group on this formula. Add the memberno field to the detail section and insert a count on it at the group level.

-LB

 

[Jazztpt]

Many thanks for your help with this lbass, I will try this out as soon as I can and let you know how I get on.

Really appreciate your assistance, this is something I can use for lots of reports.

Jazztpt

 

[Jazztpt]

Took a bit to find the time to try this at work, had a go earlier. Not working as yet. I created the SQL expression:

%Attend
(select count("Transdate") from vwTransactDetail where "MemberNo" = vwTransactDetail."MemberNo")

And the group
@Count

if {%Attend} in 1 to 20 then "1 to 20" else
if {%Attend} in 21 to 50 then "21 to 50" else
if {%Attend} in 51 to 100 then "51 to 100" else
if {%Attend} in 101 to 200 then "101 to 200" else if {%Attend} >=201 then "other"

Set up the report as suggested, but everyting is grouped under 'other', the count part of the sql is not working, so my syntax is likely wrong.

Any suggestions what I have done wrong.

Thanks
Jazztpt

 

[lbass]

Try:

(select count(A."Transdate") from vwTransactDetail A
where A."MemberNo" = vwTransactDetail."MemberNo")

Since the SQL expression accesses the database directly, if you have record selection criteria, you may need to build this into the SQL expression directly.

Place the resulting expression on your report in the group for member no group to see if you are getting the correct results.

-LB

 

[Jazztpt]

Thanks for your quick response, will give this a go asap.
Cheers
Jazztpt

 

[Jazztpt]

That works a treat, I had to start with a blank report but the concept works perfectly. Just need to buoild my report up bit by bit and take it from there.
Really appreciate your help.
One thing I don't understand is the use of the A.? what exactly does this mean/do.

Jazztpt

 

[lbass]

You could replace A with any letter or word, as it represents an alias table. By using this, you can create a "group" within the SQL expression itself by linking an A field to a corresponding table field.

-LB