JonasJ
Posts: 185
Joined: Wed Jun 03, 2009 9:39 pm
Location: Sweden
Contact: Website

GUIDE: Driver's Journal Report

Fri Dec 30, 2011 5:57 pm

GUIDE: Driver's Journal Report

This guide explains how to create a Drivers Journal report that categorizes trips into "Private" or "Business". This example uses the ERM ZEE tracking device but a similar procedure could be used for any device that has corresponding signals that indicate trip category. Note that this is a fully automated drivers journal in the sense that it is not possible to audit the contents of the report. This guide works for MySQL.

Some technical details regarding device signals:
The ERM ZEE tracking device supports, among others, two signals named "KeyPad" and "EventCode". The key pad signal gets a value when the driver presses a keypad in the vehicle to indicate the category of the current trip, e.g. "Private" or "Business". Once pressed, the key pad value stays the same until another key has been pressed. The EventCode signal indicates "Ignition on" for value 4, "Ignition off" for value 5 and "Key pad was pressed" for value 34. We will use these event code value as a way to "reset" the trip category to "Private" when the driver turns the ignition on. It is assumed in this example that a KeyPad signal value=1 indicates "Business". KeyPad=2 or higher indicates "Private".

Requirements:
It is assumed that you currently have a ERM ZEE device (or similar device having signals that indicate trip category) online in a VehicleTracker application, having installed the "VarPack2" plugin and mapped the device signals KeyPad and EventCode to the corresponding GpsGate signals in the device mapper.

---

<font size="3">Setting up the Event Rules</font id="size3">

The report will be event based, so we will begin by logging in to the VehicleTracker application to create one event rule per trip category. The event rules should be mutually exclusive, that is, there should never be more than one of them active at a time. This will allow us to get the distance and duration in time for each trip category correctly.

1. Create a new Event Rule named "CategorizedTrip Business". In event rule wizard step 1 select the "Analyze only" radio button. Select the user tag(s) that the event rule should include. In event rule wizard step 4, add two Analog Expressions so that

Code: Select all

KeyPad Equal 1
EventCode Equal 34
Select the "Match all" radio button.

Click "Show advanced options" and add three Analog Expressions so that

Code: Select all

KeyPad Not Equal 1
EventCode Equal 4
EventCode Equal 5
Select the "Match any" radio button.

Notice that this event rule only triggers on a press on the KeyPad of value 1, and it is ended by any other KeyPad value or by EventCode 5 (ignition is turned off) or EventCode=4 (ignition is turned on).

Save the event rule.

The category "Private" is a bit more complex because of the "reset to Private" behaviour when ignition is turned on. It can be represented by three event rules.

2. Create a new Event Rule named "CategorizedTrip Private 1". In event rule wizard step 1 select the "Analyze only" radio button. Select the user tag(s) that the event rule should include. In event rule wizard step 4, add one Analog Expression so that

Code: Select all

EventCode Equal 4
Click "Show advanced options" and add two Analog Expressions so that

Code: Select all

EventCode Equal 34 
EventCode Equal 5
Select the "Match any" radio button.

Save the event rule.

3. Create a new Event Rule named "CategorizedTrip Private 2". In event rule wizard step 1 select the "Analyze only" radio button. Select the user tag(s) that the event rule should include. In event rule wizard step 4, add two Analog Expressions so that

Code: Select all

EventCode Equal 34
KeyPad Equal 2
Select the "Match all" radio button.

Click "Show advanced options" and add two Analog Expressions so that

Code: Select all

KeyPad Not Equal 2
EventCode Equal 5
Select the "Match any" radio button.

Save the event rule.

4. Create a new Event Rule named "CategorizedTrip Private 3". In event rule wizard step 1 select the "Analyze only" radio button. Select the user tag(s) that the event rule should include. In event rule wizard step 4, add two Analog Expressions so that

Code: Select all

EventCode Equal 34
KeyPad Greater 2
Select the "Match all" radio button.

Click "Show advanced options" and add two Analog Expressions so that

Code: Select all

KeyPad Less or equal 2
EventCode Equal 5
Select the "Match any" radio button.

Save the event rule.
Now we can make use of the event rules in the drivers journal report.

---

<font size="3">Defining the report query</font id="size3">

Log in to SiteAdmin and go to the Reportings tab.
5. Go to Site Admin -> Reporting -> Manage Data Providers.
6. Click "New".
7. Select Copy of EventProvider01 and click "Next".
8. Name the data provider "TripCategoryEventProvider01".
9. Select "Value" in the drop down list in the area "Select which data to be collected" and click "Add".
10. A new row is added to the list. Change the Name to "KeyPad" and select field "KeyPad". Select "Last" in the Options column. Click "Insert".
11. Select "Value" in the drop down list in the area "Select which data to be collected" and click "Add".
12. A new row is added to the list. Change the Name to "EventCode" and select field "EventCode". Select "Last" in the Options column. Click "Insert".
13. Click "Save" to save the data provider. Done!

Next we will create a customized version of the EV1000 Event Rule report for our drivers journal report.
14. Go to Site Admin -> Reporting -> Manage Reports.
15. Click "New" and select "EV1000 Event Rule" from the drop down list and click "Next".
16. Click "Query Editor"
17. Click the "QEventReport" report query.
18. Insert the following SQL query into the query textbox (MySQL):

Code: Select all

SELECT 
	CAST([PeriodStart] AS DATETIME) AS PeriodStart,
	CAST([PeriodEnd] AS DATETIME) AS PeriodEnd,
	TripCategoryEventProvider01.RuleName,
	[ApplicationID] AS ApplicationID,
	Users.Username,
	Users.Name,
	TIME_TO_SEC(TIMEDIFF(TripCategoryEventProvider01.SamplingEnd, 
        TripCategoryEventProvider01.SamplingStart)) AS Duration,
	TripCategoryEventProvider01.SamplingStart,
	TripCategoryEventProvider01.SamplingEnd,
	TripCategoryEventProvider01.UserID,
	TripCategoryEventProvider01.StartAddress,
	TripCategoryEventProvider01.StopAddress,
	TripCategoryEventProvider01.DistanceGps,
	TripCategoryEventProvider01.DistanceOdo,
	TripCategoryEventProvider01.KeyPad,
	TripCategoryEventProvider01.EventCode,
	TripCategoryEventProvider01.EventState,
	TripCategoryEventProvider01.EventArgument,
	CASE WHEN TripCategoryEventProvider01.DistanceGps > 0 THEN
		TripCategoryEventProvider01.DistanceGps / 
                TIME_TO_SEC(TIMEDIFF(TripCategoryEventProvider01.SamplingEnd, 
                TripCategoryEventProvider01.SamplingStart)) 
	ELSE	0
	END AS AvgSpeed,
	CASE WHEN TripCategoryEventProvider01.RuleName LIKE 'CategorizedTrip Privat%' THEN 1
		WHEN TripCategoryEventProvider01.RuleName = 'CategorizedTrip Business' THEN 2
		ELSE 0
	END AS TripCategory,
	CASE	WHEN TripCategoryEventProvider01.RuleName LIKE 'CategorizedTrip Privat%' THEN 'Private'
		WHEN TripCategoryEventProvider01.RuleName = 'CategorizedTrip Business' THEN 'Business'
		ELSE 'Unknown'
	END AS TripCategoryName,
	CASE WHEN TripCategoryEventProvider01.RuleName LIKE 'CategorizedTrip Privat%' 
           THEN DistanceGps ELSE 0 END AS DistanceGpsPrivate,
	CASE WHEN TripCategoryEventProvider01.RuleName = 'CategorizedTrip Business' 
           THEN DistanceGps ELSE 0 END AS DistanceGpsBusiness,
	CASE WHEN TripCategoryEventProvider01.RuleName LIKE 'CategorizedTrip Privat%' 
           THEN TIME_TO_SEC(TIMEDIFF(TripCategoryEventProvider01.SamplingEnd, 
           TripCategoryEventProvider01.SamplingStart)) ELSE 0 END AS DurationPrivate,
	CASE WHEN TripCategoryEventProvider01.RuleName = 'CategorizedTrip Business'
           THEN TIME_TO_SEC(TIMEDIFF(TripCategoryEventProvider01.SamplingEnd,
           TripCategoryEventProvider01.SamplingStart)) ELSE 0 END AS DurationBusiness
FROM TripCategoryEventProvider01
	JOIN Users ON Users.UserID = TripCategoryEventProvider01.UserID
	WHERE TripCategoryEventProvider01.EventRuleID IN [EventRule]
	AND TripCategoryEventProvider01.EventState != 'Off'
	AND TripCategoryEventProvider01.UserID IN
        (SELECT TagUsers.UserID FROM TagUsers WHERE TagUsers.TagID IN [Tag])
	AND (
		TripCategoryEventProvider01.SamplingStart BETWEEN [PeriodStart]  AND [PeriodEnd] OR 
		TripCategoryEventProvider01.SamplingEnd BETWEEN [PeriodStart] AND [PeriodEnd]
	)
ORDER BY TripCategoryEventProvider01.UserID, TripCategoryEventProvider01.SamplingStart
Notice how this SQL query uses conditional checks on the event rule name to determine if this is a Private or Business trip, and then creates distance and duration columns for each category. We can use these columns to calculate the total distance driven of Business trips for example.

19. Now we change the name of this report query. In the "Name" textbox, change the name from "QEventReport" to "CategorizedTripEvent".
20. Click "Save" to save the report query.

---

<font size="3">Creating the report layout</font id="size3">

The query is done, now it is time to create the layout of the report. The following screenshot shows the expected result of steps 21-55. If you already know how to edit a report layout to make it look like the screenshot, do that and skip the steps of this section.

Image

21. Click "Back" to go to the report layout view.
22. Delete the rows that say "Total Events:" and "Total Vehicles:" by right clicking the leftmost column of those rows -> "Delete row".

The report should include a summarization of distance and duration of the different kinds of trips.
23. Add five new rows below "Total Duration:".
24. On the first added row in the first column, enter "Total Duration Business:". In the cell to the right of it, right-click -> Function -> sum -> "DurationBusiness" to make it contain the text "$sum([DurationBusiness])". Set the unit of the function cell by right clicking it -> "Unit" -> "LONG_TIME_SPAN".
25. On the second added row in the first column, enter "Total Duration Private:". In the cell to the right of it, right-click -> Function -> sum -> "DurationPrivate" to make it contain the text "$sum([DurationPrivate])". Set the unit of the function cell by right clicking it -> "Unit" -> "LONG_TIME_SPAN".
26. On the third added row in the first column, enter "Total Distance:". In the cell to the right of it, right-click -> Function -> sum -> "DistanceGps" to make it contain the text "$sum([DistanceGps])". Set the unit of the function cell by right clicking it -> "Unit" -> "TRACK_DISTANCE".
27. On the fourth added row in the first column, enter "Total Distance Business:". In the cell to the right of it, right-click -> Function -> sum -> "DistanceGpsBusiness" to make it contain the text "$sum([DistanceGpsBusiness])". Set the unit of the function cell by right clicking it -> "Unit" -> "TRACK_DISTANCE".
28. On the fifth added row in the first column, enter "Total Distance Private:". In the cell to the right of it, right-click -> Function -> sum -> "DistanceGpsPrivate" to make it contain the text "$sum([DistanceGpsPrivate])". Set the unit of the function cell by right clicking it -> "Unit" -> "TRACK_DISTANCE".

We should display the report per user.
29. Remove the contents of the cell currently having the text [RuleName]. Right click the same empty cell -> Insert field" -> "Name". The cell should now contain the text [Name].
30. Delete the row containing "Total Events: $count([Username])".

We should display summed duration and distance of the different types per user.
31. Add six new rows below the row [RuleName]. Notice that the new row does not have any background color, you will have to set this yourself if you want to by using the available styling tools in the layout view. Enter the column new names and make the header cells text bold.
32. On the first added row in the first column, enter "Total Duration:". In the cell to the right of it, right-click -> Function -> sum -> "Duration" to make it contain the text "$sum([Duration])". Set the unit of the function cell by right clicking it -> "Unit" -> "LONG_TIME_SPAN".
33. On the second added row in the first column, enter "Total Duration Business:". In the cell to the right of it, right-click -> Function -> sum -> "DurationBusiness" to make it contain the text "$sum([DurationBusiness])". Set the unit of the function cell by right clicking it -> "Unit" -> "LONG_TIME_SPAN".
34. On the third added row in the first column, enter "Total Duration Private:". In the cell to the right of it, right-click -> Function -> sum -> "DurationPrivate" to make it contain the text "$sum([DurationPrivate])". Set the unit of the function cell by right clicking it -> "Unit" -> "LONG_TIME_SPAN".
35. On the fourth added row in the first column, enter "Total Distance:". In the cell to the right of it, right-click -> Function -> sum -> "DistanceGps" to make it contain the text "$sum([DistanceGps])". Set the unit of the function cell by right clicking it -> "Unit" -> "TRACK_DISTANCE".
36. On the fifth added row in the first column, enter "Total Distance Business:". In the cell to the right of it, right-click -> Function -> sum -> "DistanceGpsBusiness" to make it contain the text "$sum([DistanceGpsBusiness])". Set the unit of the function cell by right clicking it -> "Unit" -> "TRACK_DISTANCE".
37. On the sixth added row in the first column, enter "Total Distance Private:". In the cell to the right of it, right-click -> Function -> sum -> "DistanceGpsPrivate" to make it contain the text "$sum([DistanceGpsPrivate])". Set the unit of the function cell by right clicking it -> "Unit" -> "TRACK_DISTANCE".

We should show the report data grouped by trip category.
38. Right click on the leftmost column -> "Insert Group".
39. In the drop down list for "Expression:", select "TripCategoryName". The texbox for "Expression:" should now contain the value "[TripCategoryName]".
40. In the drop down list for "Sort:", select "TripCategoryName". Select "Ascending" for the sort order.
41. Click "OK". There should now be a level 3 group in the report layout.
42. Level 3 is now grouped on trip category, so we should display that value here. Right click the first level 3 cell and choose "Insert field" -> "TripCategoryName".

On level 3, we should display summed duration and distance of the trip category.
43. Add two new rows below the row [TripCategoryName].
44. On the first added row in the first column, enter "Total Duration:". In the cell to the right of it, right-click -> Function -> sum -> "Duration" to make it contain the text "$sum([Duration])". Set the unit of the function cell by right clicking it -> "Unit" -> "LONG_TIME_SPAN".
45. On the second added row in the first column, enter "Total Distance:". In the cell to the right of it, right-click -> Function -> sum -> "DistanceGps" to make it contain the text "$sum([DistanceGps])". Set the unit of the function cell by right clicking it -> "Unit" -> "TRACK_DISTANCE".

The report should have the columns "Start Time", "Stop Time", "Duration", "Start / Stop Address" and "Distance". Delete the old level 2 header row and remove the contents of all cells of the item row (the row which left-most cell is gray and has three horizontal lines in it). The new column names should be created as a header row on the level 3 group. For this, we need a new level 3 row to put them in.
46. Right click the last added row on level 3 row and select "Insert Row below". Enter the new column names as mentioned earlier and make the header cells text bold.

The reason we have a column named "Start / Stop Address" is that addresses typically take a lot of horizontal space and we will avoid that a bit by displaying each data item as two rows, showing start address in the first row and stop address in the second row of each data item in the report.
47. Insert a new item row by right-clicking the leftmost cell having three horizontal lines in it (an item row) and select "Insert Row below".

We should now insert some values in our two item rows and we begin in the top-most item row.
48. For column "Start Time", right click the empty cell of the item row -> Insert field" -> "SamplingStart". Set the unit of the cell by right clicking it -> "Unit" -> "FULL_DATE_TIME".
49. For column "Stop Time", right click the empty cell of the item row -> Insert field" -> "SamplingEnd". Set the unit of the cell by right clicking it -> "Unit" -> "FULL_DATE_TIME".
50. For column "Duration", right click the empty cell of the item row -> Insert field" -> "Duration". Set the unit of the cell by right clicking it -> "Unit" -> "LONG_TIME_SPAN".
51. For column "Start / Stop Address", right click the empty cell of the item row -> Insert field" -> "StartAddress". Set the unit of the cell by right clicking it -> "Unit" -> "None".
52. For column "Distance", right click the empty cell of the item row -> Insert field" -> "DistanceGps". Set the unit of the cell by right clicking it -> "Unit" -> "TRACK_DISTANCE".

For the second item row we should enter the stop address as the only cell containing anything for that item row.
53. For column "Start / Stop Address", right click the empty cell of the second item row -> Insert field" -> "StopAddress". Set the unit of the cell by right clicking it -> "Unit" -> "None".

54. Now that the data is in the report, feel free to further style the report by setting background colors to headers or adding padding between rows etc. using the available styling tools.
55. We should change the name of the report to reflect what it is. For this example, enter "Driver's Journal" in the Report Properties -> Name textbox and click "Save". Done!

To view the report you need to enable it for an application and process some data as usual.

---

Best Regards,
Jonas J
GpsGate Support

JonasJ
Posts: 185
Joined: Wed Jun 03, 2009 9:39 pm
Location: Sweden
Contact: Website

RE: GUIDE: Driver's Journal Report

Thu Jan 05, 2012 6:27 pm

Update 2012-01-05: Event rule setup steps and SQL query text updated.

Best Regards,
Jonas J
GpsGate Support

piter197
Posts: 832
Joined: Sun Jan 09, 2011 3:32 am

RE: GUIDE: Driver's Journal Report

Tue Mar 27, 2012 12:11 pm

Hi,

I'm a bit confuse to understand this ERM ZEE logic using keypad, eventcode for switch and eventcode for ignition.

Suppose for MVT380, I already mapped:
-Input2 = true for business trip
-Input2 = false for private trip
-Input4 = true for ignition ON
-Input4 = false for ignition off

All data send from device to server will include the status of this Input2 and Input4.

How's the modification of example above especially for Event Rule?

Thank you.


Regards,
piter197

Return to “GpsGate for Developers”