Introduction to reports
When you use a database, you typically use reports to view, format, and summarize data. For instance, you might create a list-type report to display phone numbers for all your contacts, or a summary report to total up the sales for your company across different regions and time periods.
This article provides an overview of reports in Microsoft Access 2010, and introduces new or changed report features. This article also explains how to create a report, how to sort, group, and summarize data, and how to preview and print a report.
You'll find it's much easier to create meaningful reports if your database has a well-designed table structure and relationships. For an introduction to planning and designing a database, see the article Database design basics.
In this article
Overview
A report is a database object that you use to display and summarize data. Reports provide a way to distribute or archive snapshots of your data, either by being printed out, converted to PDF or XPS files, or exported to other file formats.
Reports can provide details about individual records, summaries across many records, or both. You can also use Access reports to create labels for mailings or other purposes.
It is possible to create "unbound" reports that do not display data, but for the purposes of this article, we'll assume that a report is bound to a data source such as a table or query.
Client reports vs. Web reports
Access 2010 provides a new feature that allows you to create a "Web database" by publishing an Access database to a SharePoint server that is running Access Services. When you create a Web database, Access reports are rendered in the browser using SQL Server Reporting Services. This conversion places some limitations on the features you can use in reports that you want to render in the browser. However, if you are not concerned about the report rendering in the browser, you can use the full set of features that the Access report designer provides.
Parts of a report
In Access, the design of a report is divided into sections. In a client database, you can view your report in Design view to see its sections. In Layout view, the sections are not as obvious, but they are still there, and can be selected by using the drop-down list in the Selection group on the Format tab. To create useful reports, you need to understand how each section works. For example, the section in which you choose to place a calculated control determines how Access calculates the results. The following list is a summary of the section types and their uses:
-
Report Header This section is printed just once, at the beginning of the report. Use the report header for information that might normally appear on a cover page, such as a logo, a title, or a date. When you place a calculated control that uses the Sum aggregate function in the report header, the sum calculated is for the entire report. The report header is printed before the page header.
-
Page Header This section is printed at the top of every page. For example, use a page header to repeat the report title on every page.
-
Group Header This section is printed at the beginning of each new group of records. Use the group header to print the group name. For example, in a report that is grouped by product, use the group header to print the product name. When you place a calculated control that uses the Sum aggregate function in the group header, the sum is for the current group. You can have multiple group header sections on a report, depending on how many grouping levels you have added. For more information about creating group headers and footers, see the section Add grouping, sorting, or totals.
-
Detail This section is printed once for every row in the record source. This is where you place the controls that make up the main body of the report.
-
Group Footer This section is printed at the end of each group of records. Use a group footer to print summary information for a group. You can have multiple group footer sections on a report, depending on how many grouping levels you have added.
-
Page Footer This section is printed at the end of every page. Use a page footer to print page numbers or per-page information.
-
Report Footer This section is printed just once, at the end of the report. Use the report footer to print report totals or other summary information for the entire report.
Note: In Design view, the report footer appears below the page footer. However, in all other views (Layout view, for example, or when the report is printed or previewed), the report footer appears above the page footer, just after the last group footer or detail line on the final page.
What's new
Creating a report in Access 2010 is a very similar process to creating a report in Access 2007. However, in Access 2010 there are a few new features that pertain to reports:
-
Shared Image Gallery In Access 2010, you can now attach an image to a database, and then use the image across multiple objects. Updating the single image updates it wherever it is used across the entire database.
-
Office Themes In Access 2010, you can now use standard Microsoft Office Themes to apply professionally-designed sets of fonts and colors to all of your Access forms and reports at once.
-
More powerful conditional formatting Access 2010 includes more powerful tools for highlighting data on a report. You can add up to 50 conditional formatting rules for each control or group of controls, and in client reports, you can add data bars to compare data across records.
-
More flexible layouts In Access 2010, the default design method for reports is to place controls in layouts. These grids help you align and size controls easily, and are required for any reports that you want to render in a browser. Although layouts themselves are not new in Access 2010, there are some changes to the ways in which you use them to move, align, and resize controls. For more information, see the video Introduction to form and report layouts.
Create a new report
Step 1: Choose a record source
The record source of a report can be a table, a named query, or an embedded query. The record source must contain all of the rows and columns of data you want display on the report.
-
If the data you want is contained in an existing table or query, select the table or query in the Navigation Pane, and then continue to Step 2: Choose a report tool.
-
If the record source does not yet exist, do one of the following:
-
Continue to Step 2: Choose a report tool and use the Blank Report tool, or
-
Create the table(s) or query that contains the data you want to use, select it in the Navigation Pane, and then continue to Step 2: Choose a report tool.
-
For more information about creating tables or queries, see the articles Introduction to tables or Introduction to queries.
For more information about creating and selecting record sources, see the article Set the record source for a report.
Step 2: Choose a report tool
The report tools are located on the Create tab of the ribbon, in the Reports group. The following table briefly describes what each tool does:
Web-compatible reports
Reports created by using these tools are compatible with the Publish to Access Services feature, and will render in a browser. Note that Web compatible objects are indicated by the globe on the object's icon.
| Button image | Tool | Description |
| | Report | Creates a simple, tabular report containing all of the fields in the record source you selected in the Navigation Pane. |
| | Blank Report | Opens a blank report in Layout view, and displays the Field List task pane. Access creates a record source query as you drag fields from the Field List to the report. |
For more information about the Publish to Access Services feature, see the article Build an Access database to share on the Web.
Client reports
Reports created by using these tools are not compatible with the Publish to Access Services feature. Adding client reports to a Web database will not prevent the database from publishing, but the client reports will not be available in the browser. However, you can use a client report when the database is open in Access.
| Button image | Tool | Description |
| | Report | Creates a simple, tabular report containing all of the fields in the record source you selected in the Navigation Pane. |
| | Report Design | Opens a blank report in Design view, to which you can add just the fields and controls you want. |
| | Blank Report | Opens a blank report in Layout view, and displays the Field List task pane. As you drag fields from the Field List to the report, Access creates an embedded query and stores it in the Record Source property of the report. |
| | Report Wizard | Displays a multiple-step wizard that lets you specify fields, grouping/sorting levels, and layout options. The wizard creates a report based on the selections you make. |
| | Labels | Displays a wizard that lets you select standard or custom label sizes, as well as which fields you want to display, and how you want them sorted. The wizard creates the label report based on the selections you make. |
Step 3: Create the report
-
Click the button for the tool you want to use. If a wizard appears, follow the steps in the wizard and click Finish on the last page.
Access displays the report in Layout view. -
Format the report until it looks the way you want:
-
Resize fields and labels by selecting them and then dragging the edges until they are the size you want.
-
Move a field by selecting it (and its label, if present), and then dragging it to the new location.
-
Right-click a field and use the commands on the shortcut menu to merge or split cells, delete or select fields, and perform other formatting tasks.
In addition, you can use the features described in the following sections to make your report more attractive and readable.
-
Add grouping, sorting, or totals
The fastest way to add grouping, sorting, or totals to a report is to right-click the field to which you want to apply the group, sort, or total, and then click the desired command on the shortcut menu.
You can also add grouping, sorting, or totals by using the Group, Sort, and Total pane while the report is open in Layout view or Design view:
-
If the Group, Sort, and Total pane is not already open, on the Design tab, in the Grouping and Totals group, click Group & Sort.
-
Click Add a group or Add a sort, and then select the field on which you want to group or sort.
-
Click More on a grouping or sorting line to set more options and to add totals.
For more detailed information about grouping, sorting, and totals, see the article Create a grouped or summary report.
Highlight values by using conditional formatting
Access 2010 includes more powerful tools for highlighting data on a report. You can add up to 50 conditional formatting rules for each control or group of controls, and in client reports, you can add data bars to compare data across records.
To add conditional formatting :
-
Open the report in Layout view by right-clicking the report in the Navigation Pane and then clicking Layout View.
-
Select all of the controls to which you want to apply the conditional formatting. To select multiple controls, hold down the SHIFT or CTRL key and click the controls you want.
-
On the Format tab, in the Control Formatting group, click Conditional Formatting.
Access opens the Conditional Formatting Rules Manager dialog box. -
In the Conditional Formatting Rules Manager dialog box, click New Rule.
-
In the New Formatting Rule dialog box, select a value under Select a rule type:
-
To create a rule that is evaluated for each record individually, select Check values in the current record or use an expression.
-
To create a rule that compares records to each other by using data bars, click Compare to other records.
-
Note: The Compare to other records option is not available in Web databases.
-
Under Edit the rule description, specify the rule that will determine when the formatting should be applied, as well as the formatting that you want when the rule's criteria are met.
-
Click OK to return to the Conditional Formatting Rules Manager dialog box.
-
To create an additional rule for this control or set of controls, repeat this procedure from step 4. Otherwise, click OK to close the dialog box.
For more information about adding conditional formatting to a report, see the video Use conditional formatting on reports.
Get a professional look with Themes
You can now apply Office 2010 Themes to an Access database, helping you to create a consistent style across all your Office documents.
Important: If you choose an Office Theme, font, or color, it is applied to all the forms and reports in your database (not just the one you are working on).
-
Open a report in Layout view by right-clicking it in the Navigation Pane and then clicking Layout View.
-
On the Design tab, in the Themes group, select the Theme, color or font that you want:
-
Use the Theme gallery to simultaneously set colors and fonts to a pre-designed scheme.
-
Use the Colors or Fonts galleries to set colors or fonts independently.
-
For more information about using Office Themes, see the video Make your Access forms and reports more consistent and appealing with Office Themes.
Add images
Traditionally in Access, images have been bound to individual image controls on forms or reports. To change a commonly-used image that was used across multiple forms and reports, each image control had to be edited manually. In Access 2010, however, you can now attach an image to a database once, and then use the image across multiple objects. Updating the single image updates it wherever it is used across the entire database. This is very useful for such things as company logos or background images that are used throughout a database.
Add an image
-
In the Navigation Pane, right-click the report to which you want to add the image, and then click Layout View.
-
Click on the report where you want to add the image.
-
On the Design tab, in the Controls group, click Insert Image.
-
Do one of the following:
-
Use an existing image If the image you want is already in the gallery, click it to add it to the report.
-
Upload a new image At the bottom of the gallery, click Browse. In the Insert Picture dialog box, navigate to the image you want to use, and then click Open.
Access adds the selected image to the report.
-
Add a background image
Note: Background images cannot be added to Web-compatible reports.
-
In the Navigation Pane, right-click the report to which you want to add the background image, and then click Layout View.
-
On the Format tab, in the Background group, click Background Image.
-
Do one of the following:
-
Use an existing image If the image you want is already in the gallery, click it to add it to the report.
-
Upload a new image At the bottom of the gallery, click Browse. In the Insert Picture dialog box, navigate to the image you want to use, and then click Open.
Access adds the selected image to the report.
-
Preview and print a report
Preview a report
-
Open the report you want to preview, or just select it in the Navigation Pane.
-
On the File tab, click Print, and then click Print Preview.
Access opens the report in Print Preview. You can use the commands on the Print Preview tab to do any of the following:-
Print the report;
-
Adjust page size or layout;
-
Zoom in or out, or view multiple pages at a time;
-
Refresh the data on the report;
-
Export the report to another file format.
-
-
To return to the database workspace, on the Print Preview tab, in the Close Preview group, click Close Print Preview.
Print a report
In addition to printing from Print Preview, you can also print a report without previewing:
-
Open the report you want to preview, or just select it in the Navigation Pane.
-
On the File tab, click Print.
-
To send the report directly to the default printer without setting printer options, click Quick Print.
-
To open a dialog box where you can select a printer, specify the number of copies, and so on, click Print.
-
No comments:
Post a Comment