Difference between revisions of "Creating Summary Reports"

Revision as of 08:40, 8 July 2009

Overview

All the reports that you have created so far have displayed each record from the table that matched the criteria.

In many instances, you will not wish to display individual records, but instead summarize the records by grouping by some value and perhaps displaying a sum of a numeric value, or a count of the records that match each group you have established.

1. Create a new report named Employees by Department based on the user table.

2. Add Users – Company Name and Users – Last name fields.

3. Set the Column Headings as shown below:

4. Build, save, and preview the report.

The report displays the department and the employee last name.

5. Close the report and display the Report Builder.

Using Group By

You use the Group By row to specify the type of totaling that you require for each column.

The options available in this combo box are as follows:

• Group By - Groups the selected records by that column. You will usually use this option in addition to one of the other options applied to other fields.
• Sum - Total of a numeric field.
• Min - The smallest value in a field.
• Max - The largest value in a field.
• Count - A count of the number of occurrences.
• Count Distinct - Count the number of distinct occurrences.
• Average - The average of a numeric field.

Using the Count Option

1. Set the following Totals options:

The records will be:

• Grouped by Department – each department will only be shown once in the report.
• Count of the Last Name – the count of employees in each department is displayed in the second column.

2. Build, save, and preview the report.

When you use Group By, the results are sorted automatically in ascending sequence.

Sorting Totals

If you wish to override the automated sorting by the Group By field, you can apply sorting options to other fields.

1. Modify the report to display the number of employees in descending order.

The report will look as follows.

Using Sum

Next you will copy the existing report and display the total salary for each department.

1. Copy the report Employees by Department to a new report Employees Total Salaries.

2. Modify the report as shown below:

• The Salary field has been added to the report and renamed from Users – Salary to just Salary.
• The Group By Sum function is used to sum the salary for each department.
• The report will sort descending from the department with the highest total salary.

3. Build, save, and preview the report.

Using Average

The average function will calculate the average for each group.

1. Modify the Salary field to the report and add the Average function.

2. Build, save, and preview the report.

You can use the same field multiple times. For example, if you wished to show the total salary AND the average salary in two columns.

Setting a Criteria in a Summary Report

You can also set criteria in this type of report, in the same way that you set criteria in a regular report.

1. Modify the report as shown below:

The report will now only display records where the average salary in the department is greater than or equal to 75000.

2. Build, save, and preview the report.

Only two records are displayed that match this criteria.