A Tour of Reports

You can explore reports in Microsoft Office Access 2007 by examining the features of the sample reports in the ContactsDataCopy.accdb sample database. A good place to start is the rptContactProducts report. Open the database, and go to the Navigation Pane. Click the Navigation Pane menu, click Object Type under Navigate To Category, and then click Reports under Filter By Group to display a list of reports available in the database. Scroll down the list of reports in the Navigation Pane until you see the rptContactProducts report, as shown in Figure 14–1. Double-click the report name (or right-click it and click the Open command on the shortcut menu) to see the report in Print Preview-a view of how the report will look when it’s printed.

Figure 14–1: You can use the object shortcut menu to open a report from the Navigation Pane.

Note 

All the reports in the sample databases are set to print to the system default printer. The default printer on your system is probably not the same printer that we used as a default when we designed the report Some of the sample reports are designed with margins other than the default of 1, inch on all sides. If your default printer cannot print as close to the edge of the paper as the report is designed, Office Access 2007 will adjust the margins to the minimums for your printer. This means that some reports might not appear exactly as you see them in this book, and some data might appear on different pages.

Print Preview-A First Look

The rptContactProducts report is based on the qryRptContactProducts query, which brings together information from the tblContacts, tblProducts, and tblContactProducts tables. When the report opens in Print Preview, you’ll see a view of the report in the Contact Products window, as shown in Figure 14–2. When you open the report from the Navigation Pane, the report shows information for all contact product sales.

Figure 14–2: The rptContactProducts report in Print Preview shows sales data gathered from several tables.

You can expand the window in Print Preview by collapsing the Navigation Pane to see more of the rptContactProducts report horizontally. Use the vertical and horizontal scroll bars to position the report so that you can see most of the upper half of the first page. If you are using a smaller SVGA screen (800×600 pixels), click the arrow below the Zoom button in the Zoom group on the Ribbon and select 75% to see more of the report. If your screen resolution is 1024×768 or higher, you should be able to easily view the report at 100%. You can also use the Zoom control in the lower-right corner of your window to adjust the zoom level.

Checking Out Reports in the Sample Application

You can see all the reports described in this chapter in the Conrad Systems Contacts application. Start the application by opening the database (Contacts.accdb), opening frmSplash, and then signing on as either Jeff or John-you don’t need a password. To see the final version of the Contact Products report, for example, click the Products button on the main switchboard form, and then click the Print button on the CSD Contacts Products form to open the Product Reports dialog box. Select Product Sales By Contact. Also select the Current Product Only and All Records options, and then click Print You’ll see the report in Print Preview for the product that was displayed on the Products form. You can also explore the reports by clicking the Reports button on the main switchboard. All reports in the application have custom Ribbons that prevent you from switching to Design view when running the application. When you’re finished looking at the reports in Contacts.accdb, be sure to go back to the ContactsDataCopy.accdb file to follow the remaining examples in this chapter.

Troubleshooting

Why did the Print Preview tab disappear when I collapsed the Navigation Pane?

If you open a report in Print Preview, Access 2007 displays only the Print Preview contextual Ribbon tab. If you collapse the Navigation Pane while in this view, Access shifts the focus to the Navigation Pane, closes the Print Preview tab, and shows the four main Ribbon tabs. To show the Print Preview tab again, click on the report tab if you are using the Tabbed Documents window option, or click on the report itself to put the focus back on the report and redisplay the Print Preview tab.

To view other pages of the report, use the navigation bar in the lower-left corner of the window, as shown here.

The four buttons, from left to right, are the First Page button, Previous Page button, Next Page button, and Last Page button. The Page Number box is in the middle. To move forward one page at a time, click the Next Page button. You can also click the Page Number box (or press Alt+F5 to select it), change the number, and press Enter to move to the exact page you want. Press Esc to exit the Page Number box. As you might guess, the Previous Page button moves you back one page, and the two outer buttons move you to the first or the last page of the report. You can also move to the top of the page by pressing Ctrl+Up Arrow, move to the bottom of the page by pressing Ctrl+Down Arrow, move to the left edge of the page by pressing Home or Ctrl+Left Arrow, and move to the right edge of the page by pressing End or Ctrl+Right Arrow. Pressing Ctrl+Home moves you to the upper-left corner of the page, and pressing Ctrl+End moves you to the lowerright corner of the page.

Headers, Detail Sections, Footers, and Groups

Although the rptContactProducts report looks simple at first glance, it actually contains a lot of information. Figure 14–3 shows you the report again with the various sections of the report marked. You can see a page header that appears at the top of every page. As you’ll see later when you learn to design reports, you can also define a header for the entire report and choose whether to print this report header on a page by itself or with the first page header.

Figure 14–3: The rptContactProducts report has a subtotal for each contact.

The data in this report is grouped by contact name, and the detail lines are sorted within contact name by date sold. You can print a heading for each group in your report, and this report has a heading for each contact. This report could easily be modified, for example, to display the product category in a header line (to group the products by category), followed by the related detail lines.

Next Access prints the detail information, one line for each row in the recordset formed by the query. In the Detail section of a report, you can add unbound controls to calculate a result using any of the columns in the record source.

Below the product detail lines for each contact, you can see the group footer for the contact. You could also calculate percentages for a detail record or for a group by including a control that provides a summary in the group footer (total for the group) or report footer (total for the report). To calculate the percentage, you would create an additional control that divides the detail or group value by the total value in an outer group or in the report footer. Access can do this because its report writer can look at the detail data twice-once to calculate any group or grand totals, and a second time to calculate expressions that reference those totals. If you scroll down to the bottom of the page, you’ll see a page number, which is in the page footer.

Note 

If you’re working with a report that has many pages, it might take a long time to move to the first or last page or to move back one page. You can press Esc to cancel your movement request Access 2007 then closes the report.

A slightly more complex report is rptProductSalesByProduct. Open that report, and go to the last page. At the end of this report, as shown in Figure 14–4, you can see the quantity and sales totals for the last product in the report, for the last category in the report, and for all sales in the database (Grand Total). There are two products in the Single User category, but the first is a demonstration edition that has a zero price, so the total sales amount of the category matches the total sales amount of the second product. The grand total is in the report footer.

Figure 14–4: The rptProductSalesByProduct report’s grand total calculation is in the report footer.

Subreports

Just as you can embed subforms within forms, you can embed subreports (or subforms) within reports. Subreports are particularly useful for showing related details or totals for the records that make up the source rows of your report. In the Conrad Systems Contacts database, you can bring together information about contacts and products-either contacts and the products they own or products and the contacts who own them. You can place detailed data about contacts and products in a subreport and then embed that subreport in the Detail section of a report that displays company data-much as you did for the fsubContactProducts form exercise in the previous chapter.

You can see an example of this use of a subreport in the rptCompanyProducts report and in the rsubCompanyProducts subreport in the Conrad Systems Contacts database. In the Navigation Pane, right-click on the rsubCompanyProducts subreport, and then click Design View on the shortcut menu to open the subreport in Design view, as shown in Figure 14–5. The Report window in Design view is shown in Figure 14–6.

Figure 14–5: Select Design View from the shortcut menu to open rsubCompanyProducts in Design view.

Figure 14–6: This is the Report window for the rsubCompanyProducts report in Design view.

You can see that this report looks very much like the continuous form you designed earlier to be a subform. If you look at the Record Source property for the subreport, you’ll find that it uses the qryRsubCompanyProducts query, which isn’t at all simple. The query brings together information from the tblProducts, tblContactProducts, tblContacts, and tblCompanyContacts tables. This subreport doesn’t display any company information at all. Switch to Print Preview by clicking the arrow in the Views group on the Ribbon and clicking Print Preview in the list of available views. You’ll see a list of various products and the contacts who own them, in date sold order, as shown in Figure 14–7.

Figure 14–7: Switch to Print Preview for the rsubCompanyProducts report to see a complex list of sales history.

Close the subreport and open the rptCompanyProducts report in Print Preview, shown in Figure 14–8. Notice as you move from company to company that the data displayed in the subreport changes to match the company currently displayed. The data from the rsubCompanyProducts report now makes sense within the context of a particular company. Access links the data from each subreport in this example using the Link Master Fields and Link Child Fields properties of the subreport (which are set to the linking CompanyID field)-just as with the subforms you created in Chapter 13, “Advanced Form Design.”

Figure 14–8: The rptCompanyProducts report has an embedded subreport to display each company’s purchase history.

As you’ll see in the next section, when we examine some features of the rptInvoices report, that report also uses subreports to link information from three related tables to each row displayed from the tblInvoices table.

Objects in Reports

As with forms, you can embed objects in reports. The objects embedded in or linked to reports are usually pictures or charts. You can embed a picture or a chart as an unbound object in the report itself, or you can link a picture or a chart as an object bound to data in your database.

The rptInvoices report in the Conrad Systems Contacts database has an image object. When you open the rptInvoices report in Print Preview, you can see the Conrad Systems logo (a stylized font graphic) embedded in the report title as an unbound bitmap image object, as shown in Figure 14–9. This object is actually a part of the report design

Figure 14–9: The rptInvoices report has an unbound bitmap image object (the Conrad Systems logo) embedded in the report header.

To see an example of how an object prints when it’s stored in a table, open rptContacts, as shown in Figure 14–10. This picture is a bitmap image object stored in an attachment field in the tblContacts table-a picture of the contact.

Figure 14–10: The Photo field in the rptContacts report is a bitmap image object stored in an attachment field.

Note 

You might notice that the Notes field in Figure 14–10 doesn’t show all the text There’s a special version of this report called rptContactsExpandNotes that fixes this problem with Visual Basic code. See Chapter 20, “Automating Your Application with Visual Basic,” for details.

Report View-A First Look

As you learned in Chapter 3, “Microsoft Office Access 2007 Overview,” Access 2007 includes a new view for reports called Report view. Unlike Print Preview, which presents static data, you can use Report view to interact with data in the report. You can explore reports that take advantage of this new view in the Housing.accdb sample database. A good place to start is the rptEmployeesPlain report. Open the database, and go to the Navigation Pane. Click the Navigation Pane menu, and click Object Type under Navigate To Category, and then click Reports under Filter By Group to display a list of reports available in the database. Scroll down the list of reports until you see the rptEmployeesPlain report, as shown in Figure 14–11. Right-click the report, and click Open to see the report in Report view.

Figure 14–11: When you click Open on the shortcut menu for the rptEmployeesPlain report, Access opens it in Report view.

Inside Out-Understanding the Open Command for Reports 

You might be wondering why clicking the Open command for the rptEmployeesPlain report opens it in Report view, but the same command opens the rptContactProducts report (discussed earlier) in Print Preview. Access 2007 includes a new report property called Default View. You can define whether a report opens in Report view or Print Preview by using this property. The default setting when you create a new report is Report view. For the rptEmployeesPlain report we left this property set to Report view, so when you double-click on the report in the Navigation Pane or click the Open command on the object shortcut menu, Access opens the report in Report view. We will discuss this new property further in Chapter 16, “Advanced Report Design.”

The rptEmployeesPlain report is based on the qryRptEmployees query, which brings together information from the tblEmployees and tblDepartments tables. When the report opens in Report view, you’ll see the data from these tables in the Housing Reservations database formatted in the report, as shown in Figure 14–12.

Figure 14–12: The rptEmployeesPlain report is set to open in the new Report view.

If you look closely at Figure 14–12 you will no doubt notice that there is no Page Number box in the lower-left corner of the window. You can also see that the Print Preview contextual Ribbon tab is not available. Access displays the four main Ribbon tabs in Report view so that you can use filters to interact and drill down to specific records and then print only this smaller group of records. If you were to print this report right now, all 16 employee records would be sent to your printer. Suppose though that you want to print only the records of employees who are in the Finance department. You could create a separate report that would show only the employees in the Finance department, but from within Report view you can ask Access to filter the records to display only the employee records you need. With the rptEmployeesPlain open in Report view, right-click in the Department field for the first record and click Equals “Finance” on the shortcut menu, as shown in Figure 14–13.

Figure 14–13: In Report view you can filter the records to show just the ones you want to print.

After you click Equals “Finance,” Access filters all the records in the report record source to obtain the three employees in the Finance department, as shown in Figure 14–14. If you print the report at this point, Access prints only a one-page report with the three records.

Figure 14–14: After you apply the filter shown in Figure 4–13, Access shows only the three employees in the Finance department.

By using Report view, you can create a single report that returns all records and then use the filtering capabilities of Access to drill down to subsets of the data. To remove the filter applied to this report, click the Toggle Filter button in the Sort & Filter group on the Home tab, and Access again displays all 16 employee records. Report view gives you as many of the filtering and sorting capabilities as you have in forms but in an object that’s designed to be printed rather than edit data.

In the new Report view, you can also define controls that respond to events, similar to what you can do with forms. We defined a Click event for the employee number text box and styled it as a hyperlink to provide a visual cue to the user. In Figure 14–15, you can see that the Employee Number field looks like a hyperlink with the data in blue and underlined. Clicking the Employee Number field opens the frmEmployeesPlain form as a dialog box displaying all information for that specific employee so that you can make any necessary changes. After closing the form and returning to the report, click the Refresh All command in the Records group on the Home tab to see any changes you made to the data using the form reflected in the report.

Figure 14–15: You can use Report view to respond to control events such as opening data entry forms.