Custom Report Example - Profile Mailing Labels W/ Phone Numbers

Objective:

In this example, we will learn how to utilize the custom reports feature to create a personal profile label specifically focused on including phone numbers. Additional fields could be added or removed during this process, such as adding a custom code or note.

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 Profile 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 First Name + Last Name, Address Line 1, City+State+Zip/Postal and Family Phone 1 from the “fields to pick from” section.
   
5. From the Query Overview Window select Edit Sort Order. Select the following fields, to sort alphabetically: Profile Last Name, Profile First Name, and Address Line 1. Once these fields have been selected, click Done.
6. Now click Run Query. Next, click on Edit form to start customizing your output.
7. Start by adjusting 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, Detail 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. Refer to the figure below to see what this should look like once done.
   • Page Header: 0.500 inches.
   • Detail: 1.000 inches.
   • Page Footer : 0.500 inches.
   
8. You will now need to direct the Custom Report Writer to print the names in columns. To do this, click on File and go to Page Setup. If you have a 20 label sheet, set the columns to 2. For 30 label sheets, set the number of columns to 3. Change the Spacing to 0.1 and the Left margin to 0.25, and lastly, set the Print Area for this report to Whole Page. Click OK.
   
9. Next, align the remaining fields in the detail section. You will want the fields left most and on top of each other. Refer to the figure below.
10. Just above the Page Footer band at the bottom of the screen, you will notice three fields; date(), page, and pageno_. Click on each field once and press the Delete key on your keyboard.
11. Just above the Page Header at the top of the screen, you will notice 4 fields; First Name Last Name, Address, City + State + Zip/Postal and Phone. Click on each field once and press the Delete key on your keyboard.
    
12. Next, let's tidy up our labels. Start by selecting all the fields by clicking on each one and then choosing the Format option. Now select Align and Align Left Sides. While still selected, go back to Format, select Size and To Widest. These settings will ensure that all names will fit the labels.
13. Finally, we will want to modify each field's expression to have “alltrim()” around them. This will remove leading and trailing blanks in case a member's name or other information wasn’t correctly input.
    • by double clicking on the first_last field to bring up the properties.
    • From here, modify the existing expression by adding “alltrim()” around it. “alltrim(first_last)” should be the result for the first field.
    • Repeat this process for the remaining 3 fields.
    
14. Once all these steps are completed, you can close the Report Designer and save your changes. Do this by clicking on the File Menu and selecting Save and Close. Start the report by clicking Start Report from the Setup for Column Layout Report Screen.