Custom Report Example - Attendance Count

Objective:

This report will provide accurate attendance counts for those churches that have multiple services on Sunday morning. Attendance counts in other reports can be skewed if a person attends multiple services.

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

Result:

Steps:

1. To begin, start by navigating to the Attendance module, select the Other Output option and then finally Custom Reports. When asked whether to run reports for “Detail” or “Summary ” select Detail.
   
   1. If you are using a version prior to 11, there will be no other output option; the Custom Reports option will be directly under the Attendance module.
      
      
2. The next step will be creating a new query and a title to go with it. This step will be different depending on whether you have created a custom report in the past or not.
   1. If you have NOT created a custom report in the past, the system will immediately prompt you for a report title. Simply enter the desired title and click OK; this will bring up the query builder for the next step.
   2. If you HAVE created a custom report in the past, you will be brought to an existing page of all your custom reports. Simply click the Add button and enter the desired title and click OK; this will bring up the query builder for the next step.
      
      
3. Now we will need to create the filter for the query. This will be a 2-conditioned query.
   1. For the first condition:
      • From the "Step 1: Pick a field" tab, select Date.
      • From the "Step 2: Pick an Operator" tab, select Equal to Date.
      • From the “Step 3: Select Ask later down towards the bottom.
   2. For the 2nd condition:
      • Start by clicking the add button and selecting the “AND” option.
      • From the "Step 1: Pick a field" tab, select Activity Type.
      • From the "Step 2: Pick an Operator" tab, select Exactly Matches.
      • From the “Step 3: Select Ask later down towards the bottom.
      • Once satisfied with your query, select done at the bottom right.
   
4. At this point, you will be prompted: Do you want to run this query now? Click Yes. Here we will need to define the output fields. Do so by simply double clicking on the desired fields; this will move them to the included section. Select Last Name, First Name, Family Phone 1 and Attendance Status from the “fields to pick from” section.
   
   
   
5. Lastly, before we run our query, define the sort order. Click on the Edit Sort Order button and choose “Last Name, First Name” from the list of fields. Click done.
   
   
6. Now click on the Run Query button. Then click Edit form to begin customizing our output.
   
   
7. Start by selecting the “status” field and pressing the delete key on your keyboard. We will create our own custom status later on.
   
   
   
8. Next, 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.
   
   
   
9. Double click on the new Group Footer (last_first+phone1) and set the band height to 0.187.
   
   
   
10. Highlight the last_first and phone1 fields and drag them above the Group Footer band that you just opened up. You can then change the Detail band height to 0.000 by double clicking on it and modifying the height.
    
    
    
    
11. Now we need to create two variables.
    1. First variable : m.atstatus
       • 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.
         
    2. 2nd variable: m.presentcount
       • Click the Add button again and call the new variable "m.presentcount".
       • In the Value To Store field, enter "iif(m.atstatus="P",m.presentcount+1,m.presentcount)"
       • Leave the other fields as the default values. 
         
12. 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.
    
    
    
13. 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.
    
    
    
14. 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, then click OK. Widen the new field by clicking and dragging the right edge of the field.
    
    
    
15. 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. 
    
    
    
16. Finally, select File, Save and Close. Start report to get your results.
    • It will show 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.