RESOLVED - Crystal Reports Question

[Mr_Floyd1976]

I have a table in Access that I need to generate a report from.

Very simply, I need to count the number of records in the table, no worries there, but then in another column in the report, I need to count the number of records where a field is equal to a given parameter.

e.g. Table has column 'booked'.. It is a boolean field, and I need to count the number of records that have a 'Yes' in that field.

Many thanks

Mark

[pnish]

What are you using to generate the report? Is it an Access report, a Crystal report or something else?

To do what you want is fairly straightforward in Crystal. In Access however, what you may need to do is create a query from your table which counts the records based on your criteria and then report off the query rather than the table.

[Mr_Floyd1976]

Sorry, wasnt very clear was I.

I will be creating the report using Crystal Reports. The datasource will be the Access table.

Many thanks


Mark

[GaryMazzone]

Try creating a variable in the report then use a CR users defined function to count the required field meeting the specified condition.

[Mr_Floyd1976]

Try creating a variable in the report then use a CR users defined function to count the required field meeting the specified condition.

Thank you for the advice, but having not used Crystal before I have no idea how to do this. I have had a play around with it and it is simple to count the number of items within a field, by using the Count function, but I cannot work out how to Count where field = "Yes".. There are a couple of Count functions which have parameters, but I cant get these to work

[GaryMazzone]

My suggestion would be using a varibale that you create in the report. Then the function you would write would be an if statement that looks at the value of the field you you want to count. If the value in the field meets you condition the increment the variable by 1. Once all records are read you can display the variable on the report as anothing field.

[Mr_Floyd1976]

My suggestion would be using a varibale that you create in the report. Then the function you would write would be an if statement that looks at the value of the field you you want to count. If the value in the field meets you condition the increment the variable by 1. Once all records are read you can display the variable on the report as anothing field.

Makes sense, but I have no idea how to do it having not used Crystal before

[pnish]

OK. These are the basic steps involved. I've made some assumptions about the database, table and field names etc, so you'll need to adapt this to suit your purpose.

1. Open your report file in Crystal
2. Display the Field Explorer... View|Field Explorer
3. Place the fields you want to display in the report's Details section
4. In the Field Explorer, right click on Formula Fields and select New...
5. Give the formula a sensible name, eg CountYes, and click Use Editor
6. In the bottom pane of the Formula Editor type the following (substituting your table name):

Shared numberVar CountOfYes;

If {Bookings.Booked} = True Then
    CountOfYes := CountOfYes + 1

7. Click Close and click Yes to save your changes
8. From the Field Explorer drag your new formula into the Details section
9. Now create another formula as in step 4, and call it SumOfYes
10. In the Formula Editor type the following code:

Shared numberVar CountOfYes;

CountOfYes

11. Close the editor and save your changes
12. This time, place your new formula in the Report Footer
13. Run your report

This should display the total number of times that the 'Booked' field was Yes at the bottom of your report.

You'll probably want to stop the CountYes field from displaying for each record, so just right-click the field, select Format Field..., click on the Common tab and check the box labelled Suppress.

[Mr_Floyd1976]

OK. These are the basic steps involved. I've made some assumptions about the database, table and field names etc, so you'll need to adapt this to suit your purpose.

1. Open your report file in Crystal
2. Display the Field Explorer... View|Field Explorer
3. Place the fields you want to display in the report's Details section
4. In the Field Explorer, right click on Formula Fields and select New...
5. Give the formula a sensible name, eg CountYes, and click Use Editor
6. In the bottom pane of the Formula Editor type the following (substituting your table name):

Shared numberVar CountOfYes;

If {Bookings.Booked} = True Then
    CountOfYes := CountOfYes + 1

7. Click Close and click Yes to save your changes
8. From the Field Explorer drag your new formula into the Details section
9. Now create another formula as in step 4, and call it SumOfYes
10. In the Formula Editor type the following code:

Shared numberVar CountOfYes;

CountOfYes

11. Close the editor and save your changes
12. This time, place your new formula in the Report Footer
13. Run your report

This should display the total number of times that the 'Booked' field was Yes at the bottom of your report.

You'll probably want to stop the CountYes field from displaying for each record, so just right-click the field, select Format Field..., click on the Common tab and check the box labelled Suppress.

Excellent.. Thank you very much