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

GUIDE: Custom distance report with trip categories

Thu Dec 22, 2011 7:04 pm

GUIDE: Custom distance report with trip categories

This guide describes how to create a custom report showing the total distance of business, private or home/office trips based on a value sent from a device.
A specific device signal (as set by the driver to specify the current trip category, e.g. business) is mapped to the GpsGate Server field CustomAnalog1 which is then used in the report Data Provider.

Requirements:
* A device that is supported by GpsGate Server and is capable of sending a signal value that corresponds to the current trip category. Once a value has been sent for the signal, the value should remain the same in all subsequent messages until sending a different value.
* The latest update of the plugins "Core" and "Reporting".

Expected device signal values for trip category:
1 - indicates "business"
2 - indicates "private"
3 - indicates "home/office"

We will start by creating a new report data provider where each trip will include the latest value of trip category.
1. Go to Site Admin -> Reporting -> Manage Data Providers.
2. Click "New".
3. Select Copy of Distance01 and click "Next".
4. Name the data provider "CategorizedTripDistance01".
5. Select "Value" in the drop down list in the area "Select which data to be collected" and click "Add".
6. A new row is added to the list. Change the Name to "TripCategory" and select field "CustomAnalog1". Select "Last" in the Options column. Click "Insert".
7. Click "Save" to save the data provider. Done!

Next we will create a customized version of the TR1000 Trip & Idle (Detailed) report where we will add the option to show trips based on category.
8. Go to Site Admin -> Reporting -> Manage Reports.
9. Click "New" and select "TR1000 Trip & Idle (Detailed)" from the drop down list and click "Next".
10. Click "Query Editor"
11. Click the "DistanceDetailed" report query.
12. Add a new parameter that will let you select which trip categories to display when viewing the final report. Click "Add Parameter".
13. Select "Custom Value" in the drop down list and enter "Trip Category" in the "Parameter name" textbox.
14. Make it Multi valued by ticking the checkbox.
15. Enter "Business" in the "Label" textbox.
16. Enter "1" in the "Value" textbox.
17. Click "Add New Value". The value was added.
18. Enter "Private" in the "Label" textbox.
19. Enter "2" in the "Value" textbox.
20. Click "Add New Value". The value was added.
21. Enter "Home/Office" in the "Label" textbox.
22. Enter "3" in the "Value" textbox.
23. Click "Add New Value". The value was added.
24. Click "Add" to add your new custom value parameter to the report. It should show up as [Trip Category] and the drop down list containing your selectable trip categories.
25. Now we will modify the SQL text to select from the new report data provider named "CategorizedTripDistance01" that you created in step 9, and include the new column, filtering on the custom value report parameter.
If there is no value for the trip category in the database, the trip will default to category Private. This may occur when no signal value has yet been set (e.g. when the device wakes up because the vehicle ignition is turned on by the driver). The SQL (MySQL) should look as follows:

Code: Select all

SELECT 
	CAST([PeriodStart] AS DATETIME) AS PeriodStart,
	CAST([PeriodEnd] AS DATETIME) AS PeriodEnd,
	[ApplicationID] AS ApplicationID,
	Users.Username,
	Users.Name,
	TIME_TO_SEC(TIMEDIFF(CategorizedTripDistance01.SamplingEnd, CategorizedTripDistance01.SamplingStart)) AS Duration,
	CASE CategorizedTripDistance01.FatPointState WHEN 'idle' 
		THEN TIME_TO_SEC(TIMEDIFF(CategorizedTripDistance01.SamplingEnd, CategorizedTripDistance01.SamplingStart))
		ELSE 0 END AS IdleDuration,
	CASE CategorizedTripDistance01.FatPointState WHEN 'run' 
		THEN TIME_TO_SEC(TIMEDIFF(CategorizedTripDistance01.SamplingEnd, CategorizedTripDistance01.SamplingStart))
		ELSE 0 END AS TripTime,
	CategorizedTripDistance01.SamplingStart,
	CategorizedTripDistance01.SamplingEnd,
	CategorizedTripDistance01.UserID,
	CategorizedTripDistance01.StartAddress,
	CategorizedTripDistance01.StopAddress,
	CategorizedTripDistance01.DistanceGps,
	CategorizedTripDistance01.DistanceOdo,
	CASE WHEN CategorizedTripDistance01.DistanceGps > 0 THEN
		CategorizedTripDistance01.DistanceGps / TIME_TO_SEC(TIMEDIFF(CategorizedTripDistance01.SamplingEnd, CategorizedTripDistance01.SamplingStart)) 
	ELSE
		0
	END AS AvgSpeed,
	CategorizedTripDistance01.FatPointState,
	CategorizedTripDistance01.Fuel,
	CASE CategorizedTripDistance01.TripCategory WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 ELSE 2 END AS TripCategory,
	CASE CategorizedTripDistance01.TripCategory WHEN 1 THEN 'business' WHEN 2 THEN 'private' WHEN 3 THEN 'home/office' ELSE 'private' END AS TripCategoryName,
	CASE WHEN CategorizedTripDistance01.TripCategory = 1 THEN CategorizedTripDistance01.DistanceGps ELSE 0 END AS BusinessDistance,
	CASE WHEN CategorizedTripDistance01.TripCategory = 2
		OR CategorizedTripDistance01.TripCategory < 1 
		OR CategorizedTripDistance01.TripCategory > 3 
		OR CategorizedTripDistance01.TripCategory is null
		THEN CategorizedTripDistance01.DistanceGps ELSE 0 END AS PrivateDistance,
	CASE WHEN CategorizedTripDistance01.TripCategory = 3 THEN CategorizedTripDistance01.DistanceGps ELSE 0 END AS HomeOfficeDistance
FROM CategorizedTripDistance01
	JOIN Users ON Users.UserID = CategorizedTripDistance01.UserID
WHERE CategorizedTripDistance01.UserID IN (SELECT UserID FROM TagUsers WHERE TagUsers.TagID IN [TagID])
	AND CategorizedTripDistance01.SamplingStart >= [PeriodStart] 
	AND CategorizedTripDistance01.SamplingEnd <= [PeriodEnd]
	AND CategorizedTripDistance01.FatPointState != 'sleep'
	AND CategorizedTripDistance01.TripCategory IN [Trip Category]
	ORDER BY CategorizedTripDistance01.UserID, CategorizedTripDistance01.SamplingStart
26. Now we change the name of this report query. In the "Name" textbox, change the name from "DistanceDetailed" to "CategorizedTripDistanceDetailed".
27. Click "Save" to save the report query.

28. We will now modify the report layot to make use of the new TripCategory column. Click "Back" to go to the report layout view.
29. The report should include a summarization of the different kinds of trips: Add three new rows below the row that says "Total Distance:".
30. On the first added row in the first column, enter "Total Business Distance:". In the cell to the right of it, right-click -> Function -> sum -> "BusinessDistance" to make it contain the text "$sum([BusinessDistance])". Set the unit of the function cell by right clicking it -> "Unit" -> "SIGNAL_DISTANCE".
31. On the second added row in the first column, enter "Total Private Distance:". In the cell to the right of it, right-click -> Function -> sum -> "PrivateDistance" to make it contain the text "$sum([PrivateDistance])". Set the unit of the function cell by right clicking it -> "Unit" -> "SIGNAL_DISTANCE".
32. On the third added row in the first column, enter "Total Home/Office Distance:". In the cell to the right of it, right-click -> Function -> sum -> "HomeOfficeDistance" to make it contain the text "$sum([HomeOfficeDistance])". Set the unit of the function cell by right clicking it -> "Unit" -> "SIGNAL_DISTANCE".
33. We should display the report per user and also group it on trip category. Right click on the leftmost column -> "Insert Group".
34. In the drop down list for "Expression:", select "TripCategoryName". The texbox for "Expression:" should now contain the value "[TripCategoryName]".
35. In the drop down list for "Sort:", select "TripCategoryName". Select "Ascending" for the sort order.
36. Click "OK". There should now be a level 3 group in the report layout.
37. Level 3 is grouped on trip category, so we should display that value here. Right click the first level 3 cell and choose "Insert field" -> "TripCategoryName".
38. All header row labels should now be moved down to the level 3 group. For this, we need a new level 3 row to put them in. Right click the top level 3 row and select "Insert Row below". An empty row was inserted. 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.
39. Copy every column header name individually from the level 2 header row (e.g. Start Time, Stop Time, Duration etc) to the new row. Make the header cells text bold.
40. When all headers have been copied to level 3, delete the level 2 row containing the same column header texts that are no longer needed. Right click the leftmost column on the level 2 header row -> "Delete row".
41. We should calculate the total distance of each trip category. This is preferrably done per user which is done best at level 2 in the layout. Add three new rows on level 2. Notice that there is no background color for the new rows, you will have to copy the background color value from an existing level 2 row to the new rows individually.
42. In the first cell in the first row enter "Business Distance:". In the cell to the right of this, right click -> "Function" -> sum -> "BusinessDistance". Set the unit of the function cell by right clicking it -> "Unit" -> "SIGNAL_DISTANCE".
43. In the first cell in the second row enter "Private Distance:". In the cell to the right of this, right click -> "Function" -> sum -> "PrivateDistance". Set the unit of the function cell by right clicking it -> "Unit" -> "SIGNAL_DISTANCE".
44. In the first cell in the third row enter "Home/Office Distance:". In the cell to the right of this, right click -> "Function" -> sum -> "HomeOfficeDistance". Set the unit of the function cell by right clicking it -> "Unit" -> "SIGNAL_DISTANCE".
45. We should change the name of the report to reflect what it is. For this example, enter "Categorized Trip" 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

Strack01
Posts: 27
Joined: Thu Jun 09, 2016 7:02 pm

RE: GUIDE: Custom distance report with trip categories

Tue Jun 21, 2016 1:31 am

Hi Jonas,

is there an updated way of doing this via GPSGate 4.0 script expressions now?

Return to “GpsGate for Developers”