Report Builder Walkthrough: Percent of Rows Above or Below a Threshold

Use this technique to calculate what percentage of rows fall above or below a threshold column value

If you want to count rows based on a certain column value, such as "How often are we spending more than 30 minutes on scene?" in order to calculate a percentage of trips above and below the 30-minute threshold, don't use a column filter, because column filters remove rows entirely from the grid, and therefore make it impossible to calculate a percentage.

Instead, use one of the "countLessThanOrEqual", "countGreaterThan", "percentLessThanOrEqual", or "percentGreaterThan" aggregation functions.

Example Walkthrough

Here is how to quickly create a report showing the percentage of slow pickups (crew spent greater than 30 minutes on-scene) for each origin facility.

This example is available as a built-in report, ID number 400, named "Transports on Contract - Rate of 31+ Minutes On-Scene - Past 120".

Follow these steps to create it from scratch:

1. From the Data Hub, select the "Dispatches-Overview" and "Dispatches-Runtimes" datasets, and then click the "Launch New Report" button.
2. From the report's sidebar, in the "Columns" tab, deselect all columns by clicking the uppermost checkbox, the one next to the "Search" box.
3. Still in the sidebar, drag the column "Repose Name" down to the "Row Groups" box.
4. Still in the sidebar, drag the column "Minutes On-scene" down to the "Values" box, and then click it to change its aggregation function from SUM to "percentGreaterThan30".
5. Still in the sidebar, drag the column "Dispatch ID" down to the "Values" box, and then click it to change its aggregation function from SUM to COUNT. This will show you how many total trips you made to each origin.
6. Still in the sidebar, switch to the "Filters" tab, and add a filter for "Repose Is Contracted For Service" to "true". This will exclude all trips from places that are not known facilities, or that were not covered by a contract. 
7. Still in the sidebar, again in the "Filters" tab, add a filter for "Trip Is Outbound" equals true. This will exclude return trips where crews are often forced to wait for patients to come off dialysis machines.
8. Again in the "Filters" tab, add a filter for "Execution Status" not equal to "Delegated to affiliate". This will exclude trips that you delegated to affiliated providers, where you don't control the performance of the crews, and where you probably don't receive accurate leg-time records.
9. Again in the "Filters" tab, add a filter for "Transport Disposition" equal to "Transport by this EMS unit". This will exclude any dud visits.
10. From the grid, click the "count(Dispatch ID)" column twice, to sort your busiest facilities to the top.

The grid will then show what percent of the time your crews are getting stuck longer than 30 minutes on-scene at each facility.

If you then wish to graph the data, do this:

1. From the grid, click and drag the "count(Dispatch ID)" column to the right, so that it switches places with the "percentGreaterThan30(Minutes On-scene)" column.
2. From the grid, click and drag to select the top few dozen rows, to capture your busiest facilities. You only need to include the first two columns -- the "Repose Name" column and the "percentGreaterThan30(Minutes On-scene)" column.
3. Right-click the selection area, select "Chart Range", select "Bar", and then "Grouped".
4. You might need to enlarge the chart a bit, if the facility names don't have room to appear.

Special Note About Age Brackets

If you are looking to group or filter your receivables or invoices by their age bracket (0-30 days, 31-60 days, 61-90 days, etc.), you will find pre-calculated "Age Bracket" columns in the datasets for this purpose; you don't need to use an aggregation function.