4,401
edits
Changes
no edit summary
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 and display a total of some numeric value, or perhaps a count of the records.
1. Create a new report named '''Employees by Department''' based on the [[User|user]] table.
2. Add '''Users – Company Name and Users – Last name''' fields.
3. Set the '''Column Headings''' as shown below:
[[Image:Reps61.png]]
4. '''Build''', '''save''', and '''preview''' the report.
The report displays the department and the '''employee last name'''.
[[Image:Reps62.png]]
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.
[[Image:Reps63.png]]
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:
[[Image:Reps64.png]]
The records will be grouped by department, with the count of employees in each department displayed in the second column.
2. '''Build''', '''save''', and '''preview''' the report.
[[Image:Reps65.png]]
=Sorting Totals=
You can also apply sorting options to the report.
1. Modify the report to display the '''number of employees''' in '''descending order'''.
[[Image:Reps66.png]]
The report will look as follows.
[[Image:Reps67.png]]
=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:
[[Image:Reps68.png]]
* The '''Salary''' field has been added to the report and renamed – 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.
[[Image:Reps69.png]]
=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.
[[Image:Reps70.png]]
2. '''Build''', '''save''', and '''preview''' the report.
[[Image:Reps71.png]]
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.
[[Category:Reports]][[Category:System Management]][[Category:Applications]]
In many instances, you will not wish to display individual records, but instead summarize the records and display a total of some numeric value, or perhaps a count of the records.
1. Create a new report named '''Employees by Department''' based on the [[User|user]] table.
2. Add '''Users – Company Name and Users – Last name''' fields.
3. Set the '''Column Headings''' as shown below:
[[Image:Reps61.png]]
4. '''Build''', '''save''', and '''preview''' the report.
The report displays the department and the '''employee last name'''.
[[Image:Reps62.png]]
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.
[[Image:Reps63.png]]
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:
[[Image:Reps64.png]]
The records will be grouped by department, with the count of employees in each department displayed in the second column.
2. '''Build''', '''save''', and '''preview''' the report.
[[Image:Reps65.png]]
=Sorting Totals=
You can also apply sorting options to the report.
1. Modify the report to display the '''number of employees''' in '''descending order'''.
[[Image:Reps66.png]]
The report will look as follows.
[[Image:Reps67.png]]
=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:
[[Image:Reps68.png]]
* The '''Salary''' field has been added to the report and renamed – 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.
[[Image:Reps69.png]]
=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.
[[Image:Reps70.png]]
2. '''Build''', '''save''', and '''preview''' the report.
[[Image:Reps71.png]]
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.
[[Category:Reports]][[Category:System Management]][[Category:Applications]]