Customizing De Novo Reports

Most of the preceding examples in this chapter are based on a standard report structure and template you chose when creating the Inventory by Category report in Chapter 16. When you start a report from scratch, you must add sections required by your reports, set up printing parameters, and, if the number of records in the data source is large, consider limiting the number of detail rows to supply only the most significant information.

Adding and Deleting Sections of Your Report

When you create a report from a blank template or modify a report created by the Report Wizard, add new sections to the report by using the following guidelines:

• To add Report Headers and Footers as a pair, choose View, Report Header/Footer.

• To add Page Headers and Footers as a pair, choose View, Page Header/Footer.

• To add a Group Header or Footer to a report with a Group By value specified, click the Sorting and Grouping button on the toolbar and set the Group Header or Group Footer property value, or both to Yes.

Figure 17.20 shows a blank report, with the headers and footers for each section that you can include in a report. (Although Figure 17.20 shows only one group, you can add up to 10 group levels to your report.)

Figure 17.20. A report with a single Group By property value has a total of seven sections.

If you group the data in more than one level (group, subgroup, sub-subgroup), you can add a Group Header and Footer for each level of grouping. This action adds to your report another pair of sections for each subgroup level.

You delete sections from reports by using methods similar to those that you use to create the sections. To delete unwanted sections, use the following guidelines:

• To delete the Detail section or an individual Report Header, Report Footer, Page Header, or Page Footer section, delete all controls from the section, and then drag the divider bar up so that the section has no depth. To delete a Report Footer, drag the report's bottom margin to the Report Footer border. These actions do not actually delete the sections, but sections with no depth do not print or affect the report's layout.

• To delete Report Headers and Footers as a pair, choose View, Report Header/Footer. If the Report Header or Footer includes a control, a message box warns you that you will lose the controls in the deleted sections.

• To delete Page Headers and Footers as a pair, choose View, Page Header/Footer. A warning message box appears if either section contains controls.

• To delete a Group Header or Footer, click the Sorting and Grouping button and set the Group Header or Group Footer property's value to No.

Tip

Page and Report Headers and Footers that incorporate thin lines at the upper border of the header or footer can be difficult to delete individually. To make these lines visible, choose Edit, Select All to add sizing anchors to the lines. Hold down the Shift key and click the controls that you want to save to deselect these controls. Then press the Delete key to delete the remaining selected lines.

Controlling Page Breaks and Printing Page Headers and Footers

The Force New Page and Keep Together properties of the report's Group Header, Detail, and Group Footer sections control manual page breaks. To set these properties, double-click the group's section header to display the section's Properties window. Force New Page causes an unconditional page break immediately before printing the section. If you set the Keep Together property to Yes and insufficient room is available on the current page to print the entire section, a page break occurs and the section prints on the next page.

To control whether Page Headers or Footers print on the first, last, or all pages of a report, press Ctrl+R or click the Select Report button, and then click the Properties button. You then select the Page Headers and Page Footers option in the Format page of the Properties window (see Figure 17.21).

Figure 17.21. Specify when Page Headers or Page Footers appear in the report in the Form Page of the Report Properties window.

Adding Other Controls to Reports

Access places no limit on the Toolbox controls that you can add to reports. So far, the controls that you've modified or added have been limited to labels, text boxes, lines, and the combo boxes that Access places automatically for fields configured as lookup fields. These four kinds of controls are likely to comprise more than 90% of the controls used in the reports you create. Controls that require user interaction, such as lists and combo boxes, can be used in a nonprinting section of the report, but practical use of these controls in reports is limited. The following list describes other controls that you might want to add to reports:

• Bound object frames Print the contents of the OLE Object field data type, including bound charts you design in the next chapter. An OLE object can be a still or animated graphic, a video clip, CD audio track, or even MIDI music. Reports are designed only for printing, so animated graphics, video, and sound are inappropriate for reports.

• Unbound object frames Display OLE objects created by OLE server applications, such as the Graph Wizard, Windows Paint, Microsoft Word, or Excel. Usually, you place unbound objects in the report's Form Header or Form Footer section, but you can add a logo to the top of each page by placing the image object in the Page Header section. A graph or chart created by the Chart Wizard is a special kind of unbound OLE object; you can't create a bound chart with the Wizard.

• ActiveX controls Similar to objects within unbound object frames. You can add PivotTables and PivotCharts to reports, but you must establish the design parameters for the PivotTable or PivotChart before printing.

• Lines and rectangles (also called shapes) Add graphic design elements to reports. Lines of varying widths can separate the sections of the report or emphasize a particular section.

• Check boxes and option buttons Can be used to indicate the values of Yes/No fields or within group frames to indicate multiple-choice selections. Group frames, option buttons, and check boxes used in reports indicate only the value of data cells and do not change the values. Reports seldom use option or toggle buttons.

Adding graphs in bound and unbound object frames and placing PivotTables and PivotCharts in reports are subjects covered in Chapter 18, "Adding Graphs, PivotCharts, and PivotTables."

Reducing the Length of Reports

A report's properties or controls don't limit the number of rows of detail data that a report presents. One way of minimizing detail data is to write a TOP N or TOP N PERCENT query using Jet or Transact-SQL. Chapter 21, "Moving from Jet Queries to Transact-SQL" has examples of the use of SELECT TOP N [PERCENT] statements. All rows of a table or query appear somewhere in the report's Detail section, if the report includes a Detail section with at least one control. To include only a selected range of dates in a report, for example, you must base the report on a query with the criteria necessary to select the Detail records or apply a filter to the report. If the user is to select the range of records to include in the report, use a parameter query as the report's data source.