Customize Data Providers

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

This guide will show how to customize a report definition or Data Providers. For example, we will modify the distance report template to use an Ignition signal from the assets 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. Multiple report queries can use a reporting table.

Data providers are managed in VehicleTracker under Main Menu > Reports > Manage Data Providers. You need the Manage Data Providers privilege to see this menu item.

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 tracker odometer variable
Fuel Consumption	The fuel consumption, if measured by the tracker
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 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 asset starts moving and stopped when a asset stands still for some time. A standstill (fat point 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 (open Distance01 in Manage Data Providers):

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 for queries to use other sub-queries to generate the expected result. Report templates and their queries are edited in Main Menu > Reports > Manage Report Templates (open a template and click Query editor). The image below shows the Query editor with the DistanceDetailed query.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 click Save
2. Verify that all required parameters are available in Parameter values; click Add parameter to add the ones that are missing
3. Select which application to run the query against in ApplicationID
4. Assign values to all parameters that suit your test, e.g. the right time interval (Period) and GroupID
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.

Modify an existing report

Now to the example. I will modify the distance report template to use the Ignition status (on / off) from the assets 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.

In VehicleTracker, follow these steps:

1. Go to Main Menu > Reports > Manage Data Providers
2. Click on New data provider
3. In Select which data to process, select Copy of Distance01 and click Next. In Name, enter DistanceIgnition (the name cannot contain spaces) and enter a Description.
4. In the section Select which data to be collected select Value in the drop-down menu and click on Add column. In the new row, replace the name with Ignition and select the field Ignition.
5. In the section Select when data should be written, select Column value change from the drop-down menu and click on Add write condition. Then select Ignition in the Options drop-down menu of the new row.
6. Click the delete icon for the default write condition Each trip and confirm with Delete. A data provider needs at least one write condition, so add the new one first. Removing Each trip also removes the FatPointState column it collects.
7. Click Save to save the new data provider
8. To test the new provider, open a report query in the query editor (see below) and use Execute with your application and a date with some tracking data.
9. 

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. Go to Main Menu > Reports > Manage Report Templates and click on New report template
2. In Template, select TR1000 Trip & Idle detailed (Group) and click Create. This will create a copy of an existing template that we can modify.
3. Click on Query editor.
4. Under Report queries, select 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 line AND Distance01.FatPointState != ‘sleep’
5. Click Save to save the query when done
6. Test the query: select the parameter values and click on Execute.
7. Click on Back to return to the report template page

The new query should look like this:

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. Remove the value [FatPointState]
2. Click right on the cell and Insert the field [Ignition] and the column title Ignition state 
3. In Name, rename the new report template to Trip & Idle (Ignition)
4. Click Save to save the report.

We now have a new report template that defines trips based on ignition on/off status sent from the assets instead of the default behaviour with standstills.

Note: for a transition period, data providers and report templates can also still be managed in Site Admin (Legacy) > Reporting.