Advanced Formatting for Reports in Access 2013

Group and Sort

To group and sort data in your reports, open up a report. We're going to use the basic Books report.

Switch to Design view and go to the Design tab. Click Group & Sort in the Grouping & Totals group.

This will appear at the bottom of your report:

Decide how you want to group and sort. This is really easy.

Click Add a Group, then add how you want to group.

It will ask you what you want to group by (pick a data label in your report), and how you want to sort that group.

In this example, we are going to group by Author, from A to Z.

You can see how the report has been updated to add a grouping row for Author.

Conditional Formatting

Conditional formatting is used to change the appearance of a control. You can highlight data on your report when you do this, or simply change the format. Conditional formatting can be set for a text box or combo control box. It's called conditional formatting because the control must meet specific conditions in order for the formatting to be applied.

Let's show you how it works and how to do it.

Go to the Layout view of your report.

Now, click on the control that you want to apply conditional formatting. We've selected Price.

Under the Format tab, click Conditional Formatting in the Control Formatting group.

You'll now see this window:

Click on New Rule. Now you're going to put it to use. Enter your criteria as we've done below. Select the formatting that you want.

Click OK.

Then click Apply, then OK.

The conditional formatting is now applied to our report. All Price values greater than 10 have been made a bold red font.

Create Data Bars

Data bars show the value of your records by showing the contrast between the records. Look at the example below to get a visualization of what data bars look like:

Data bars can only be applied to a bound control that has a numeric data type. They make it easier to compare values. When you want to apply data bars, you use the Conditional Formatting Rules Manager.

To apply data bars, go to the Layout view of your report.

Next, select the control for which you want to apply data bars, as we've done below.

Go to the Format tab, then click Conditional Formatting.

Click New Rule.

Select Compare to Other Records, as we've done above.

If you don't want to see the numeric values, click Show Bar Only

Now specify how you want to represent the shortest bar. For example, the lowest value will be the shortest bar. You can also select number or percent. Take a look at the tables below to understand this better.

Shortest Bar

Select:	What It Will Do:
Lowest Value	This is the default setting and sets visual limit for lowest value.
Number	All values less than or equal to the shortest bar are length 0. Interested in learning more? Why not take an online Microsoft Access 2013 course?
Percent	Like the number setting, but percentage for each value is compared with rest of the records.

Do the same thing for the longest bar.

Longest Bar

Select:	What It Will Do:
Highest Value	The default setting and sets the limit for the highest value.
Number	All values greater than or equal to longest bar number fill the control. The bar goes all the way across the control.
Percent	Like the number setting, but percentage for each value is compared with rest of the records.

Select a bar color, then click OK.

Click Apply, then OK.

Below you can see the data bars added to our report. We left the data showing, but you might want to take yours out.

Printing and Exporting Reports

There are many new ways to print and export reports in Access 2013. In this article, we'll introduce you to them, and show you how to do them.

Previewing a Report

If you're used to printing in Microsoft Word, you may at first feel a bit frustrated when trying to print a report correctly in Access 2013. It isn't because it can't be done, it's because there are a few extra steps you need to take in order to assure that the report prints correctly, and in a pleasing manner. To illustrate the possible issues, we're going to show you a report we've run on the table we've created called "Books."

In the example above, we've selected Layout View. To get here, we went to the Design tab of the Report Layout Tools and clicked the View button.

In this view, we are able to change the way the report looks. In Print Preview, we can only see an exact replica of the way it will print, not alter it.

If you look closely, you will see some dotted lines. We've marked the intersections of these lines with red arrows. Those dotted lines represent the edge of the printable area. They are like margins in Microsoft Word.

Now look at the next example. This is in Print Preview. It shows us exactly what the report would look like when printed.

Notice on the right side of the page, how the column was cut off in the exact place the dotted lines (or margins) appeared in Design View? The portion of the report that fell to the right of the margin is going to print too, of course, but only a separate page.

This obviously is not an acceptable way to print this report.

There is an easy way to fix it, though, if page orientation is not an issue.

Go to the Page Setup tab. Below is an example of the options available.

Right now, the page is set to print in Portrait mode. We can tell because Access has highlighted the portrait button. To change the layout, simply select Landscape.

This is now what our page will look like when printed.

If page orientation is important, however, you may have to adjust the columns until they all fit on the page, or even choose to remove some of them. Every option involves compromise, of course, so it's really just a matter of finding a solution that you are satisfied with.

Page Setup

Earlier in this section, I introduced you to the dotted lines in Layout view and likened them to margins in Microsoft Word. That is exactly what they are. Now I'm going to tell you how you can change them.

Navigate to the Page Setup tab and click the Page Setup button. This launches a dialogue window.

Margins are defined in inches or millimeters, depending on your system settings. By default, Access 2013 applies a one-quarter inch margin (6.35mm as shown above) on all four sides, but you can use any margin that appeals to you. To do so, just click into a field and enter a new number. As you do so, the sample box on the right gives you a rough idea of what to expect. In the next illustration, I've selected a 1-inch margin (25mm) on all sides.

Click OK when finished. Layout view for this arrange will reflect our changes.

The Print Data Only checkbox allows to easily remove the borders and print only the information inside each cell.

Now, switching to the Page tab of the Page Setup dialogue allows us to select a paper source, the size of the paper, and even choose a printer. From here, we can also change the orientation of the page.

The Columns tab allows you to specify how many columns you'd like, the size of each column, and the amount of space between columns and rows.

Each of these options is also available on the ribbon in Print Preview.

Printing a Report

When you've finished previewing your report, and are satisfied with the results, it's time print it. You can do so by clicking the Print button on the left side of the ribbon in Print Preview.

This will launch the Print dialogue.

From here, you can select an available printer, specify a print range, and the number of copies. Click OK to send the report to the printer.

You can also access the print tools in Backstage View.

• Quick Print sends the file directly to the printer without allowing you to select any options or make any alterations to your report for easy reading.
• Print launches the Print dialogue window.
• Print Preview launches the Print Preview screen.

Exporting a Report

I don't know about you, but when I hear the words "exporting a report" I think of a complicated conversion process involving many steps. Nothing could be farther from the truth. Exporting is just another way of saying "saving as" a new file type. Access 2013 does all the work for you, all you have to do is know where to find the tools. For the most part, Access 2013 even makes that process easier by putting them right in the ribbon.

In the remainder of this section, we're going to show you where to find the tools and how to make sure the information you export is complete.

The first thing we're going to do is run our report. If you aren't already in Report view, click the View button and select it. Navigate to the External data tab. This is where most of the importing and exporting tools are located.

The Saved Exports button will be covered later in the section. For now, let's learn how to export into different formats.

Export to Excel

Use the Excel button to export your data to Microsoft Excel 95, 97, or 2003.

Use the File name box to manually enter a location on your computer in which to store the exported file. You may also use the Browse button to search for a location.

Use the File format drop-down menu to select a file type. You can save it as an Excel 5.0/95 workbook, or an Excel 97 – 2003 Workbook.

When you click OK, Access exports the file to the specified location, then asks you if you want to save the export.

Saved exports appear in Saved Exports manager.

Click Save export steps. You'll then be asked to enter in a name and description.

Press Save Export to save it.

Export a Report as a Text File

Use the Text File button to export your report as a text file. The Export screen opens, where you can enter a location for the file, and choose whether you want to preserve the formatting options. When you click OK, you will see a small window like this:

Here you will select an encoding option. If you are not sure, leave the default setting as Windows. Click OK when finished. You will then have the option of saving this export.

Click Save Export.

Exporting to XML

XML stands for Extensible Markup Language. It is a format for storing information. Many people confuse it with HTML, which is a format for displaying information.

To export your report in XML, use the XML file button.

You will be asked to enter a location in which to save the file. Click OK.

You may now select an option as to which information to export. You can choose to export only the data in each field, the data along with table and field formatting (XSD) or a presentation of your data, which is similar to an HTML file.

When you click OK, you will be asked if you'd like to save this export to the Saved Exports manager.

Export to PDF or XPS

Exporting to PDF is like printing your document, except that instead of a paper copy, you're getting a digital copy. XPS is a format similar to PDF, but created by Microsoft.

Select a report, then click the PDF or XPS button to get started. You will be asked to select a file location for the PDF.

When you click OK, Access asks you if you'd like to save the export to the Saved Export Manager.

More Options

By clicking the More button in the Export group on the ribbon, you access options to save the report as a Microsoft Word file, an HTML file, and others. Since the process to export to each of these files is nearly identical, we won't repeat it here.

Saved Exports

If you use the same export settings frequently, you may want to save those settings. The Saved Exports button is basically the saved export manager. As we discussed the export options, we saved some of the export settings. Right now, we're going to examine the Saved Export window.

Here we have the saved settings. One export is called "Export-Books". It has a short description--"Export data to Excel file" - and a location. We can edit any of this information by clicking on it and typing in something new.

To run a saved report, select it from the list and click Run.

To delete a report, select it and click Delete.