Custom Report Example - Pictorial Directory

One of the most commonly requested custom reports in PowerChurch Plus is the Pictorial Directory. Although every church will want something a little different, the following is a short example that will help you with designing your own customized directory.

1. From the Personal Profiles menu, choose Other Output, then Custom Reports.
2. Begin by adding a new query entitled Custom Church Directory. If this is your first Personal Profiles custom report, you will be automatically prompted for a query title. Otherwise, click Add. After entering the title, click OK.
3. For this example, we will use the following query condition: Personal Status | exactly matches | Member. You can enter additional query conditions, such as Include in Memb. Rpts | is true. To do this, click the Add button, then choose an AND/OR connector to place between the new condition and the prior. When finished building the query, click Done.
4. At this point, you will be prompted: "Do you want to run this query now?" Click Yes.
5. After a short instructional screen, you will be prompted to select fields to show in the query output. Select the following fields: 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. To select these fields, simply double click on each name. Click Done when you are finished.
6. 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.
7. Back at the Query Overview Window, uncheck Browse query results and click Run Query.
8. At the Setup for Column Layout Report window, click on Screen in the Output Direction box and then click on the Edit Form button.
9. 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.
10. Click on the Add button to open the Expression Builder dialog box. Enter the following expression: lastname + firstname_a + address. Click OK then OK again.
11. On the Report Designer, you will notice five gray bars: Page Header, Group Header 1, Detail, Group Footer 1, and Page Footer. To receive the desired output on this report, we will need to resize these bands. Here's how: Move your mouse over the gray bar labeled Group Header 1. Your pointer will turn into a small up and down arrow. Hold down the left mouse button and drag the bar down about two inches, or enough to accommodate the size of Family Picture that you plan to include. Release the mouse button. You will notice that the white area just above the Group Header 1 is now larger.
12. Next, we will move some of the output fields into the newly resized Group Header 1 band. Click and drag the nameline field up to the Group Header 1 band (just above the grey Group Header 1 bar). Move the address and city_zip boxes ( if you don't see city_zip, you can add it in Step 13 below) into the Group Header 1 band in the same way. (Note: You may need to resize the Group Header 1 band again. You also may need to resize the nameline, address and city_zip fields to accommodate longer family names. See Step 16 below.)
13. In this example, we will not need the fields lastname and firstname_a. To delete them, click on the field box lastname and then press the Delete key. Click on the field box firstname_a and, again, press the Delete key. We will also not be using the text labels located in the Page Header band or the DATE() field in the Page Footer. To delete these text boxes, simply click on them and press Delete. In this example, we kept the Page label and the _Pageno field located in the Page Footer.
14. In our next step, we will be adding fields: firstname_b (Profile First Name), phone1 (Family Phone 1) and city_zip, if necessary, from Step 11 above. To create a field, click on the Field tool from the Report Controls tool bar. Once you have clicked on this tool, move your pointer into the Group Header 1 band, or appropriate area, and click again. A Field Properties dialog box will be displayed. In the expression area, type firstname_b and click OK. Click on the newly created field box and move it to the proper location. Repeat the same steps to create the remaining fields and move them to their appropriate locations as shown above. These new items may also need to be resized. See Step 17 below.
15. Next, we will add a family picture to the report. Click on the Picture/OLE Bound Control tool on the tool bar. Click in the Group Header 1 band at the point where you want the image to appear and drag it to the size you will need. On the Picture/OLE Bound Properties dialog box, select Expression or variable name under Control source type. Then click in the Control source: text box and type family_picture. Also, change If source and frame are different sizes: to Scale contents, retain shape if necessary; see below. Click OK. You may need to adjust the Group Header 1 area again to allow for a bigger/smaller picture.
16. We will now create a Page Header for this report. Before we begin to add text, resize the Page Header band using the same procedures outlined in Step 11 above. Next, click on the Label tool on the tool bar. Move your pointer into the white area above the Page Header bar and click again. Your pointer will now become a blinking cursor. Type the name of your church and then click on the Pointer tool on the tool bar. Now, let's change the font used on the church name. Click on the label that contains your church's name. Then, select Font from the Format menu. Select the font you wish to use and then click OK. (In this example, most fields are Arial 10 point but the church name is 24 point, Bold). Repeat the same procedures as you create the remaining three labels: Directory of Families, NAME, and TELEPHONE. You can also add a Directory of Families label to the Page Footer. Move these labels to their proper locations. You may also need to change the font for any field/label on the report that was originally there, nameline, address, etc.
17. Some fields may need to be resized. To do this, first click on a field to select it. You will notice that small squares appear around the box once it is selected. Position your mouse over one of these small squares, click down, and drag that square to enlarge the box.
18. Finally, you will need to create lines that accent your report. To draw lines, click on the Line tool on the tool bar. Next, move your pointer into the Page Header band, just above the Name label. 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. Repeat for any additional lines you want to add to the report.
19. If you have a scanned graphic of your church's logo available, you can add it to this report by selecting the Picture tool again from the tool bar and then clicking anywhere on the Page Header band. The Picture/OLE Bound Properties dialog box will appear. Enter the path and file name of your graphic, or if you don't know that information, you can browse for the file by clicking on the ellipsis button (...) next to the Control Source field. Next, select Scale contents, retain shape from the drop down box for the If source and frame are different sizes: field. Click OK. The picture will appear wherever you initially clicked. You may need to resize or move the picture.
20. At any time, you can see what the report will look like by clicking File and Print Preview. To get back to the Report Designer window, close the Print Preview window by clicking on File > Close, or click on the Close Preview icon located next to the Print Report icon.
21. Once you have completed these steps and are satisfied with the preview, 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 Column 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.