Custom Report Example - Activity Mailing Labels

This example uses the Activities & Skills module. It shows you how to print mailing labels for specific activity/skill codes.

1. First you will need to define a query. Do this by going to: Membership > Activities & Skills > Other Output > Custom Reports. If you have never created a Custom Report in this module, you will be prompted to enter a title for your query (otherwise click the Add button). Enter the title "Activity Mailing Labels" and click OK.
2. Our next step is to build a filter that will select the members of the desired activity/skill. On the Pick a Field tab, scroll down and click on the Activity Number field and click Next Step or double-click the field to automatically move to the next step. On the Pick an Operator tab, the Equal to number operator should be selected by default so double-click it to move to the next step or click Next Step. On the Enter a value tab, click the button labeled Ask later because we want to be asked for an activity/skill code each time we run the query. Click Done.
3. At this point you will be asked "Do you want to run this query now?". Click Yes. A dialog box will be shown that briefly describes how to use the Field Picker Dialog box. After reading the descriptions, click OK.
4. The Select Fields to Show in Query dialog box is displayed. Choose the following as output fields by double-clicking them: Mailing Name, Address Line 1, and City+State+Zip/Postal. When you are finished, click Done.
5. You can specify the sort order by clicking on Edit Sort Order from the Query Overview window and double-clicking the field or fields to use. For this example, we will sort the labels alphabetically by Household Key Last Name, so double-click on that field and click Done.
6. On the Query Overview screen, check the box to "Hide duplicate records". This will prevent multiple labels from printing for the same family. Then uncheck "Browse query results". At this point, the Query Overview window should look like the following picture:
   
   
   
   Click Run Query. You will be prompted to enter the activity/skill number that you want to print labels for. Enter that number and click Done.
7. PowerChurch Plus will display a screen titled "Setup for Column Layout Report". Change the Output Direction to Screen and click Edit Form. You will now see the Report Designer screen:
   
   
   
   Looking on the screen, you will notice the report is divided into sections. These sections are called "bands". Double click on the Page Header band and set the height to 0.5 inches and click OK. Do the same to the Page Footer band. Double-click on the Detail band and on the General tab, set its height to 1.0 inch and click OK. See the following picture:
   
   
   
   
8. For labels, you will need to direct the Custom Report Writer to print the data in columns. To do this, click on File and go to Page Setup. If you have 20 label sheets, set Number of Columns to 2, Spacing to 0.1 inches and Left margin to 0.25 inches. For 30 label sheets, set the Number of Columns to 3, and set Spacing and Left margin accordingly. The Spacing and Left margin settings used for this example are approximate, you may have to adjust for your brand of labels. Lastly, set the Print area for this report to Whole page and click OK.
9. In the Page Header and Page Footer bands, delete any labels or fields there, like "Mailing Name", "Address", "DATE()", etc., by clicking on each field once to select it and pressing the Delete key on your keyboard.
10. The fields in the Detail band (just below the Column Header band) need to be lined up. You do this by clicking on the fields, holding down the left mouse button, and dragging them underneath one another. See the following picture:
    
    
    
    
11. Now the fields need to be aligned and trimmed of excess space. Click on the nameline field in the Detail band and while it is selected (you will see Bounding Boxes around it), press the left arrow key on your keyboard a few times to make sure it is lining up to the left margin. Then, double-click it to bring up the Field Properties screen and change the Width to accommodate longer names. If you are using 20 label sheets, 3.5 inches would be a good width, but you can adjust this accordingly and click OK. While the first field is still selected, hold down the Shift key and click on the other fields to select them. You should see Bounding Boxes around each field, indicating they are also selected. See the following picture:
    
    
    
    Click on Format at the top of the screen. Click on Align, then Align Left Sides. Click on Format again, then Size, then To Widest. This will make all the fields Left-Aligned and the same width. See the following picture:
    
    
    
    
12. Click in the Page Footer area to de-select the fields. Next, double-click the nameline field again to open the Field Properties screen. In the Expression box, make sure the cursor is blinking to the left of the field name and type "alltrim("(without quotes). Then, with the right arrow key, move the cursor to the end of the field name and type ")" (without quotes), so that it looks like "alltrim(nameline)" (without quotes) and click OK. Then, do the same for the other fields, adding "alltrim()" (without quotes) to each field name. See the example below:
    
    
    
    
13. Once all of these steps are completed, you are ready to run the report. Click on File at the top of the screen, then Close and Yes to save the changes. From the Setup for Column Layout Report screen, click Start report and the labels will be displayed. You can click on the Print Report button at the top of the screen to print the labels, or click the Close Preview button to close the preview window and return to the Query Overview screen.