Custom Report Example - Church Directory (V10+)

Objective:

The purpose of this report is to teach you how to build your own custom church directory. Building a custom church directory will allow you to include fields that are otherwise not included in the standard church directory. In this example, we will include the birthdate, but other fields and customization could be added.

Result:

Steps:

1. To begin, start by navigating to the Personal Profiles module, select the Other Output option and then finally Custom Reports.
   
   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 Personal Profiles 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.
   • From the "Step 1: Pick a field" tab, select Household Key Last Name.
   • From the "Step 2: Pick an Operator" tab, select Is not empty.
   • Click done when satisfied with the query.
   
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 Household Key Last Name, Household Key First Name, Mailing Name, Address Line 1, City + State + Zip/Postal, Family Phone 1, Profile First Name, and Birth Date from the “fields to pick from” section.
   
5. Now on the Query Overview Window, select Pick Output Type. Select Report in Form Layout then click done.
   
6. Now we need to define the sort order. Select Edit Sort Order on the Query Overview Window. Since we want to sort by family, select the following fields in this order: Household Key Last Name, Household Key First Name, Address Line 1, and Directory Sequence. Once these fields have been selected, click Done.
   
7. Click on the Run Query button. Then click Edit form to begin customizing our directory.
8. The first thing we need to do is group each family member to his/her family. To do this, select Data Grouping from the Report menu. Click Add. In the Expression Builder dialog box, enter the following expression: lastname + firstname_a + address. Click OK, then OK again.
   
9. On the Report Layout Screen, you will notice five bars/sections: Page Header, Group Header, Detail, Group Footer, and Page Footer. To achieve the desired output on this report, we will need to resize these sections. Start by resizing the Group Header bar. To do so double click on the bar and enter in .600 into the height field. If done correctly, you should notice that the white area above the Group Header bar is now larger.
   
10. Next, we will move some of the output fields into the newly resized Group Header section. Click and hold down the mouse while on top of the rectangle that contains the field nameline. Drag the nameline field up to the Group Header band (just above the Group Header bar) and position it similar to the figure below. Move the address and city_zip boxes into the Group Header band in the same way.
    
11. In this example, we will not need the labels Last Name:, First Name:, Mailing Name:, Address:, City + State + Zip/Postal:, Phone 1:, First Name: and Birth:. To delete them, select each of them and then press the Delete key.
12. Additionally, we are now safe to delete the lastname, firstname_a and qm_space fields.
13. Only the Firstname_b and born fields should remain. First select the firstname_b field and align it with the nameline field from above. Then select the born field and align that with the phone1 field above. If done correctly, it should look similar to the figure below.
    
14. We will now work on the Page Header for this report. Start by resizing the Page Header section like we previously did. Double click on the page header bar and enter 1.250 for the height.
    
15. Next, add a title to the church directory. Select the Text Tool in the toolbar. The text tool looks like a capital A in the toolbar. Click into the newly opened Page Header section and start typing the name of your church.
    
16. Let's customize that title now. Select the pointer tool from the toolbar and then select the church name textbox. Then click on Format under the navigation menu and Font. Here you can customize the font to your liking.
    
17. Repeat steps 15 and 16 to create headers for the "Directory of Families," "Name," "Birth Date," and "Telephone". Once done, align the newly created headers in line with the name, born and phone fields like in the figure below.
    
18. Now we need to adjust the size of the remaining sections/bars. Start with the Detail bar, double click on it and set the height to be .250 inches. Next, select the Group Footer bar and set the height to .175 inches. Finally select the Page Footer and set the height to be .300 inches. Once done, the result should look like the figure below.
    
19. Finally, you will need to create lines that accent your report. To draw lines, select the Line Tool from the tool bar (the Line Tool looks like a large, off-center plus sign).
    1. Next, move your pointer into the Page Header band, just above the Name text box. Click down and drag the pointer toward the right margin. Release the mouse button. You can adjust the length of any line by dragging the small squares located on either side of the line.
    2. Optionally you may want to create a line in the group footer section to split up families. Repeat the 19a step to accomplish this.
    3. Additionally, you could adjust the lines weight by right clicking the line and selecting properties. From there, select the Style tab and change the weight from 1 to 2.
       
20. Optionally, if you have a scanned graphic of your church logo available, you can add it to this report by selecting the Picture Tool from the tool bar and then clicking anywhere on the Page Header band. A Report Picture dialog box will then appear. First locate your picture using the control source ellipsis or by typing in the name. Then select the "Scale Picture - Retain Shape" bullet. Click OK. The picture will appear wherever you initially clicked. You may need to resize or move the picture.
21. Once you have completed these steps, select Save from the File menu. To close the Report Designer, select Close from the File menu.
22. To print your report, click the Start Report button from the Setup For Form Layout Report window. Remember, some additional adjustments may be required depending on your printer and report layout. Please refer to the Custom Reports section of your PowerChurch Plus manual for additional information.