|2011-03-23 21:03 by Henrik||Guides, Reporting|
The reporting framework introduced in GpsGate Server 3.0 enables easy report customization. You can create your own report definitions and customize existing report templates without programming skills.
I will describe the different parts of the reporting framework and then show how to customize a report definition. As an example I will modify the distance report template to use an Ignition signal from the vehicles to define trip starts and stops.
Technical note: This requires at least version 18.104.22.16803 of the Reporting plugin. Read more about updating it here
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 start moving and stopped when a vehicle stands still for some time. A stand still (fat point) 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 it’s value. This can be used if for example an Ignition variable is used to define 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 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.
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.
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.
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:
- Edit the query and save it
- Verify that all require parameters are available, add the required parameters
- Select which application to run the query against
- Assign values to all parameters that suit your test, e.g. the right time interval and TagID
- 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.
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 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 fat points (e.g. idle spots) to define trip starts and stops.
Create new data provider
First we create a new data provider that records trips based on the Ignition status.
Login to Site Admin and follow these steps:
- Click on the Reporting tab and select Manage Data Providers
- Click on New
- Select Copy of Distance01 and click Next. Rename the new rule to DistanceIgnition and enter a description.
- 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
- In the section Select when data should be written click on Remove for the default value Each trip
- Select Column value change from the drop down menu and click on Add. Then select Ignition from the Monitor column drop down menu
- Save the new data provider
- 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:
- Select Manage Reports from the menu and click on New
- Select Trip & Idle (Detailed) and click Next. This will create a copy of an existing template that we can modify.
- Click on Query editor in the section Table layout to edit the default query.
- 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’
- Save the query when done
- Test the query, select parameters and click on Execute.
- Click on Back to return to the report layout page
The new query should look like this:
SELECT Users.Username, Users.Name, 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, DistanceIgnition.SamplingStart, DistanceIgnition.SamplingEnd, DistanceIgnition.UserID, DistanceIgnition.StartAddress, DistanceIgnition.StopAddress, DistanceIgnition.DistanceGps, DistanceIgnition.DistanceOdo, DistanceIgnition.MaxSpeed, DistanceIgnition.AvgSpeed, DistanceIgnition.Ignition, DistanceIgnition.Fuel 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:
- Select the Query DistanceDetailed
- Remove the row [FatPointState]
- Add a row with the expression [Ignition] and the Title Ignition state leave Unit and Sorting as None. Click Insert
- Rename the new report definition to Trip & Idle (Ignition)
- Click Save to save the report definition