Customize Data Providers

You can create your own report definitions and customize existing report templates without programming skills.

This guide will show to customize a report definition or Data Providers. As an example, we will modify the distance report template to use an Ignition signal from the vehicles to define the trip starts and stops.

Data providers

A data provider processes raw track data and stores the aggregated information in a reporting table. The provider defines what data to process, how it should be aggregated, and if any calculations should be made before the data is stored in the reporting table.

Data providers run as background processes to provide fast access to updated reporting data. A reporting table can be used by multiple report queries.

The following types of data can be collected:

Address The address of a position. It can be the first (Start) or last (Stop) position that fits into the time interval of the record
Average The average value of the selected variable
Distance (GPS) The distance, measured by GPS
Distance (Odometer) The distance measured by the device odometer variable
Fuel Consumption The fuel consumption, if measured by the device
Geofence The Geofence name of a position. It can be the first (Start) or last (Stop) position that fits into the time interval of the record
Max The maximum value of the selected variable
Min The minimum value of the selected variable
Sum The sum of the occurrences for the selected variable
Value The value of any selected variable

The data is written to the reporting table when the write conditions are fulfilled.

The following options exist for write conditions:

Each trip One data row will be written for each trip. By default, a trip is started when a vehicle starts moving and stopped when a vehicle stands still for some time. A standstill (fatpoint in the query) is also considered a trip and will create another row in the reporting table.
Column value change A row will be written each time the selected variable changes its value. This can be used if for example an Ignition variable is used to define the start and stop for a trip.
Period of time A row is written after a defined time period. This is useful if you want to accumulate report data over a period of time, such as the number of incidents per hour.
Variable condition A row is written each time a condition is fulfilled for a variable, for example “[speed] > 10”

As an example, the data provider for the distance report creates a record for each trip based on the “Each trip” condition. The record includes the start and stop times and positions, user information, and trip distance. The record also holds additional data such as average and maximum speed during the trip as shown in the image below:

Note: Multiple data providers can be used by one report definition and this allows for flexible reports based on combinations of aggregated data.

Report Definitions

A report definition consists of at least one report query and a table layout definition for the final report. Multiple report definitions can use a reporting table created by a data provider.

Query editor

The query editor is used to define queries used by the report. Queries can use both system tables and the report tables created by the data providers. It is also common that queries use other sub-queries to generate the expected result. The image below shows the Query editor with the DistanceDetailed query.query_editor.jpg

As you can see in the image, it is possible to execute queries in the query editor to simplify testing.

Follow the steps below to execute a query:

  1. Edit the query and save it
  2. Verify that all require parameters are available, add the required parameters
  3. Select which application to run the query against
  4. Assign values to all parameters that suit your test, e.g. the right time interval and TagID
  5. Click on Execute to run the query

The result will be displayed below the query editor. You can now edit your query and change the parameters until you reach the expected result.

When your query returns the expected result you can move on to set the layout.

Report Layout

The report layout page defines column names and formats for the final report output. You can set the column order, remove columns from the report, and format the column headers and content. To create a layout, you select the query that you want to use for the report.

The following options are available for the fields:

Expression By default, this contains the variable. You can add expressions.
Title This is the display name for the column in the report
Unit The unit controls the formatting o the output. You can for example set date formatting to SHORT_DATE or LONG_DATE.
Sorting Sort order is either ascending or descending. If multiple columns have sorting, it will be prioritized from left to right
Action You can edit or remove a table column
Order You can move table columns up and down in the list

The image below shows the DistanceDetailed default table layout.


Modify an existing report

Now to the example. I will modify the distance report template to use the Ignition status (on / off) from the vehicles to define trip starts and stops. I will also remove the default setting for the template that is to use standstills (e.g. idling/stopped spots) to define trip starts and stops.

Create a new data provider

First, we create a new data provider that records trips based on Ignition status.

Login to Site Admin and follow these steps:

  1. Click on the Reporting tab and select Manage Data Providers
  2. Click on New
  3. Select Copy of Distance01 and click Next. Rename the new rule to DistanceIgnition and enter a description.
  4. In the section Select which data should be collected select Value in the drop-down menu and click on Add. Then enter the name Ignition and select the field Ignition. Click on Insert to add the column to the table
  5. In the section Select when data should be written click on Remove for the default value Each trip
  6. Select the Column value change from the drop-down menu and click on Add. Then select Ignition from the Monitor column drop-down menu
  7. Save the new data provider
  8. Select your application and a date with some tracking data to test the new provider. Click on Run to process data. The output will show you how your configuration processes the data

Create a new report definition (Query and layout)

Now that we have created a new data provider we also need to create a query and define the layout for the new report. It will be quite similar to the default distance report template so we can use that as a starting point.

Follow these steps to customize the query:

  1. Select Manage Reports from the menu and click on New
  2. Select Trip & Idle (Detailed) and click Next. This will create a copy of an existing template that we can modify.
  3. Click on Query editor in the section Table layout to edit the default query.
  4. Select report query DistanceDetailed and change the following:
    Change all the occurrences of Distance01 to DistanceIgnition
    Change Distance01.FatPointState WHEN ‘idle’ to DistanceIgnition.Ignition WHEN ‘0’
    Change Distance01.FatPointState WHEN ‘run’ to DistanceIgnition.Ignition WHEN ‘1’
    Remove the last line AND Distance01.FatPointState != ‘sleep’
  5. Save the query when done
  6. Test the query, select parameters, and click on Execute.
  7. Click on Back to return to the report layout page

The new query should look like this:

    CASE DistanceIgnition.Ignition WHEN '0'
        THEN TIME_TO_SEC(TIMEDIFF(DistanceIgnition.SamplingEnd, DistanceIgnition.SamplingStart))
        ELSE 0 END AS IdleDuration,
    CASE DistanceIgnition.Ignition WHEN '1'
        THEN TIME_TO_SEC(TIMEDIFF(DistanceIgnition.SamplingEnd, DistanceIgnition.SamplingStart))
        ELSE 0 END AS TripTime,
FROM DistanceIgnition
    JOIN Users ON Users.UserID = DistanceIgnition.UserID
WHERE DistanceIgnition.UserID IN (SELECT UserID FROM TagUsers WHERE TagUsers.TagID IN [TagID])
    AND DistanceIgnition.SamplingStart >= [PeriodStart]
    AND DistanceIgnition.SamplingEnd <= [PeriodEnd]


Create a new report layout

Now we have the right data in our report query. The report layout section lets us select how to publish the data in the report.

Follow these steps to modify the report layout:

  1. Select the Query DistanceDetailed
  2. Remove the row [FatPointState]
  3. Add a row with the expression [Ignition] and the Title Ignition state leave Unit and Sorting as None. Click Insert
  4. Rename the new report definition to Trip & Idle (Ignition)
  5. Click Save to save the report definition

We now have a new report definition that defines trips based on an ignition on/off status sent from the vehicles instead of the default behavior with standstills.