Custom Report Example - Attendance Count Report

This report will provide accurate attendance counts for those churches that have multiple services Sunday morning. Attendance counts in other reports can be skewed if a person attends multiple services. This report is grouped by Activity Type, a customizable code field that was added in PowerChurch Plus Version 9.

If you have two activities set up, 9AM Worship Service and 11AM Worship Service, make sure that they are both assigned the same Activity Type, for example: Worship Services. You can do this on the Maintain Activity/Skill Descriptions screen.

To create the Attendance Count report:

1. Add the query.

Go to Membership > Attendance > Other Output > Custom Reports. Older versions of PowerChurch Plus do not have an Other Output menu. Click the Add button and give the new report a name. We used Attendance Count Report.

2. Create the filter for the query.

In the Filter Building window, choose "Date" in step one, "Equal to date" in step two, and click the "Ask Later" button in step three. Add a second query condition, choosing the AND connector. Choose "Activity Type" in step one, "Exactly Matches" in step two, and click the "Ask Later" button again in step three. Click Done. This will create prompts asking for the date and activity type each time you run the report.

3. Select the output fields.

In this example, we chose "Last Name, First Name" (about mid-way down the list, you will see Profile names. Last Name, First Name is just after the Profile name entries), "Family Phone 1", and "Attendance Status". Click Done.

4. Set the sort order.

Click the Edit Sort Order button and choose "Last Name, First Name". Click Done.

5. Run the query.

Uncheck the Browse Query Results option, check the Hide Duplicate Records option, then click the Run Query button. When prompted, enter the date that you would like to report on and an activity type like "Worship Services," for example. Again, these prompts will come up each time you run the report.

6. Edit the report form.

Click the Edit Form button to open the Report Designer window.

• Delete the "status" field, we will be adding a variable later to handle this.
• Right click on the Detail band and choose Data Grouping. Click the Add button and enter "last_first+phone1". This will group all results by the individual person. Click OK.
• Double click on the new Group Footer (last_first+phone1) and set the band height to 0.187.
• Highlight the last_first and phone1 fields and drag them into the Group Footer band that you just opened up. You can then change the Detail band height to 0.000.
• Go to Report > Variables. Click the Add button and call the new variable "m.atstatus". In the Value To Store field, enter iif(status="P","P",m.atstatus) In the Initial Value field, enter "" (empty quotes). Choose Reset Value Based On: Group: Last_First+Phone1.
• Click the Add button again. Call the new variable "m.presentcount". In the Value to Store field, enter iif(m.atstatus="P",m.presentcount+1,m.presentcount) Leave everything else set to the defaults and click OK.
• Back in the Report Designer, click the Field tool (the "ab|" button) then click into the Group Footer next to the phone1 field. In the Expression field that opens, enter m.atstatus and click OK.
• Click the right mouse button on the Group Footer band and choose "Optional Bands". Under "Summary", check the Report Has Summary Band option, then click OK.
• Click the Field tool (the "ab|" button) then click into the new Summary band that opened at the bottom of the Report Designer directly below the atstatus field. Enter m.presentcount in the Expression field that opens up, then click OK. Widen the new field by clicking and dragging the right edge of the field.
• Click the Label tool (the "A" button) then click into the new Summary band, to the left of the presentcount field. Type Total present: then click away from it.

7. Preview the report.

Go to File > Print Preview to take a look at your report.

• It shows the list of everyone who has attendance status for the selected list of activities on the selected date.
• A "P" will print next to the name and phone number of those who were present in at least one of the services.
• At the very end of the report, you will have an accurate count of those who were present at one or both services.

You can close the preview and make any necessary changes to customize the look of the report to your liking.