How to..Build a YTD Monthly Incident Rate Report (using Non-Standard Sections) – Support

Learn how to build a rolling incident rate report that will provide monthly values for TRIR (Total Recordable Incident Rate), LTIR (Lost Time Incident Rate), RTIR (Restricted Time Incident Rate) and a count of total incidents each month. This report will help you more effectively monitor, analyze, and improve workplace safety by leveraging eCompliance's form-building capabilities and Safety Intelligence's powerful data analysis tools.

*For more information about the Standard Incident sections in eCompliance

Pre-Requisites
- Pre-Requisites Video: Coming Soon!
Step-by-Step Report Guide
Additional Tips
Skills Learned

Pre-Requisites

Before you start building your Rolling Monthly Incident Rate Report, you'll need to ensure you have all of the following elements in your Incident Form Template:

Employee Hours

Make sure that you have input a sufficient amount of Employee Hours data into the Employee Hours section of eCompliance* to cover at least 1-2 months or at best an entire reporting period, which in this case is 12 months.

*For more information on How to Add/Edit Employee Hours

Incident Details

Your Incident Form Template should include the following fields:

Incident Date - Consider which date field you would like to use for the Incident Date. This field is important as this is the field that will determine which month your Incident falls into. The suggested field is the 'Date' field in the header of the Incident form.* The image below illustrates where you can find the header 'Date' field, as well as a demonstration of how a user can alter the date to the incident date.

*See the Optimized Date Fields suggestion in the Additional Tips below for more information.

Incident Injury/Illness Classification - To ensure effective querying and reporting in Safety Intelligence (SI), it's crucial to properly set up your incident form templates. Here's how to structure your incident injury/illness classification questions to capture the necessary data:

Recordable Incident

Include a question that explicitly determines the form as a recordable incident. For example:

"Is this a recordable incident?" with a Yes/No answer option.'

This allows you to filter and query in SI to get a count of recordable incident by filtering for 'Yes' answers on that question.

Lost Time and Restricted Time Incidents

Option 1: Separate Questions

Having separate questions makes it straightforward to filter each classification individually in SI. Questions for Lost Time and Restricted Time incidents can be as simple as the following examples:

"Is this a Lost Time incident?" with Yes/No options
"Is this a Restricted Time incident?" with Yes/No options

Option 2: Combined Classification Question

Use a single question like "Injury Classification?" with multiple choice answers:

No Treatment
First Aid
Medical Aid/Treatment
Restricted/Modified Duty
Lost Time
Fatality

To query specific incidents that fall under categories like 'Lost Time' or 'Restricted Time' incidents in Safety Intelligence - filter for the corresponding answer options.

Verify Key Metrics

TRIR Calculation
(Total number of recordable incidents x 200,000) / Total hours worked

LTIR Calculation
(Total number of Lost Time incidents x 200,000) / Total hours worked

RTIR Calculation
(Total number of Restricted Time incidents x 200,000) / Total hours worked

Pre-Requisites Video: Coming Soon!

We are currently finalizing a video that will show you how to optimize your incident form templates for more effective reporting. This video will illustrate the guidance provided in the Pre-Requisites section.

Step-by-Step Report Guide

Tutorial Video: Coming Soon!

We're currently finalizing a tutorial video that will walk you through the step-by-step process below. The video will be available shortly, so keep an eye on this guide for the update!

Data Preparation

Before creating your report, ensure that your data is ready by verifying your incident form template in eCompliance:

i. Check Employee Hours Data - Confirm that you've entered employee hours for at least the past few months in eCompliance. The more historical data you have, the more accurate and informative your report will be.

ii. Incident Form Fields - Ensure your form includes the following required fields:

Incident Date - this will allow the data to be grouped by month
Recordable Incident Classification
Lost Time Incident and Restricted Time Incident Classifications

Report Creation in Safety Intelligence

Now that your data is prepared, follow these steps to create the report in Safety Intelligence:

i. Navigation to Report Creation

Log into eCompliance and navigate to the Safety Intelligence module.
Choose whether to create this report in your Personal (named) Folder or your Organization's Folder
Select Create* and select Dashboard.

*If you are not yet familiar with creating new dashboard in Safety Intelligence, please refer to Lesson 1 in our Safety Intelligence Self-Guided Onboarding Series.

ii. Adding the First Tile

Once your dashboard is created, enter Edit mode and click ⊕ Add in the blue edit bar menu at the top left.
Choose Visualization to open the Explore menu.
Select the Incident Rates explore to retrieve your monthly Employee Hours data:

iii. Selecting Data Sources

In the Explore, select the following fields:
- Incident Date - Month Name
- Measure - Hours Entered

- Add a filter to the Incident Date - Date field by clicking the upside-down triangle next to the field name. Choose matches (advanced) in the dropdown menu of the filter and set it to "this year to day". This will make your report dynamic, always showing year-to-date (YTD) data.
- Click Run and the data pane will display the total hours worked per month up to the current month:

iv. Save the Report

After viewing the YTD Employee Hours, select Save on both the explore and the dashboard. You don't need to configure the visualization at this point; leave the tile as-is for now.

At this stage, you should now be out of Edit mode and your dashboard should be saved with the first tile displaying your YTD Employee Hours. This screenshot is a sample shot of the stage where you should be at now:

Next, we will configure a second tile and merge it with the first one.

v. Create a Merge Query

Without leaving the dashboard, click the ellipsis (three dots) in the top-right corner of the Employee Hours tile you created. Select Explore from here. This will open the explore in a new view, allowing you to merge with the second explore to retrieve your incident data:

In the explore view, click the cog icon in the top-right corner and select Merge results. This will open the explore selection menu again. To review how to navigate this menu, refer to the earlier section of the guide here.

This time, you will select the Forms, Question and Answers, and Action Items explore to pull your incident data.

vi. Configure your Second Tile

Set up filters - In the explore, select the Form Date - Date filter and the Form Title filters.
- Configure the filters as follows:
  - Form Date - Set the Form Date to matches (advanced) and set it to "this year to day".
  - Form Title - Set the Form Title filter and ensure you select the name(s) of all relevant incident forms:

Merge on a common field - Select the Form Date - Month Name field from the field picker. This will be the common field used to merge your two explores, just as you did in the Employee Hours explore:

vii. Create filtered measures for Recordable, Lost Time, and Restricted Time Incidents

Create filtered measures to pull different views of the data. You will create three filtered measures for: Recordable Incidents, Lost Time Incidents, and Restricted Time Incidents.

Filtered measures allow you to focus on a subset of data within your incident form.

Create the First Filtered Measure (Recordable Incidents) - In the field picker find the Count Forms measure. Instead of selecting it click the ellipsis menu to the right of the measure and select Create filtered measure:

Configure the Custom Measure - In the Edit custom measure menu, configure the following:
- Name - Change the default name to something meaningful (e.g. recordable_incidents)
- Filters - Apply filters based on the Question Name and Answer fields to count only the recordable incidents.

The filters work just like the tile-level filters you initially set in the explore. All of the fields that are available in the explore will be available for you to filter on.

In the case of the recordable_incidents measure, you will find the Question Name field and the Answer field as you will only want to get the count of recordable incidents. To add additional filters, click the + next to the Filter value - this measure will be used to get the monthly TRIR.

Once the measure is configured, click Save - the measure will now automatically aggregate for each month within the tile.

Follow the same steps to create filtered measures for Lost Time and Restricted/Modified Duties:

Filtered measure for Lost Time incidents

Filtered measure for Restricted Work/Modified Duty incidents

Run the Merge Query - Once you have completed this, click Run and you will now see the number of forms YTD per month that meet each incident requirement.

At this stage, you still do not need to worry about configuring the visualization. You will do that in the last step. For now, click Save in the top right corner of your Merge Query - this will take you to the Merged Results view.

Safety Intelligence will automatically merge the explores on the field that matches - in this case Month Name. Click Run and you will now see in the Data pane a monthly breakdown of Employee Hours, and a count of each measure you created.

viii. Create the Table Calculations for TRIR/LTIR/RTIR:

Now that you have the various monthly counts of incidents and the employee hours, you can calculate your Total Recordable Incident Rate (TRIR) first - here's how:

In the Merged Results view, click Add calculation to start creating your table calculation. This will open the Create table calculation view.

This is where to find the Add calculation button to add a table calculation.

Create table calculation view

Recall the TRIR calculation:

(Total number of recordable incidents x 200,000) / Total hours worked

In the Expression box, type the formula, but instead of numbers, you will use the names of the fields you have created. For example:
- Total Recordable Incidents = recordable_incidents (the filtered measure field you previously created)

Begin typing the name of the field and Safety Intelligence will suggest the correct field. You can simply select it form the list to insert it into the formula:

You formula should look like this in the Expression box:

(${recordable_incidents}*200000) / ${incidentrates.hoursentered}

Set the Format to your desired setting - in this case the TRIR rate has been formatted to show 2 decimal places. Create a name that reflects the calculation field as well i.e. TRIR:

Save your table calculation and repeat the same steps to create the calculations for LTIR and RTIR using the respective filtered measures. Remember that your measure name will vary depending on the name you gave to your filtered measures previously:

LTIR:

(${lost_time}*200000) / ${incidentrates.hoursentered}

RTIR:

(${restrictedmodified_duties}*200000) / ${incidentrates.hoursentered}

Run your report, and you will now have a monthly TRIR/LTIR/RTIR for the current year.

ix. Configure your Visualization

Now you can configure the visualization to display your monthly rates for TRIR/LTIR/RTIR along with a visual representation of the total monthly employee hours.

Hide the Counts for Each Filtered Measure - To focus on rates, hide the raw counts for each filtered measure:
- Click the cog icon next to each measure.
- Select the Hide this field from visualization option - you will know it's hidden when you see an eye with a line through it next to the measure name:

Select the Visualization Type - Open the Visualization pane and select the Line Graph option:

Customize the Visualization - On the right hand side of the Visualization pane, click Edit to open the configuration menu. You will see five tabs:
- Plot
- Series
- Values
- X & Y

Configure the Series -Go to the Series tab to configure the look of your graph.
- Colour Palette - Choose a colour palette that suits your reporting preferences.
- Show Full Field Name - Toggle this off to keep the labels concise.
- Point Style - Select your preferred point style (e.g. Outline or Circle). For this demo we have chosen the Outline point style.

At the bottom of the menu, you will see all of your fields listed. Click the small arrow next to each field to open further customization options:
- Employee Hours (renamed to Hours Worked)
  - Type is set as Column to show the monthly total as vertical bars
- Incident Rates (TRIR/LTIR/RTIR)
  - Type is set as Line to show the trend clearly over time
  - Point Style is set to Circle to highlight each data point

Show Values - Navigate to the Values tab and toggle Value Labels on if you want to display the values directly on the visualization.

Configure the Y-axis - Go to the Y tab:
- Ensure that TRIR, LTIR and RTIR are assigned to the Left Axes. If they are not, click the three lines next to the value label and drag them to the correct axis. Since the rates are smaller than the employee hours, move Hours Worked to the Right Axes:

(Optional) Plot Style - If you prefer a smooth curve for the lines in your graph, go to the Plot tab and under Line Interpolation select Monotone for a curvier line style.

Run your visualization one last time to see all of your configurations applied.

x. Save your New Visualization to your Dashboard

Once you are happy with the way your report looks, save your newly created visualization to your dashboard. To do this, select the cog icon at the top right corner of your Merged Results view and select Save to Dashboard...

Note: After saving your merge query, you will see the original Employee Hours tile that you initially created as the base for the merge query on your dashboard. You can safely delete this original tile, as it's no longer needed.

Additional Tips

Clear Labeling: Label your questions clearly to make it evident what each answer option represents. This will help users understand how to respond correctly and enable accurate data filtering in SI.

Consistency: Create a single, comprehensive incident form template that serves as the "source of truth" for all incident classification reporting and tracking, rather than multiple versions of incident forms if not required. This approach minimizes confusion and facilitates easier and more accurate querying in SI.

Optimized Date Fields: When referencing an incident date in your report, consider using the form's 'Date' header field as the Incident Date. This field is directly linked to the Form Date field in any of the Forms-related explores, streamlining the report-building process:
- If you prefer to use a Date-type question, be prepared to navigate the 'Forms with Multiple Questions' explore and apply specific filters, which may introduce additional complexity.
- For a reliable submission date timestamp of when the form was created, relay on the 'Created Date' field, which remains unaltered.

Additional Visualization Configuration - Legend Configuration - Go to the Plot tab in your visualization editing menu to adjust the legend settings:
- You can change the position of the legend to Left, Right, or Center to suit your preferences.
- Toggle Hide Legend on or off depending on whether you want it displayed in the chart.

Additional Visualization Configuration - Tick Density - In the Y-tab of Edit menu, you can further refine your Y-axis configuration by adjusting the Tick Density. This allows you to control the number of tick marks shown on the Y-axis, which can help if the graph looks too cluttered or sparse:
- Default - Will automatically adjust the tick density to the maximum/minimum values.
- Custom - Selecting the Custom tick option allows you to use the slider that appears to adjust the tick density in real-time so that you can visually see the chart as you adjust the slider.

Additional Visualization Configuration - Maximum/Minimum Values - In the Y-tab of Edit menu, you can adjust the Minimum and Maximum values to control the range of the Y-axis:
- Minimum - Set a baseline if you want to ensure that the Y-axis starts at a specific value other than 0.
- Maximum - Set an upper limit ot focus the graph on a particular range, preventing large outliers from skewing the view.

Related Resources & Reports

This section provides internal/external links to articles, videos, or other materials relevant to the contents of this guide:

Skills Learned

Table Calculations
Merge Query
Visualization - Line Graph

Related to