Creating a Detail/Summary Report

A Detail/Summary report allows you to see both detail and summary records. A detail record contains copies of the fields from the database you queried. A summary record collapses or combines two or more detail records into a single record. For example, suppose you want to generate a sales report that shows each individual sale record and also shows the total sales by state. To do this, you would select one field in your report form to be a group field and another field to be a summary field. A group field is a field whose values remain constant across a set of records. A summary field summaries the records in each group. How you do this is explained in this section.

Continuing the above example, if each record in your file represents a sale, you would select the field that holds the state the sale was made in to group your records by. That is, the records would be grouped together by state. A group only makes sense if you also select at least one other field to summarize each group with. In this example, you would select a numeric field that holds the amount of the sale. For a summary statistic, you might select the "Sum" statistic to total the sales by state for the year. However, you could choose an "Average" to show the average sale size by state or choose a "Count" to show the number of sales made in each state. The statistics available to you include:

Count:	Number of records in the group.
Sum:	Total of all values in the group
Average:	Mean or average value of the group.
Lowest:	Single lowest value in the group.
Highest:	Single highest value in the group.
Std. Deviation:	Standard deviation in the group.
Variance:	Variance (or degree of variation) in the group.

While you can select any field to count, the other statistics require you to select a numeric field. Below is a step-by-step example.

This example below assumes that you are querying a file of sales records. From this file, you selected the two output table fields of Year to date sales and State. In this example, you will create a Detail/Summary report that groups by state and summarizes sales for each state.

STEP 1: Go to report setup

Run a query that has State and Year to date purchases selected as output table fields. This will take you to the Report Setup dialog.

Press the button to access the Report Designer.

STEP 2: Open Report Designer

After pressing the button, you will be shown your report form as it exists in the Report Designer dialog.

Pull down the Report menu by pressing Alt+R or by clicking on the Report pull-down menu pad. Next, select the menu option reading Data Grouping...

STEP 3: Data Grouping Dialog

Before you can enter a group field, you must first mouse click in the empty text box at the top of the Group Expressions list. Once the cursor is sitting in this text box, type in the name of the group field, which in this case is STATE

Click the OK button to return to the Report Designer.

STEP 4: Return to report form

The Report Designer will now show your report form with two new horizontal bands. Near the top of this report form directly under the Page Header band will appear the first new band labeled:

Group Header 1: STATE.

Additionally, directly after the Detail band will appear the second new band labeled:

Group Footer 1: STATE

Now that you have selected a field to group records by, you must insert a summary field in your form. This summary field will display a statistic such as a total or average for the records in each group.

STEP 5: Make space for sum

Before you can insert a summary field, you must first open up a space in the report form to hold it. To make this space, click on the band:

Group Footer 1: STATE

Then, while holding the left mouse button down, drag the mouse downward to create the space.

STEP 6: Insert sum field

In this new space, insert the name of the field to be summarized. The best way to do this is to copy this field from the detail band in your report form. First select the 'Year to date purchases' field named ytdpurch which appears directly above the Detail band. Copy it to the clipboard by pressing Control+C. Next, paste in a copy of this field by pressing Control+V. Click and drag this pasted field to position in the newly opened space above the band:

Group Footer 1: STATE

STEP 7: Open Expression

Once this summary field is in place, you must indicate which type of summarization should be performed. To do this, first double click on the Ytdpurch field you just copied to the Group Footer band. This will open the Report Expression dialog shown to the right.

Now click on

STEP 8: Open Calculate Dialog

After clicking , you will see the Subtotal or Calculate Field dialog.

Click on the type of summarization you want, which in this case is .

Now click OK to exit. This will take you back to the Report Expression dialog. Click OK to exit this dialog and return to the Report Designer.

To preview your report while still in this designer, pull down the report menu (Alt+R) and select the Preview option.

When done, you can exit and save by pressing the Control+W keys. This will return you to OT2 Query Maker's Report Setup dialog.

To make your report clearer, insert a description next to your summary field. To do this, first make sure the Report Controls Toolbar is visible. If it isn't, pull down the View menu (Alt+V) and click on the Report Controls Toolbar option. Next, click on the Text tool button . Click in the blank region to the left side of this summary field and type in a description such as Subtotal: When done, click on the selection pointer button, then press Control+W to exit and save

STEP 9: View report

After exiting the Report Designer, you will return to the Report Setup Dialog shown in STEP 1. To preview your report before printing, pick the output direction of Screen and click .

You will see something as shown to the right. To print your report, change the output direction to Printer and click Start report again.

OurTown2000 Query Handbook - Creating Reports

Creating a Detail/Summary Report

OurTown2000 Query Handbook - Creating Reports