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

GUIDE: How to use report Time of Day parameter

Thu Sep 15, 2011 8:11 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 only show trips that are within a daily time interval (for example 8-17).

To do this, we need to customize the Report query (SQL query to querying the collected data) to make use of two Time of Day parameters.

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) - Within daily time interval"
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".
5. Add a new Time of Day parameter to the query by clicking Add Parameter and select "Time of Day" in the drop down list. Name it "StartTime". Click Add.
6. Add yet another Time of Day parameter to the query by clicking Add Parameter and select "Time of Day" in the drop down list. Name it "EndTime". Click Add.
7. We need to use the two parameters in the SQL query. The query will look slightly different for MySQL and T-SQL. 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'
        AND CAST(DATE_ADD(Distance01.SamplingStart, INTERVAL [_UTCOFFSET] SECOND) AS TIME) > [StartTime]
	AND CAST(DATE_ADD(Distance01.SamplingEnd, INTERVAL [_UTCOFFSET] SECOND) AS TIME) < [EndTime]
	ORDER BY Distance01.UserID, Distance01.SamplingStart
8. 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'
	AND CAST(DATEADD(SECOND, [_UTCOFFSET], Distance01.SamplingStart) AS TIME) > [StartTime]
	AND CAST(DATEADD(SECOND, [_UTCOFFSET], Distance01.SamplingEnd) AS TIME) < [EndTime]
	ORDER BY Distance01.UserID, Distance01.SamplingStart
9. Click "Save".

We are done! You can now activate the report for an application and you will be able to select a daily time interval for the report data by selecting for example 08:00 and 17:00 in the StartTime and EndTime drop down lists.

Best Regards,
Jonas J
GpsGate Support

Bolt
Posts: 197
Joined: Fri Apr 17, 2009 2:55 am
Location: Australia

RE: GUIDE: How to use report Time of Day parameter

Sun Sep 18, 2011 2:57 am

Hi

I am subscriber on your server.

- Am I able to create this report?
- Can the report be created to run from one day to the next?
For example, 1800 to 0600 the next day

Cheers

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

RE: GUIDE: How to use report Time of Day parameter

Mon Sep 19, 2011 4:35 pm

Bolt,

Yes, as long as you have a subscription with access to SiteAdmin you can create this customized report and activate the report for selected applications.

I guess a solution to your specific problem could be to change the SQL to include trips later than 18 or sooner than 06, and you would get the desired result over two consecutive dates in the report.

Best Regards,
Jonas J
GpsGate Support

Bolt
Posts: 197
Joined: Fri Apr 17, 2009 2:55 am
Location: Australia

RE: GUIDE: How to use report Time of Day parameter

Sun Sep 25, 2011 4:19 am

Hi Jonas

I'm lost.....
Can you please give a detailed description on how to create the report as per my previous reply.
I'm sure others would like to use the same parameters especially where shift work is involved.
Please remember I am a subscriber on your servers.

Thanks

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

RE: GUIDE: How to use report Time of Day parameter

Mon Sep 26, 2011 8:59 pm

Bolt,

You could use the following SQL statement (MySQL) instead of the red-marked SQL in the initial post. Using the following SQL you get all data rows that has a start time later than the selected [StartTime] (you select 1800 when displaying the report)
or it has an end time earlier than the selected [EndTime] (you select 0600 when displaying the report). That is, you will see all trips that started later than 1800 and all the trips that ended before 0600.

AND (CAST(DATEADD(SECOND, [_UTCOFFSET], Distance01.SamplingStart) AS TIME) > [StartTime]
OR CAST(DATEADD(SECOND, [_UTCOFFSET], Distance01.SamplingEnd) AS TIME) < [EndTime])

Best Regards,
Jonas J
GpsGate Support

Bolt
Posts: 197
Joined: Fri Apr 17, 2009 2:55 am
Location: Australia

RE: GUIDE: How to use report Time of Day parameter

Tue Nov 08, 2011 2:56 am

Hi

I have a requirement to provide a report for engine hours (run time) only, per shift.

I'm assuming "ignition on" would be the appropriate time measured but to get a report running from 0600 - 1800 and also 1800 - 0600 (next day) is the tricky bit. Your post shows how and where to insert the SQL modification for detailed trip and idle but I would appreciate a guide for this new "Engine Hours" report.

Bolt
Posts: 197
Joined: Fri Apr 17, 2009 2:55 am
Location: Australia

RE: GUIDE: How to use report Time of Day parameter

Wed Nov 16, 2011 9:59 am

HI

Any help on my request above.

I've tried many times to insert the SQL lines in different Query's but I just cannot get it to work.

Cheers

Bolt
Posts: 197
Joined: Fri Apr 17, 2009 2:55 am
Location: Australia

RE: GUIDE: How to use report Time of Day parameter

Sun Nov 20, 2011 12:16 am

I get this error when using this alternate statement

ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.46-community]FUNCTION db4014.DATEADD does not exist

However, when using the previous statements in red, I do not get an error but when I check the report, it didn't work.

Maybe, for running from 1800 overnite to the next day 0600, the dates "from" and "to" should be included???


Originally posted by JonasJ

Bolt,

You could use the following SQL statement (MySQL) instead of the red-marked SQL in the initial post. Using the following SQL you get all data rows that has a start time later than the selected [StartTime] (you select 1800 when displaying the report)
or it has an end time earlier than the selected [EndTime] (you select 0600 when displaying the report). That is, you will see all trips that started later than 1800 and all the trips that ended before 0600.

AND (CAST(DATEADD(SECOND, [_UTCOFFSET], Distance01.SamplingStart) AS TIME) > [StartTime]
OR CAST(DATEADD(SECOND, [_UTCOFFSET], Distance01.SamplingEnd) AS TIME) < [EndTime])

Best Regards,
Jonas J
GpsGate Support
<font color="red"></font id="red">



I'm still trying to get "Engine Run Hours".
I added a new "Provider Queries" > "EventProvider01" to include "engine hours"?? Is this the correct way to go?

If so, how do I apply it to the SQL statement?

Cheers

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

RE: GUIDE: How to use report Time of Day parameter

Mon Nov 21, 2011 1:34 pm

Bolt,

The alternative SQL was for MSSQL and the corresponding MySQL syntax would be

AND CAST(DATE_ADD(Distance01.SamplingStart, INTERVAL [_UTCOFFSET] SECOND) AS TIME) > [StartTime]
OR CAST(DATE_ADD(Distance01.SamplingEnd, INTERVAL [_UTCOFFSET] SECOND) AS TIME) < [EndTime])

Regards,
Jonas J

stelazh
Posts: 52
Joined: Tue Sep 27, 2011 2:57 pm

RE: GUIDE: How to use report Time of Day parameter

Sat Dec 03, 2011 1:44 pm

Hello ,
I have the same problem... i have modify the MySQL and T-SQL of DistanceDetailed . Also i have add two parameter Start Time and End Time but i can't generate data. I tried to generate them afer 24 hours but nothing...What can i do please???

stelazh
Posts: 52
Joined: Tue Sep 27, 2011 2:57 pm

RE: GUIDE: How to use report Time of Day parameter

Mon Dec 05, 2011 1:59 pm

How is the commnad in SQL ( code ) for generate data for all week except saturday and sunday ??? Please can you help me ???

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

RE: GUIDE: How to use report Time of Day parameter

Mon Dec 05, 2011 2:09 pm

Hi stelazh,

Since I don't know enough details of your problem (I would need to know the database type you have and the exact SQL you are trying to execute) I can only give some general advice.

1. You only need to modify the SQL of your database type.

2. You can quickly test the SQL by selecting parameter value and clicking Execute (if you have processed data). Remember to select the period/application/tag in the parameters for which you have processed data before (you can process data by logging in to an application and creating a report that uses the Distance01 data provider, TR1000 for example). If nothing shows up in the result table, try removing complex SQL until you get some result rows in the result table. Then add more complex SQL from there.

Best Regards,
Jonas J

stelazh
Posts: 52
Joined: Tue Sep 27, 2011 2:57 pm

RE: GUIDE: How to use report Time of Day parameter

Mon Dec 05, 2011 2:16 pm

Thank you so much ...
Now please help me with this please if you can :

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 WHEN FatPointState = 'idle' THEN TIME_TO_SEC(TIMEDIFF(Distance01.SamplingEnd, Distance01.SamplingStart)) ELSE 0 END AS DurationIdle,
CASE WHEN FatPointState = 'run' THEN TIME_TO_SEC(TIMEDIFF(Distance01.SamplingEnd, Distance01.SamplingStart)) ELSE 0 END AS DurationTrip,
CASE WHEN FatPointState = 'idle' THEN 1 ELSE 0 END AS IdleCount,
CASE WHEN FatPointState = 'run' THEN 1 ELSE 0 END AS TripCount,
Distance01.SamplingStart,
Distance01.SamplingEnd,
Distance01.UserID,
Distance01.StartAddress,
Distance01.StopAddress,
Distance01.DistanceGps,
Distance01.DistanceOdo,
Distance01.MaxSpeed,
Distance01.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'
AND CAST(DATE_ADD(Distance01.SamplingStart, INTERVAL [_UTCOFFSET] SECOND) AS TIME) > [StartTime]
AND CAST(DATE_ADD(Distance01.SamplingEnd, INTERVAL [_UTCOFFSET] SECOND) AS TIME) < [EndTime]


In this query of DistanceDetailed report TR1001 ,i want to modify the sql code in mode to expect saturday and sunday from my data.

Can you help me please???

stelazh
Posts: 52
Joined: Tue Sep 27, 2011 2:57 pm

RE: GUIDE: How to use report Time of Day parameter

Mon Dec 05, 2011 2:22 pm

I think need to use this function DAYOFWEEK(‘date’) but i don't know how.

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

RE: GUIDE: How to use report Time of Day parameter

Mon Dec 05, 2011 2:36 pm

Hi stelazh,

I recommend that you start playing around and learning the database date and time functions for doing such things. For MySQL, look at
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

For MSSQL, look at
http://msdn.microsoft.com/en-us/library/ms186724.aspx

GpsGate Server stores datetime columns in UTC, so in order to get the correct date for your application (local time) you need to add the UTC offset to the column you are using (e.g. Distance01.SamplingStart).
The UTC offset is available in seconds as the variable [_UTCOFFSET]. It is parsed by GpsGate Server and replaced by the UTC offset of your application as set by the time zone setting.

For example in MySQL,
DAYNAME(Distance01.SamplingStart)
would give you the name of the day in UTC time.

and
DAYNAME(DATE_ADD(Distance01.SamplingStart, INTERVAL [_UTCOFFSET] SECOND))
would give you the name of the day in application local time.

So you could use this function to select rows from Distance01 data provider where the name of the day is not 'Saturday' or 'Sunday'. Something like:

AND DAYNAME(CAST(DATE_ADD(Distance01.SamplingStart, INTERVAL [_UTCOFFSET] SECOND) AS TIME)) <> 'Saturday'
AND DAYNAME(CAST(DATE_ADD(Distance01.SamplingStart, INTERVAL [_UTCOFFSET] SECOND) AS TIME)) <> 'Sunday'

The actual name of the day would depend on the language settings of your database, you will have to play around to get it right.

Best Regards,
Jonas J

Return to “GpsGate for Developers”