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

GUIDE: How to use report Custom Value parameter

Thu Sep 15, 2011 8:46 pm

This guide applies to GpsGate Server 3.0 with the reporting framework.

Example case:
In this guide we will customize the Trip & Idle Detail report to make it possible to select to show only trips where vehicles are idling or trips where vehicles are moving, or both.

To do this, we can customize the Report query (SQL query to querying the collected data) to make use of the Custom Value parameter.

Solution:
1. Go to SiteAdmin -> Reporting -> Manage Report -> New
2. Select "TR1000 Trip & Idle (Detailed)" -> next
3. Give it the name "TR1000 Trip & Idle (Detailed) - Selectable Trip/Idle"
4. Now we need to edit the "query" to filter the data using the values of two new Time of Day parameters. To do this click "query editor"
5. Click the Report Query named "DistanceDetailed"
6. Add a new Custom Value parameter to the query by clicking Add Parameter and select "Custom Value" in the drop down list. For Parameter name, enter the text "TripState"
7. Tick the checkbox Multi valued.
8. Add a new selectable value. For Label, enter the text "Idle". For Value, enter the text "idle".
9. Click "Add New Value". The value is added to the list.
10. Add a new selectable value. For Label, enter the text "Trip". For Value, enter the text "run".
11. Click "Add New Value". The value is added to the list that now contains two values.
12. Click "Add"
13. We need to use the custom value parameter in the SQL query. Modify the query text (with MySQL radiobutton selected) to the following (Modifications are highlighted in red.):

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(Distance01.SamplingEnd, Distance01.SamplingStart)) AS Duration,
	CASE Distance01.FatPointState WHEN 'idle' 
		THEN TIME_TO_SEC(TIMEDIFF(Distance01.SamplingEnd, Distance01.SamplingStart))
		ELSE 0 END AS IdleDuration,
	CASE Distance01.FatPointState WHEN 'run' 
		THEN TIME_TO_SEC(TIMEDIFF(Distance01.SamplingEnd, Distance01.SamplingStart))
		ELSE 0 END AS TripTime,
	Distance01.SamplingStart,
	Distance01.SamplingEnd,
	Distance01.UserID,
	Distance01.StartAddress,
	Distance01.StopAddress,
	Distance01.DistanceGps,
	Distance01.DistanceOdo,
	Distance01.MaxSpeed,
	CASE WHEN Distance01.DistanceGps > 0 THEN
		Distance01.DistanceGps / TIME_TO_SEC(TIMEDIFF(Distance01.SamplingEnd, Distance01.SamplingStart)) 
	ELSE
		0
	END AS AvgSpeed,
	Distance01.FatPointState,
	Distance01.Fuel
FROM Distance01
	JOIN Users ON Users.UserID = Distance01.UserID
WHERE Distance01.UserID IN (SELECT UserID FROM TagUsers WHERE TagUsers.TagID IN [TagID])
	AND Distance01.SamplingStart >= [PeriodStart] 
	AND Distance01.SamplingEnd <= [PeriodEnd]
	AND Distance01.FatPointState != 'sleep'
	<font color="red">AND Distance01.FatPointState IN [TripState]</font id="red">
	ORDER BY Distance01.UserID, Distance01.SamplingStart
14. Select the T-SQL radiobutton and modify the query text to the following (Modifications are highlighted in red.):

Code: Select all

SELECT 
	CAST([PeriodStart] AS DATETIME) AS PeriodStart,
	CAST([PeriodEnd] AS DATETIME) AS PeriodEnd,
	[ApplicationID] AS ApplicationID,
	Users.Username,
	Users.Name,
	DATEDIFF(SECOND, Distance01.SamplingStart, Distance01.SamplingEnd) AS Duration,
	CASE Distance01.FatPointState WHEN 'idle' 
		THEN DATEDIFF(SECOND, Distance01.SamplingStart, Distance01.SamplingEnd)
		ELSE 0 END AS IdleDuration,
	CASE Distance01.FatPointState WHEN 'run' 
		THEN DATEDIFF(SECOND, Distance01.SamplingStart, Distance01.SamplingEnd)
		ELSE 0 END AS TripTime,
	Distance01.SamplingStart,
	Distance01.SamplingEnd,
	Distance01.UserID,
	Distance01.StartAddress,
	Distance01.StopAddress,
	Distance01.DistanceGps,
	Distance01.DistanceOdo,
	Distance01.MaxSpeed,
	CASE WHEN Distance01.DistanceGps > 0 THEN
		Distance01.DistanceGps / DATEDIFF(SECOND, Distance01.SamplingStart, Distance01.SamplingEnd) 
	ELSE
		0
	END AS AvgSpeed,
	Distance01.FatPointState,
	Distance01.Fuel
FROM Distance01
	JOIN Users ON Users.UserID = Distance01.UserID
WHERE Distance01.UserID IN (SELECT UserID FROM TagUsers WHERE TagUsers.TagID IN [TagID])
	AND Distance01.SamplingStart >= [PeriodStart] 
	AND Distance01.SamplingEnd <= [PeriodEnd]
	AND Distance01.FatPointState != 'sleep'
	<font color="red">AND Distance01.FatPointState IN [TripState]</font id="red">
	ORDER BY Distance01.UserID, Distance01.SamplingStart
15. Click "Save".

We are done! You can now activate the report for an application and you will be able to select the trip state for the report data by ticking for example the checkbox "Idle" in the TripState drop down list.

Note that the Label of a custom value is translatable for the report by using the Label as a phrase key. In this case, "Idle" and "Trip" would become phrase keys in the language file. See the following post for more information on how to translate reports:
topic.asp?TOPIC_ID=13566

Best Regards,
Jonas J
GpsGate Support

teonguyen
Posts: 45
Joined: Fri May 07, 2010 11:06 am
Location: Vietnam
Contact: Website

RE: GUIDE: How to use report Custom Value parameter

Tue Sep 20, 2011 2:01 pm

Dear Jonasj

We have some customers, they want to determine how much fuel was added when additional fuel tanks, you can help us to create that column in the FU1001 Fuel Report (Daily)report.

Thanks for support

User avatar
johan
GpsGate
Posts: 15293
Joined: Wed Aug 04, 2004 10:40 pm
Location: Sweden
Contact: Website

RE: GUIDE: How to use report Custom Value parameter

Tue Sep 20, 2011 2:02 pm

Please post new questions in new topics!

Regards,
Johan

GpsGate Support

Return to “GpsGate for Developers”