Difference between revisions of "Creating Summary Reports"
Julia Decker (talk | contribs) |
|||
| (11 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| + | {{SeqReportPageHeader}} | ||
==Overview== | ==Overview== | ||
All the reports that you have created so far have displayed each record from the table that matched the criteria. | 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 | + | 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|user]] table. | 1. Create a new report named '''Employees by Department''' based on the [[User|user]] table. | ||
| Line 29: | Line 30: | ||
The options available in this combo box are as follows: | The options available in this combo box are as follows: | ||
| + | '''Standard''' | ||
* '''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. | * '''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. | * '''Sum''' - Total of a numeric field. | ||
| Line 36: | Line 38: | ||
* '''Count Distinct''' - Count the number of distinct occurrences. | * '''Count Distinct''' - Count the number of distinct occurrences. | ||
* '''Average''' - The average of a numeric field. | * '''Average''' - The average of a numeric field. | ||
| + | * '''Standard Deviation''' - The standard deviation of a numeric field. | ||
| + | |||
| + | '''Total Group Options''' - ''See [[Using Total Group Options in Report Builder]] for details. | ||
| + | * '''Total Group By''' | ||
| + | * '''Total By Sum''' | ||
| + | * '''Total By Count''' | ||
| + | * '''Total By Average''' | ||
| + | * '''Total By Standard Deviation''' | ||
| + | * '''Display Total Label''' | ||
==Using the Count Option== | ==Using the Count Option== | ||
| Line 43: | Line 54: | ||
[[Image:Reps64.png]] | [[Image:Reps64.png]] | ||
| − | The records will be | + | 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. | 2. '''Build''', '''save''', and '''preview''' the report. | ||
[[Image:Reps65.png]] | [[Image:Reps65.png]] | ||
| − | + | ||
| + | When you use '''Group By''', the results are sorted automatically in '''ascending''' sequence. | ||
| + | |||
==Sorting Totals== | ==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'''. | 1. Modify the report to display the '''number of employees''' in '''descending order'''. | ||
| Line 60: | Line 76: | ||
[[Image:Reps67.png]] | [[Image:Reps67.png]] | ||
| − | + | ||
==Using Sum== | ==Using Sum== | ||
| Line 71: | Line 87: | ||
[[Image:Reps68.png]] | [[Image:Reps68.png]] | ||
| − | * The '''Salary''' field has been added to the report and renamed – Salary. | + | * 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 '''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. | * The report will sort descending from the department with the highest total salary. | ||
| Line 78: | Line 94: | ||
[[Image:Reps69.png]] | [[Image:Reps69.png]] | ||
| − | + | ||
==Using Average== | ==Using Average== | ||
| Line 109: | Line 125: | ||
Only two records are displayed that match this criteria. | Only two records are displayed that match this criteria. | ||
| − | [[ | + | ==See Also== |
| + | * [[Using Total Group Options in Report Builder]] | ||
| + | |||
| + | {{PrevNextStart}} [[Accessing the Current Date in a Report]] | ||
| + | {{PrevNextMid}} [[Sharing Reports with People - Overview]] | ||
| + | {{PrevNextEnd}} | ||
| + | |||
| + | |||
| + | [[Category:Reports]] | ||
Latest revision as of 12:40, 2 May 2014
Click here to watch a video on SmartSimple's integrated reporting subsystem.
Contents
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:
Standard
- 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.
- Standard Deviation - The standard deviation of a numeric field.
Total Group Options - See Using Total Group Options in Report Builder for details.
- Total Group By
- Total By Sum
- Total By Count
- Total By Average
- Total By Standard Deviation
- Display Total Label
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.
See Also
