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.
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 in Site Admin 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.
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:
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 to 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.