Report Designer Tutorial

Tutorial Overview

This tutorial walks you through the creation of a report using the Report Designer. Click here for a general description of the Report Designer features.

The tutorial creates a report from scratch. However, you will often want to start with a basic report and simply modify it. The process is exactly the same, except in this case the initial steps are already done.

Step 1 - Select a Data Source

The first step is selecting the data source (or database) from where the report data will come.

Select the File | New menu command and the Report Designer will automatically prompt you for a data source. The following Data Link Properties window will appear.

Click on the Provider tab and select Microsoft Jet 4.0 OLE DB Provider. Click Next >> to move to the Connection tab.

Enter the full path to the EZScale000.mdb database including the drive and directory. You can browse to locate the EZScale000.mdb file clicking the small button .

Click OK to save your selection.

2)      Select a Record Source

After selecting the data source, you need to select a record source. The data source contains many record source objects, including tables and queries. Before you can create the report, you need to select which of the record sources will be used.

To select the record source, select the View | Layout Window menu (or press F5) to invoke the Layout Window. Press the pin icon on the upper corner of the window so it stays on top of all other windows.

Open the drop-down list at the top of the Layout Window. You will see a list of all record sources available. For this tutorial, select "Products".

This is how the Layout Window should look after selecting a record source from the drop down list and dragging a couple of fields to the Details section. In this case, we selected the Tickets Query record source, then dragged the TicketID, DateTime, NetWt, and POReferenceID fields to the Details section.

When you selected the "Tickets Query" record source, the Fields list was automatically populated with all the fields available for the record source.

The status bar on the main Report Designer window also changed. Now it shows the name of the active record source, the data source, and the record count. For example:

Report [Tickets Query] from [EZScale000.mdb] records [250/698]

The record count shows two values. The first is the number of records available for previewing. The second is the total number of records on the record source. The number of records you see will depend on the number of records in your current data set. To improve speed, the Report Designer only loads the first 250 records from the data source while in preview mode. Later, when you actually print the report, all records will be loaded.

3)      Define the Report Layout

To define the report layout, drag fields from the Fields list to the Groups and Details lists with the mouse. You may move fields between any two lists, and you may also reorder the fields by dragging them higher or lower within the Groups and Details lists.

As you move the fields, the main window shows a report preview that is automatically updated. This makes it very easy to create the report and make it look exactly the way you want.

For this tutorial, we will create a simple report of TicketID's, DateTime, NetWt, and POReferenceID. If you have not already done so, drag the following fields from the Fields list to the Details list: TicketID, DateTime, NetWt, and POReferenceID.

Look at the main window and you will see a "live preview" of the report. If you drag the fields around in the Layout Window, the data is automatically sorted and the report is updated.

Now assume we would like to see the data grouped by POReferenceID. To do this, drag the POReferenceID field into the Groups list. The data is automatically grouped by supplier.

The report now looks like this. Of course, your report will depend on what data is in your data source.

4)      Set Field Properties

By default, the Report Designer sorts all fields in ascending order. It also provides default styles and column widths.

Let's change some of these defaults so our report shows the most recent tickets first. Double-click the TicketID field on the Layout Window (we'll explain why we're selecting the TicketID instead of the DateTime field in a moment). This shortcut will bring up the Settings Window with the TicketID field already selected. Alternatively, you could use the View | Global Settings menu; click the Fields tab, then select the TicketID field.

The Report Designer always sorts from left to right. To sort the report by DateTime, you need to prevent sorting by TicketID. To do this, set the TicketID Sorting field to (none).

You may also change the field title. "TicketID" is a good identifier in a database, but doesn't look good on a report. Change the Title field to "Ticket ID" instead...(we put a space between Ticket and ID)

 This is how the dialog should look:

Now we can set the sort order for the DateTime field. Select the DateTime field and change the Sort Order to Descending. Since we are only displaying the date and not the time, change the field Title to "Date". You could also change the Format if you wanted to display the date and the time or some other variation.

Click OK to dismiss the dialog and the changes will be applied to the report. This is how it should look at this point. Notice how the products are grouped by POReferenceID and the Date column is sorted in descending order.:

 5)      Create Aggregate Statistics

Creating aggregate statistics with the Report Designer is easy. For this tutorial, we will calculate the Total weight for the purchase order.

To add the aggregate, double-click the NetWt field on the Layout Window and set the Total As field to Sum. While we're at it, let's modify the format to show a thousands separator comma and drop decimal portion of the weights. Change the Format field to ###,###. Click OK and the Sum of the NetWts will be added to the report and formatted with commas and no decimals.

You may want to label POReferenceID and the aggregate so it is clear that the aggregate is a total and not an average. To do this, double-click the POReferenceID field on the Layout Window and set the Title to

"PO %s. Total Wt:"

Group fields are a little different from Detail fields. You may include the field value in the title by embedding a "%s" in the title.

After changing the titles, you may want to adjust the column widths. To do this, just drag the divisions in the header row on the preview area.

The picture below shows the current state of the report and the dividers you need to drag to resize the columns:

5)      Set Styles

Our report is almost ready. To finish it, we will customize the styles to change fonts, colors, and borders.

To do this, double-click one of the headings in the main window preview area. This will bring up the Settings dialog with the Heading 1 Top style already selected. Change the font to 14-point Tahoma (or another font if your system does not have Tahoma installed), black and bold. Also change the top border to (none).

Next, select the Detail Title style from the list on the left. Change the font to gray, not bold. Click OK to dismiss the dialog and apply your changes.

6)      Set Global Report Properties

The last step in the report creation process is setting the global parameters that apply to the entire report.

The most important parameters are the report title, headers and footers. You may also want to set the page margins and orientation.

Bring up the Settings window using the VIEW|GLOBAL SETTINGS menu (or press F4). Select the Text tab to define the report title.

In the Report Title field, type the following:

Total Weight by Purchase Order
Generated on <dd>

Use the ENTER key to create the line break. The <dd> tag is a special code that gets replaced with the current date when the report is generated. There are several such codes, but you don't have to memorize them. To insert a code into the text you are typing, click the SPECIAL button at the bottom of the pane and select from the popup menu.

To define the header text, select "header" from the list on the left of the dialog. The header is composed of three parts: one is aligned to the left, one to the center, and one to the right. By default, the Report Designer will print the data source name on the left, the record source on the center, and a "Page <p> of <n>" string on the right. Change the left part of the header to "Tickets by Purchase Order" and clear the center part.

The report is done. To see how it looks, click the Preview tab on the main window.

 7)       Save and Print the Report

Now that you have defined a report, save it using the File | Save menu or pressing Ctrl-S. This will create a report definition file with the default extension of VRD.

The VRD file contains all the information needed to generate the report, except the data, which comes from the data source.

To print the report, use the File | Print menu. This will generate the entire report with fresh data and send it to the printer.

Whenever you generate new tickets or add purchase orders, you can create a new report by loading the VRD file and printing it again.

Note:      When you print from the designer, all the records in the query will be included in your report. To print the report with only a subset of the available records, you generate the report using the Run Report option under EZScale's Task menu.