Purpose Statement

Custom Columns in Ad-Hoc Reporting enable users to create calculated fields and combine data elements to generate new information not available in standard columns. This feature allows for mathematical operations, concatenations, and time calculations between existing data fields, providing flexibility to create specialized reports tailored to specific analytical needs and operational requirements.

Background Information

Custom Columns extend the capabilities of standard reporting by allowing users to perform calculations and data manipulations directly within their reports. This functionality is essential for creating complex analytical reports, custom time calculations, field combinations, and mathematical operations between data elements. Custom Columns are particularly valuable for agencies that need specialized calculations beyond the pre-built Automatic Calculation Columns, enabling advanced data analysis and custom performance metrics.

Required Permissions

Users must have the following permissions to create and manage Custom Columns:

• Ad-Hoc Reporting Access: Permission to access the reporting module and create reports
• Custom Column Creation: Ability to add and configure custom calculated fields
• Data Source Access: Read permissions for the Data Source being used
• Column Management: Permission to modify report columns and configurations

Video

Step By Step Guide

1. We got here by navigating from Reports Module > Ad-Hoc Reports > Create Report. Complete the tabs up to choosing a Report Type, then navigate to the Columns Tab.

Note: See the Ad-Hoc Reporting: Columns article for information on choosing the columns for your Report.

2. At the bottom of your column options, click on Add Custom Column.

3. The Add Custom Column modal will pop up and you can name your column.

4. Chose the type of Operation (Calculation) you want to perform from the drop-down menu.

5. Options for Operations:

1. Concatenation (displays the value of two data fields in one column)
   
2. Sum
3. Subtract
4. Multiply
5. Divide
6. Date Time Difference
7. Date Difference
8. Time Difference

​

​

6. Depending on the type of Operation you selected, you will now add fields or Columns to calculate from. Adding a Column allows you to choose any field from your Data Source Configuration.

7. Clicking Add Column again allows you to add a second Data Source field to calculate between. You can only calculate between two values.

In this example we are calculating a Total Call Time by using the Date Time Difference operation from the Enroute At time to the Clear At time.

Note: if your time calculation displays in a negative value in your column, reverse the order of your Columns by dragging Column 2 up to the Column 1 position.

8. If you choose to use the Date Time Difference Operation, you will need to select a Value Format from the drop-down menu. This is the format that you want your time calculation to be expressed in.

9. Value Formats for Date Time Difference Display as:

1. Time Interval - 00:00:00 (Hours:Minutes:Seconds)
2. Years, Days, Hours, Minutes - 000.0 (You choose the decimal places to show)
3. Seconds - 0000
4. Text - 0 Days 0 Hours 0 Minutes
   

10. When you have finished building out your Custom Column, click Add Column in the bottom right-hand corner to save and close.

11. Your new column will now appear in your report.

Best Practices

Custom Column Planning:

• Plan your calculations before creating columns to ensure accuracy
• Use descriptive names that clearly indicate the column's purpose
• Test calculations with known data to verify accuracy

Operation Selection:

• Choose the most appropriate operation type for your data and analytical needs
• Consider data types when selecting mathematical operations
• Use concatenation for combining text fields or creating custom identifiers

Time Calculation Best Practices:

• Verify field order to avoid negative time calculations
• Select appropriate value formats based on your analysis requirements
• Consider using pre-built Automatic Calculation Columns when available for common time intervals

Performance Considerations:

• Limit the number of custom columns to maintain report performance
• Avoid overly complex calculations that may slow report generation
• Test custom columns with large datasets before finalizing

Troubleshooting & FAQs

Common Issues:

Q: Why is my time calculation showing negative values? A: Negative values typically indicate incorrect field order. Reverse the order of your columns by dragging Column 2 to the Column 1 position to correct the calculation.

Q: Can I create custom columns that calculate between more than two fields? A: No, custom columns are limited to calculations between two values. For more complex calculations, consider creating multiple custom columns or using alternative approaches.

Q: Why can't I see certain fields when adding columns to my calculation? A: Field availability depends on your Data Source Configuration and user permissions. Ensure you have access to the required data fields and that they exist in your selected Data Source.

Q: How is "Current Date/Time" different from "Add Date/Time"? A: Current Date/Time uses the actual date and time when the report is generated (dynamic), while Add Date/Time allows you to set a fixed date and time that remains constant across all report runs.

Q: Should I use Custom Columns or Automatic Calculation Columns? A: Use Automatic Calculation Columns when available for standard time intervals, as they are pre-built and optimized. Use Custom Columns for specialized calculations not covered by the automatic options.

Q: Can I edit a custom column after it's created? A: Yes, you can typically edit custom columns by accessing the column configuration. However, changes may affect existing saved reports that use the custom column.