Purpose Statement
Custom Columns in Ad-Hoc Reporting allow users to create calculated fields by combining, comparing, or manipulating existing report data.
This feature supports mathematical calculations, text combinations, date and time differences, and constant values. Custom Columns are useful when a report requires specialized values that are not available as standard columns or Automatic Calculation Columns.
Background Information
Custom Columns expand Ad-Hoc Reporting by allowing users to create new report fields based on existing data. They can be used for calculations, custom time intervals, field combinations, static values, and other report-specific needs.
Custom Columns are especially helpful when agencies need specialized metrics beyond the pre-built Automatic Calculation Columns. They can also be used in Criteria after they are created, allowing users to filter report results based on calculated values.
Custom Columns support 9 operations in the Add Custom Column modal. These include text, math, date/time, and static value options.
Required Permissions
Users may need the following permissions to create and use Custom Columns:
- Reports Module access
- Ad-Hoc Reports access
- Permission to create or edit Ad-Hoc Reports
- Permission to modify report columns
- Read access to the selected Data Source
- Access to the underlying fields used in the Custom Column
Permission names may vary based on agency configuration. If fields or Custom Column options are not available, contact a system administrator to verify the user’s Reports permissions, Ad-Hoc Reporting permissions, Data Set Permissions, and Data Source access.
Video
Operations Overview
The Add Custom Column modal provides 9 operations.
| # | Operation | Category | Description |
|---|---|---|---|
| 1 | Concatenation | Text | Combines values from two or more fields into one column |
| 2 | Sum | Math | Adds two numeric values together |
| 3 | Subtract | Math | Subtracts one numeric value from another |
| 4 | Multiply | Math | Multiplies two numeric values together |
| 5 | Divide | Math | Divides one numeric value by another |
| 6 | Date Time Difference | Time | Calculates the difference between two date/time values |
| 7 | Date Difference | Time | Calculates the difference between two date values |
| 8 | Time Difference | Time | Calculates the difference between two time values |
| 9 | Constant Value | Static | Adds a fixed value to every row in the report |
Step-by-Step Guide
-
Navigate to the Columns Tab
Navigate to Reports > Ad-Hoc Reports > Create Report.
Complete the required tabs until you reach the Columns tab.
Custom Columns are created from the Columns tab. See the Ad-Hoc Reporting: Columns article for more information about selecting standard report columns.
-
Open the Add Custom Column Modal
At the bottom of the column options, click Add Custom Column.
The Add Custom Column modal will open.
-
Name the Custom Column
Enter a clear, descriptive name in the Name your column field.
Use a name that explains what the column calculates or displays, such as Turnout Time Minutes, Full Name, or Benchmark Variance.
-
Select an Operation
Use the Operation dropdown to select the type of Custom Column to create.
The selected operation determines which fields, values, or formatting options are available.
-
Add the First Column or Value
Click Add Column to select the first field from the Data Source.
For some operations, you may also be able to add a static value or current date/time reference.
-
Add the Second Column or Value
Click Add Column again to select the second field or value.
Most Custom Column calculations compare or calculate between two values.
Column Options depend on Operation Selected.
-
Select a Value Format, If Applicable
For date and time-based operations, select the appropriate Value Format.
Common formats may include:
- Time Interval
- Years
- Days
- Hours
- Minutes
- Seconds
- Text
-
Review the Column Order
Confirm the fields are in the correct order.
For subtraction and time difference calculations, order matters. If a time calculation returns a negative value, reverse the order of the fields.
-
Save the Custom Column
Click Add Column in the lower-right corner to save the Custom Column and close the modal.
Detailed Operation Guide
Concatenation
Concatenation combines text values from two or more fields into one Custom Column.
Examples include:
| Column Name | Column 1 | Column 2 | Example Output |
|---|---|---|---|
| Full Name | Last Name | First Name | Smith, John |
| Full Address | Street Address | City | 123 Main St Springfield |
| Unit and Station | Unit ID | Station Name | E1 Station 5 |
| Incident Label | Incident Number | Incident Type | 2024-001234 Structure Fire |
Common uses include combining names, addresses, unit identifiers, or report labels.
Sum
Sum adds two numeric values together.
Examples include:
| Column Name | Column 1 | Column 2 | Example Output |
|---|---|---|---|
| Total Personnel | Firefighter Count | Officer Count | 8 |
| Combined Mileage | Mileage Out | Mileage Return | 24.6 |
| Total Hours | Regular Hours | Overtime Hours | 52.5 |
Common uses include calculating totals for staffing, mileage, hours, equipment, or quantities.
Subtract
Subtract subtracts the second numeric value from the first.
Examples include:
| Column Name | Column 1 | Column 2 | Example Output |
|---|---|---|---|
| Net Mileage | Ending Odometer | Starting Odometer | 12.3 |
| Remaining Inventory | Starting Stock | Used Quantity | 45 |
| Overtime Hours | Total Hours | Regular Hours | 12.5 |
Order matters. The system subtracts Column 2 from Column 1.
Multiply
Multiply multiplies two numeric values together.
Examples include:
| Column Name | Column 1 | Column 2 | Example Output |
|---|---|---|---|
| Total Pay | Hours Worked | Hourly Rate | 1,250.00 |
| Overtime Pay | Overtime Hours | Overtime Rate | 562.50 |
| Extended Cost | Unit Price | Quantity | 450.00 |
Common uses include payroll calculations, cost calculations, quantity-based totals, and weighted scoring.
Divide
Divide divides the first numeric value by the second.
Examples include:
| Column Name | Column 1 | Column 2 | Example Output |
|---|---|---|---|
| Average Per Unit | Total Calls | Unit Count | 45.2 |
| Cost Per Hour | Total Cost | Hours Worked | 32.50 |
| Calls Per Day | Monthly Calls | Days in Month | 8.3 |
| Compliance Rate | Compliant Count | Total Count | 0.92 |
Division by zero may return a blank or null value.
Date Time Difference
Date Time Difference calculates the difference between two full timestamp values that include both date and time.
This is commonly used for incident and EMS time interval calculations.
Examples include:
| Column Name | Column 1 | Column 2 | Format | Example Output |
|---|---|---|---|---|
| Turnout Time | Dispatch Date/Time | Enroute Date/Time | Minutes | 1.50 |
| Travel Time | Enroute Date/Time | Arrive Date/Time | Minutes | 4.25 |
| Response Time | Dispatch Date/Time | Arrive Date/Time | Time Interval | 00:05:45 |
| On-Scene Time | Arrive Date/Time | Clear Date/Time | Minutes | 32.00 |
| Transport Time | Depart Scene Date/Time | Arrive Hospital Date/Time | Minutes | 12.75 |
| Time Since Last Service | Last Service Date/Time | Current Date/Time | Days | 127.3 |
Common uses include turnout time, travel time, response time, on-scene time, transport time, and equipment maintenance intervals.
Date Difference
Date Difference calculates the difference between two date-only values.
Examples include:
| Column Name | Column 1 | Column 2 | Format | Example Output |
|---|---|---|---|---|
| Days Since Inspection | Last Inspection Date | Current Date | Days | 245 |
| Certificate Age | Issue Date | Expiration Date | Days | 365 |
| Days Overdue | Due Date | Current Date | Days | 15 |
| Employee Tenure | Hire Date | Current Date | Years | 5.3 |
| Equipment Age | Purchase Date | Current Date | Years | 3.7 |
Common uses include inspection scheduling, certification tracking, overdue calculations, asset age, and employee tenure.
Time Difference
Time Difference calculates the difference between two time-only values.
Examples include:
| Column Name | Column 1 | Column 2 | Format | Example Output |
|---|---|---|---|---|
| Shift Duration | Shift Start Time | Shift End Time | Hours | 12.0 |
| Break Length | Break Start | Break End | Minutes | 30.0 |
| Time Until Shift | Current Time | Shift Start Time | Time Interval | 02:30:00 |
| Overtime Past Shift | Shift End Time | Actual End Time | Minutes | 45.0 |
Common uses include shift calculations, break tracking, and schedule comparisons.
Constant Value
Constant Value adds a fixed value to every row in the report.
The value does not change based on the data in the record.
Examples include:
| Column Name | Constant Value | Use Case |
|---|---|---|
| Agency Name | Springfield Fire Department | Adds agency identifier to every row |
| Report Year | 2025 | Tags all records with a reporting year |
| Pay Rate | 35.00 | Adds a standard hourly rate |
| Benchmark Minutes | 4.00 | Adds a target benchmark for comparison |
| Region | North District | Labels records by region |
| Export Tag | NFIRS | Adds an export identifier |
Common uses include export tagging, agency labels, benchmarks, payroll rates, compliance thresholds, and third-party system imports.
Value Format Options for Time Calculations
Date and time-based operations require a value format.
| Format | Output Example | Description |
|---|---|---|
| Time Interval | 01:23:45 | Displays the result in HH:MM:SS format |
| Years | 2.5 | Displays the result as numeric years |
| Days | 45.7 | Displays the result as numeric days |
| Hours | 1.39 | Displays the result as numeric hours |
| Minutes | 83.75 | Displays the result as numeric minutes |
| Seconds | 5025 | Displays the result as numeric seconds |
| Text | 0 Days 1 Hours 23 Minutes | Displays the result as descriptive text |
When a numeric format such as Years, Days, Hours, or Minutes is selected, a Decimal field may appear so users can choose how many decimal places to display.
Use Time Interval when the report is intended for easy reading. Use Seconds when the value will be exported, filtered, sorted, or used in additional calculations.
Current Date/Time Reference
For Date Time Difference, Date Difference, and Time Difference operations, users may be able to select Current Date/Time, Current Date, or Current Time.
This creates a dynamic reference that uses the current date and time when the report is generated.
Examples include:
| Column Name | Column 1 | Column 2 | Format | Purpose |
|---|---|---|---|---|
| Days Since Last Service | Last Service Date | Current Date | Days | Equipment maintenance tracking |
| Time Since Dispatch | Dispatch Date/Time | Current Date/Time | Hours | Active incident duration |
| Days Until Expiration | Current Date | Expiration Date | Days | Certification countdown |
| Equipment Age | Purchase Date | Current Date | Years | Asset lifecycle tracking |
| Days Since Last Inspection | Last Inspection Date | Current Date | Days | Inspection compliance |
Current Date/Time is dynamic and updates each time the report runs. A manually entered date or time remains fixed.
Using Custom Columns in Criteria
After a Custom Column is created, it can be used in the Criteria tab to filter report results.
Examples include:
| Custom Column | Filter Operator | Value | Purpose |
|---|---|---|---|
| Response Time Minutes | Less Than | 5.00 | Show calls meeting a 5-minute benchmark |
| Days Since Inspection | Greater Than | 365 | Find overdue inspections |
| Total Hours | Greater Than | 40 | Identify overtime records |
| Equipment Age Years | Greater Than | 10 | Find older equipment |
| Turnout Time Seconds | Less Than or Equal | 80 | Review turnout compliance |
This is useful when users want the report to include only records that meet a calculated condition.
Common Fire/EMS Time Calculation Examples
| Metric | Operation | Column 1 | Column 2 |
|---|---|---|---|
| Turnout Time | Date Time Difference | Dispatch Date/Time | Enroute Date/Time |
| Travel Time | Date Time Difference | Enroute Date/Time | Arrive Date/Time |
| Total Response Time | Date Time Difference | Dispatch Date/Time | Arrive Date/Time |
| On-Scene Time | Date Time Difference | Arrive Date/Time | Clear Date/Time |
| Total Call Duration | Date Time Difference | Dispatch Date/Time | Clear Date/Time |
| Transport Time | Date Time Difference | Depart Scene Date/Time | Arrive Hospital Date/Time |
| Hospital Turnaround | Date Time Difference | Arrive Hospital Date/Time | Clear Date/Time |
| Alarm Handling Time | Date Time Difference | PSAP Call Date/Time | Dispatch Date/Time |
Tip:
Many common time calculations may also be available as pre-built Automatic Calculation Columns. Use Automatic Calculation Columns when available. Use Custom Columns when a specialized calculation, value format, or custom comparison is needed.
Best Practices
Custom Column Planning
- Plan calculations before creating the Custom Column.
- Use descriptive names that explain what the column calculates.
- Test calculations with known records to confirm the results are correct.
- Document complex Custom Columns for other report editors.
Operation Selection
- Use Concatenation to combine text fields.
- Use Sum, Subtract, Multiply, or Divide for numeric calculations.
- Use Date Time Difference for timestamp-based interval calculations.
- Use Constant Value for fixed labels, rates, benchmarks, or export values.
Time Calculation Best Practices
- Place the earlier timestamp first and the later timestamp second.
- If a time calculation returns a negative value, reverse the column order.
- Use Minutes or Seconds for benchmarking and compliance analysis.
- Use Time Interval for display-friendly reports.
- Use Current Date/Time for “time since” or “days until” calculations.
- Use Automatic Calculation Columns when available for standard response intervals.
Performance Considerations
- Limit the number of Custom Columns when possible.
- Avoid unnecessary chains of calculations.
- Test reports with smaller date ranges before running them against large datasets.
- Use date/time calculations carefully on very large reports because they may increase processing time.
Export and Scheduling Considerations
- Custom Columns are retained in saved reports.
- Custom Column values can appear in scheduled report deliveries and exports.
- Constant Value columns are useful for exports consumed by payroll, finance, CAD, or third-party systems.
- Custom Columns using Current Date/Time recalculate each time the report is generated.
Troubleshooting & FAQs
Why is my time calculation showing a negative value?
The fields may be in the wrong order. Time calculations subtract the second value from the first. Reverse the order so the earlier timestamp comes before the later timestamp.
Can I create a Custom Column using more than two fields?
Most calculation operations are limited to two values. For more complex calculations, create multiple Custom Columns and build the calculation in steps.
Why can’t I see certain fields when adding columns to my calculation?
Field availability depends on the Data Source Configuration and the user’s permissions. Confirm the correct Data Source is selected and that the user has access to the needed fields.
How is Current Date/Time different from adding a static date or time?
Current Date/Time updates each time the report runs. A static date or time remains the same every time the report is generated.
Should I use Custom Columns or Automatic Calculation Columns?
Use Automatic Calculation Columns when they are available for standard time intervals. Use Custom Columns when you need a specialized calculation, a specific value format, or a calculation that is not already provided.
Can I edit a Custom Column after it is created?
Yes. You can edit Custom Columns from the column configuration area in the Columns tab.
Can I use a Custom Column as a filter?
Yes. After the Custom Column is created, it becomes available in the Criteria tab and can be used to filter report results.
What is Constant Value used for?
Constant Value adds the same fixed value to every row. It is useful for agency names, reporting years, benchmark values, pay rates, export tags, or third-party import requirements.
Why does my PSAP Call Date/Time Custom Column show blank values?
If a Custom Column using PSAP Call Date/Time is placed as the first column in the report, values may not display. Move the Custom Column to a different position to resolve the issue.
What happens if I divide by zero?
Division by zero may return a blank or null value for that row. The report should still generate.
Do Custom Columns work with scheduled report deliveries?
Yes. Custom Columns are saved with the report configuration and calculate when the scheduled report runs. Columns using Current Date/Time recalculate dynamically on each delivery.