Custom Report Example - Pictorial Directory

Objective:

The purpose of this report is to teach you how to build your own custom pictorial directory. Building a custom pictorial directory will allow you to format the directory however you want. Additionally, you could opt to include alternate fields that are not in the system's pictorial directory.

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 Personal Status Code.
   • From the "Step 2: Pick an Operator" tab, select Exactly Matches.
   • From the “Step 3: Enter a Value” tab, select Member as the code.
   • 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, Family Picture, Profile First Name, and Directory Sequence from the “fields to pick from” section.
   
   
   
5. From the Query Overview Window select Edit Sort Order. 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.
   
   
6. Now click Run Query. Next, click on Edit form to start customizing your output.
   
   
7. 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.
   
   
   
8. Let's clean up the report builder interface. Start by selecting the following fields and labels and deleting them. Select the lastname and firstname_a fields and press the delete key on your keyboard. Select the following labels, Last Name, First Name, Mailing Name, Address from the header and Date() in the footer and delete.
   
   
   
9. Next, let’s adjust the layout of our report. We need to resize the bars/sections. To do so we are going to right click on the various bands labeled Page Header, Group Header, Group Footer and Page Footer and select the properties option. Once in the properties, you will find a height field. Set the heights to the following values. These may need to be adjusted in the end for your desired look. Refer to the figure below to see what this should look like once done.
   • Page Header: 1.250 inches.
   • Group Header 1: 2.000 inches.
   • Group Footer 1: 0.200 inches.
   • Page Footer : 0.300 inches.
     
     
     
10. Now that we have formatted our report, we will need some new fields. We will be adding 3 fields; firstname_b (Profile First Name), phone1 (Family Phone 1) and city_zip.
    • Select the field tool in the toolbar; the field tool is represented as “ab|” in the toolbar.
    • Click into the group header section and drag to create a sizeable box for our field. This will bring up the field properties. The field sizes can be modified later, if necessary, by dragging the square bounds on each side.
    • enter firstname_b into the expression box and click OK.
    • Repeat these steps 2 more times, but when asked for the expression, enter phone1 for the 2nd field and then city_zip for the 3rd field.
       
      
      
11. Now make sure all the fields are within the group header section so we can align them properly. Start by lining up the nameline, address and city_zip on the upper left-hand side. Then align the firstname_b below those 3 fields. Finally, align the phone1 field to the right of the firstname_b field. See the figure below to see how the report designer should look.
    
    
    
12. Next, we will add the family picture.
    • Select the Picture/OLE Bound Control tool from the toolbar. This tool is represented by a floppy disk with the letters OLE.
    • Click into the group header section and click and drag to create a sizeable box for the picture. This will bring up the properties.
    • Under the “control source type” section select Expression or variable name.
    • Under “control source:” enter family_picture.
    • Under “if source and frame are different sizes” select Scale contents, fill the frame. Click OK when finished.
       
      
      
13. We will now work on the Page Header for this report. Let's add a title to the pictorial directory. Select the Text Tool in the toolbar. The text tool looks like a capital A in the toolbar. Click into the Page Header section and start typing the name of your church.
    
    
    
14. 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.
    
    
    
15. Repeat steps 13 and 14 to create headers for the "Directory of Families," "Name,"and "Telephone". Once done, align the newly created headers in line with the name and phone fields like in the figure below.
    
    
    
16. 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).
    • 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.
    • Optionally you may want to create a line in the group footer section to split up families. Repeat the step from 16a to accomplish this.
    • 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.
      
17. 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.
    
    
18. Once you have completed these steps, select Save from the File menu. To close the Report Designer, select Close from the File menu.
    
    
19. 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.