Custom Report Example - Total Contributions By Envelope Report

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.

The first task in this example is to create the query. The following procedure gives the details. Subsequent procedures show how to add subtotals, a grand total, and a report title.

To create the report query:

1. Create a new query for the Contributions module. Select Custom Reports from the Contributions menu and choose the Posted option. Give the new query the title "Total Amount Given to Each Fund by Envelope."
2. Create the filter condition. Choose Contribution Date as the field and Between Two Dates as the operator. For the Contribution Date range, select Ask Later. This option prompts you for a date range every time you select the query.
3. Choose the output fields. On the Query List Manager, choose the new query and click Select. On the Select Fields to Show in Query window, choose the following fields: Envelope Number, Envelope Name, Contribution Fund Number, Contribution Fund Desc, and Contribution Amount.
4. Group and summarize. On the Select Fields to Show in Query window, choose two group fields and one summary field:
   • Choose Envelope Number, click Group, select Group Records First By This Field, and click Done.
   • Choose Contribution Fund Number, click Group, select Group Records Second By This Field, and click Done.
   • Choose Contribution Amount, click Group, select Sum This Field For Each Group, and click Done.
5. Specify the sort order. Select Edit Sort Order on the Query Overview window. Choose the following sort fields: Profile Last Name, Profile First Name, Envelope Number, Contribution Fund Number. This example sorts records alphabetically using a method similar to the previous example, the "Contributions Over $250" report. The additions of Envelope Number and Contribution Fund Number are for the groups defined in the previous step. After sorting the output alphabetically, the groups are sorted by Envelope Number and then Fund Number.

The next task is to group the records on the report by Envelope Number and add a subtotal.

To group by Envelope Number and add a subtotal:

1. On the Query Overview window, click Pick Output Type.
2. On the Type of Output dialog box, choose Detail/Summary Report and click Edit.
3. From the Group 1st by list, choose Envelope Number.
4. Click the Step 2: Pick Summary Field tab.
5. From the Summarize the Field list, choose Contribution Amount.
6. Click the Sum button.
7. Click Done, and then Done again.

The next step is to add a grand total at the end of the report. You work on the Report Designer for this step.

To add a grand total:

1. On the Query Overview window, click Run Query. You see a prompt asking for the Contribution Date range. Enter a range of dates.
2. If a preview window appears, close it.
3. On the Report Setup dialog box, click Edit Form.
4. Add a Summary band. Choose Properties from the Report menu, then click the Optional Bands tab. Check the Report Has Summary Band option and click OK.
5. Insert the sum_amount field into the Summary band. Use the Copy and Paste functions on the Edit menu. First, select the sum_amount field in the Detail band (not the Group Footer band). Then choose Copy, then Paste, and then drag the field into the Summary band.
6. Specify the summary type for the sum_amount field. Double-click the sum_amount object in the Summary band to display the Field Properties window. Click the Calculate tab and select Sum in the Calculation Type field. Also, select Report from the Reset Based On field.
7. Add the label "Grand Total" next to the summary field. Use the Label tool to add this object. You can display the label in boldface text by selecting it and choosing the Font option from the Format menu.

The next task will look familiar if you worked through the report to print contributions over $250. The following procedure shows how to suppress duplicate records from the report. The result is that each name and envelope number appear only once.

To suppress repeated values:

1. On the Report Designer window, double-click the field env_no.
2. On the Field Properties dialog box, click the Print When tab.
3. Choose No under Print Repeated Values.
4. Click OK to return to the Report Designer.
5. Repeat steps 1 through 3 for the env_name field.
6. On the Print When tab of the Field Properties dialog box, in the Also Print section, check When This Group Changes. Notice that the field env_no appears in the list to the right of this option. You used the envelope number field to define a group in an earlier procedure.
7. Click OK to return to the Report Designer. The purpose of these steps is to show how duplicate names are handled if different envelopes have the same name.

The final task for this report is to clean it up a bit and add a title.

To create a professional looking report:

1. 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.
2. 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.
3. Change the wording of the headers.
   • Change Env. No. to Envelope.
   • Change Desc to Fund Description.
   • Change Amt to Amount.

To change a text object, select the Label tool and then click the object. You can edit the object while it is selected.

1. Widen the env_name and desc fields. Choose the Select tool and then click one of the objects. Drag its right handle to widen the field. Each field should be long enough so that the corresponding data won't be truncated in the report.
2. Add a church name to the Page Header band as 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.

Note that you could create a separate Title band in step 5 instead of using the header band. However, the contents of the Title band appear just once at the start of the report. The Page Header contents appear on every page.

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. The following illustration shows how the report should look.