Trip and idle report with temperature values

This guide explains how to include temperature values to the Trip and Idle report.

Mapping the temperature signal

In order to get temperature values displayed on the Trip and Idle report, the first thing that needs to be done is mapping the temperature signal received from our device. Use the device mapper to map the temperature signal to the temperature variable.

tempmapper

Creating a new Data Provider

At this point, a new Data Provider has to be created to include the temperature signals needed on the report. The Data Providers are found on SiteAdmin under the Reporting tab. Select “Manage Data Providers”, click the “New” button and then select “Copy of Distance01” from the drop down list. Then the name can be changed to DistanceandTemp, for example. 

The values that will be displayed on the report are maximum, minimum and average temperature, so they have to be added using the dropdown list below the values already present on the Data Provider.

add values

Replace the name “New” with something more intuitive (for example, MaxTemp) and look for the temperature variable on the dropdown list to the right of the name. Repeat this procedure for Min and Avg values, then save the changes. The Data Provider should look like this:

temp dataprovider

Adding temperature to the report query and layout

Now a new report has to be created based on the Trip and Idle report. A new report can be created in SiteAdmin under the Reporting tab. Select the “New” button at the bottom of the page and the TR1000 Trip & Idle (Detailed) template from the list. First the query has to be changed to use the new data provider. Click the “Query Editor” button and select the “DistanceDetailed” query on the top left menu, delete the SQL code, and replace it with this:

 
SELECT
    CAST([PeriodStart] AS DATETIME) AS PeriodStart,
    CAST([PeriodEnd] AS DATETIME) AS PeriodEnd,
    [ApplicationID] AS ApplicationID,
    Users.Username,
    Users.Name,
    TIME_TO_SEC(TIMEDIFF(DistanceandTemp.SamplingEnd, DistanceandTemp.SamplingStart)) AS Duration,
    CASE DistanceandTemp.FatPointState WHEN 'idle'
        THEN TIME_TO_SEC(TIMEDIFF(DistanceandTemp.SamplingEnd, DistanceandTemp.SamplingStart))
        ELSE 0 END AS IdleDuration,
    CASE DistanceandTemp.FatPointState WHEN 'run'
        THEN TIME_TO_SEC(TIMEDIFF(DistanceandTemp.SamplingEnd, DistanceandTemp.SamplingStart))
        ELSE 0 END AS TripTime,
    DistanceandTemp.SamplingStart,
    DistanceandTemp.SamplingEnd,
    DistanceandTemp.UserID,
    DistanceandTemp.StartAddress,
    DistanceandTemp.StopAddress,
    DistanceandTemp.DistanceGps,
    DistanceandTemp.DistanceOdo,
    DistanceandTemp.MaxSpeed,
    CASE WHEN DistanceandTemp.DistanceGps > 0 THEN
        DistanceandTemp.DistanceGps / TIME_TO_SEC(TIMEDIFF(DistanceandTemp.SamplingEnd, DistanceandTemp.SamplingStart))
    ELSE
        0
    END AS AvgSpeed,
    DistanceandTemp.MaxTemp,
    DistanceandTemp.MinTemp,
    DistanceandTemp.AvgTemp,
    DistanceandTemp.FatPointState,
    DistanceandTemp.Fuel
FROM DistanceandTemp
    JOIN Users ON Users.UserID = DistanceandTemp.UserID
WHERE DistanceandTemp.UserID IN (SELECT UserID FROM TagUsers WHERE TagUsers.TagID IN [TagID])
    AND DistanceandTemp.SamplingStart >= [PeriodStart]
    AND DistanceandTemp.SamplingEnd <= [PeriodEnd]
    AND DistanceandTemp.FatPointState != 'sleep'
    ORDER BY DistanceandTemp.UserID, DistanceandTemp.SamplingStart

Save the changes and go back to the layout editor. The last step is adding the columns for the max, min and avg temperature values.

columns

Save the changes on the layout.

Activate the report in an application

The report needs to be activated in an application before it can be used. Activate the report by following the steps in section 2 of this guide.