Ad-Hoc Reporting: Summary Grouping
Purpose
To demonstrate the different ways that you can group data into a Summary Report in Ad-Hoc Reports. Summary Reports are a broad look at totals, averages, trends, or percentages in your agency's data. This data can then be used to create custom graphs or charts.
Related Articles
Video
Instructions
1. Row Groups
Click the plus icon to add a Row Group. Row Groups are the item you are going to use to group your data. You can select any Data Field from the Data Source Configuration tab. In this example we are going to group by Shift. Click the plus icon again to add a second group. Click the trashcan icon to remove any group.
You must select at least one Summary Column data item before your Row Group will display. Please see step 2. The data we summarized in this example is the total number of Incidents, grouped by Shift (from the Incident Reports Data Source).
If you choose only one Row Group your Report will have one line item or Group for every item in that Data Field. In this example we have one row per Shift - A, B, & C.
You can add a second Row Group. Click the plus icon and then select the second Data Field you want to use in the drop-down menu. This will create a secondary group nested in the first group. In this example we grouped by the Incident Types Series. Under each Primary Group you will have the option to expand or collapse the section to see the Secondary Group. The overall Totals appear at the bottom. In this example the primary section for C Shift is not expanded to show the difference.
2. Summary Columns
This section is where you will determine the Data that you want to summarize or calculate in your Group.
A - Measure - Choose the type of calculation to perform from the drop-down menu.
B - Column for Aggregation - Select which column of data the calculation will be performed on. Any field in your original Data Source will be available. Depending on what you select, certain calculations will not be available. (For example: you cannot calculate a sum or average for a text data field. You can count the number of items to get a numerical amount.)
C - Filter Conditions - You can filter your data to this specific Column. You previously had the opportunity to filter data under the Criteria tab for the whole report. This field will allow you to Filter out more data for this particular Summary Column. If you create additional Summary Columns, you can filter the data out differently for those additional columns. You can add more than one filter per Summary Column. The filtering function here works the same as the Criteria tab.
Example: You can summarize a count of calls for Summary Column 1 and filter to only show ALS call types. Then use the same count for Summary Column 2, but filter to only show BLS call types.
D - Name - Give this column a name to appear in the header. In our original example we named the Column "Calls per Shift" to explain what the number in the column represented.
E - Value Format - Select the formatting type that best coordinates with the type of summary you are performing from the Filter.
F - Decimals - Enter the number of decimal places you want to display in the result. Example: if you counted a total number of incidents, choose "0", because a count would use whole numbers only. If you calculated a dollar amount use "2". (Total number of incidents = 189 / Total cost for fuel = $237.45)
G - Alias - The value in this column is now given an Alias. This is used if you need to make any further calculations under the Computed Columns section.
3. Computed Columns
Computed Columns allow you to further calculate values based on the data in your Summary Columns.
A - Formula:
Use the placeholder drop-down B to select the Alias you want to enter as part of your formula. Or you can select a Column Total. You can also just type the name of the Alias into the Formula field. Continue adding additional numbers, symbols, or Column Aliases to complete your equation. Mathematical functions (symbols) can include: Addition (+), Subtraction (-), Divide (/), or Multiply (*).
Formula Examples:
col_1 / col_1_total * 100 (This will divide Column 1 amounts, by the Total of the Column to give you a fraction, then multiplying the fraction by 100 gives you a percentage of the Total.)
col_1 + col_2 (This will give you the sum of Column 1 plus Column 2.)
C - Name:
Give your new column a name that describes the calculation you created.
Examples: "Total Number of Supplies" or "Percent of Total Calls"
D - Value Format:
Choose the type of display you want your calculation to appear as.
Example: Percent will have a % sign - "45.75%", Currency will have a $ sign - "$275.89"
4. Add a Column Group
Adding a Column Group allows you to break your data out into more granular sections.
A - Select the Data Source Field that you want to group by.
B - Select the type of calculation you are going to use for that data.
C - Select the Data Source you are going to use to make that calculation.
Note: B and C can be the same Count type and Data Source as your original Summary Column.
Example: Our first example showed the number of calls per shift.
Adding a Column Group to break that out further and show the number of Calls per Shift per First Arriving Apparatus.
Our report would then expand to show all of the data broken out into Column Groups.
The original calculated Summary Column is still at the end of the Report with the totals.
5. Add Chart
Adding a chart displays a visual representation of your data.
A - Chart Type:
Bar - This will display a horizontal bar graph.
Column - This will display a vertical bar graph.
Donut - This will display a circular chart with no center.
Gauge - This will display a progress gauge based on an overall total goal.
Line - This will display data points connected by a line to show trends.
Pie - This will display a circle chart with data grouped as wedges.
B - Grouping:
Select the Data Source Field you originally choose in your Row Group.
C - Measure:
You will need to choose how you want this data measured or calculated for the chart. This can be the same Measure Type as in your Summary Column.
D - Column for Aggregation:
This is the column you are going to use for your calculation.
Example: We used the same format as our previous Row Group and Summary Column #1 to calculate Calls per Shift. We used the Shift as a Group for the Chart, and then used the total number of incidents for the calculation by counting the number of Incident Ids.
Note: Hovering over a wedge or bar on a chart will display the calculation as a number and/or percentage for that item.
Related Articles
Ad-Hoc Reporting: Tabular Grouping
Purpose To demonstrate how to group your data for a Tabular Report in Ad-Hoc Reporting in the Reports Module. Related Articles Report List Features Ad-Hoc Reporting: Report Type Ad-Hoc Reporting: Summary Grouping Ad-Hoc Reporting: Criteria Ad-Hoc ...Ad-Hoc Reporting: Columns
Purpose Statement The Columns configuration allows users to select and customize which data fields from their chosen Data Source will be displayed in a Tabular Ad-Hoc Report. This feature enables users to create focused, relevant reports by choosing ...Ad-Hoc Reporting: Summary Rows
Purpose This article will review creating Summary Rows in a Tabular report in Ad-Hoc Reports. Related Articles Report List Features Ad-Hoc Reporting: Report Type Ad-Hoc Reporting: Columns Ad-Hoc Reporting: Tabular Grouping Video Instructions 1. ...Ad-Hoc Reporting: Automatic Calculation Columns
Purpose Agencies often use standard calculations for time intervals on an incident. Instead of having to create a Custom Column to calculate these time intervals on every report that requires them, First Due created a series of these calculations as ...Ad-Hoc Reporting: Criteria
Purpose This article will review how to use the Criteria filtering options when creating an Ad-Hoc Report in the Reports Module. Choosing Criteria for your report allows you to focus on the relevant data in a specific section of your system. Related ...