Custom Report Example - Total Contributions By Envelope Report

Objective:

This example creates a report that shows the total contributions to each fund by envelope number. It uses data from the Contributions module. The report contains two nested groups of records. It groups records first by Envelope Number and then by Contribution Fund. It includes a summary total for each fund, a subtotal by envelope, and a grand total at the end of the report.

Result:

Steps:

1. To begin, start by navigating to the Contributions module, select the Other Output option and then finally Custom Reports. When prompted, choose the Posted option.
   
   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 Contributions 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. 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 Contribution Date.
   • From the "Step 2: Pick an Operator" tab, select Between two dates.
   • From the “Step 3: Enter a value” tab, select Ask Later.
   • 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 Envelope Number, Envelope Name, Contribution Fund Number, Contribution Fund Desc and Contribution Amount from the “fields to pick from” section.
   
   
5. Now we are going to define three Groups.
   • First, start by selecting the Envelope Number from the list of fields to include. Then click Group at the bottom to see the Select Group Field Type dialog box. Select the first option, “Group records first by this field (GRP 1)” and click done.
   • Next, select Contribution Fund Number and click Group again and choose “Group Records Second By This Field(GRP2)”. This option counts the number of records in each Activity Number group.
   • Lastly, select Contribution Amount and click Group again and choose “Sum this field for each group (SUM)”. Click done and then done again to finalize the output.
     
     
     
6. From the Query Overview Window select Edit Sort Order. Select the following fields, to sort alphabetically: Profile Last Name, Profile First Name, Envelope Number, and Contribution Fund Number. Once these fields have been selected, click Done.
   
   
7. The next task is to group the records on the report by Envelope Number and add a subtotal. Start by selecting the Pick Output Type button near the top of the Query Overview Window.
   1. On the Type of Output dialog box, choose Detail/Summary Report and click Edit.
   2. From the “Group 1st by” list, choose Envelope Number.
      
   3. Click the Step 2 tab and from the “Summarize the field” list select Contribution Amount. When finished, click Done and Done again.
      
      
      
8. Now click Run Query. Next, click on Edit form to start customizing your output.
9. First, we will add a Grand Total field.
   1. Start by adding the summary band. Choose Properties from the Report menu, then click the Optional Bands tab. Check the Report Has Summary Band option and click OK.
   2. Next, copy the “sum_amount” field and paste it into the Summary band at the bottom.
   3. Double click on the sum_amount to modify some properties. Select the Calculate tab and select Sum in the “calculation type” field. Also, select Report from the “reset based on” field.
   4. Finally, add a label by using the label tool in the toolbar. Make this label “Grand Total” and align it in front of sum_amount.
      
10. Next, we need to suppress duplicate values.
    1. Double click on the env_no field to start and navigate to the Print When tab.
    2. From here select No, under “Print Repeated Values” and then OK.
    3. Now double click on the env_name field and repeat steps 1 and 2.
    4. Additionally for env_name, check the box next to “When this data group expression changes” under Also Print.
       
       
       
11. Now, let's clean up the report and make it more professional-looking.
    • Delete the Fund Number field and header description. This field appears in the output for sorting purposes, but it isn't necessary in the final report. To delete the field, choose the Select tool, click fund_no, and press Delete. Repeat the process with the Fund text object.
    • Move the desc field and the Desc text object to the left. Click and drag them one at a time. They should be at about the 4" mark on the ruler bar.
    • Change the wording of the headers.
      • Change Env. No. to Envelope.
      • Change Desc to Fund Description.
      • Change Amt to Amount.
        
12. Finally, let's add a title. First, create some extra space by dragging the bottom edge of the page header band downward. Next, drag the existing header titles to the bottom of the report band. Then use the Label tool to add the church name to the top of the band above the header titles. To center the title within the band, choose Align from the Format menu and select the Center Horizontal option. Use the Font command on the Format menu to display the header in 18-point bold face italic text.
    
13. You're finally ready to print the report. On the Report Designer, choose Save from the File menu. Then exit the Report Designer and click Start Report on the Report Setup dialog box.