sg09
Posts: 95
Joined: Mon Feb 01, 2010 12:33 am

Site visit report + mileage

Thu Apr 12, 2012 1:40 pm

Hi,
we have these vehicles that visit only 5 sites. We would like to generate a report showing when the vehicle left one site and reached the other site, time and km traveled. Sites can be POIs or Geofences.
What would be the best report to accommodate this ?

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

RE: Site visit report + mileage

Thu Apr 12, 2012 1:44 pm

Please take a look at the "Time on Site" reports TS100x here:
http://gpsgate.com/reports

Regards,
Johan

GpsGate Support

sg09
Posts: 95
Joined: Mon Feb 01, 2010 12:33 am

RE: Site visit report + mileage

Thu Apr 12, 2012 1:54 pm

Hi,
i've been testing those for two days but they don't provide KMs traveled from Site A to Site B (or POI A to POI B)
The closest one is the Trip & Idle Detailed, but it must have only POIs or Geofences as start and stop address. Any stops in between must be ignored.

sg09
Posts: 95
Joined: Mon Feb 01, 2010 12:33 am

RE: Site visit report + mileage

Thu Apr 12, 2012 4:21 pm

I've been trying modifying the TS1003 Time on Site (Vehicle) report, and added the DistanceGps field, but values seem not to be accurate. Values are like: 694.167700, 763.278983, 816.415302 while they should be approximately 32Km.

sg09
Posts: 95
Joined: Mon Feb 01, 2010 12:33 am

RE: Site visit report + mileage

Fri Apr 13, 2012 12:39 pm

Hello,
is possible to modify the Trip & Idle report to only show starts/stops from/to POIs or Geofences, or add mileage to TS1003 report?

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

RE: Site visit report + mileage

Fri Apr 13, 2012 8:41 pm

Hi sg09,

It should be possible to get milage between POIs by modifying the report query SQL to count milage when the event rule state is 'off' (not inside POI). I will give more input on Monday.

Best Regards,
Jonas J
GpsGate Support

sg09
Posts: 95
Joined: Mon Feb 01, 2010 12:33 am

RE: Site visit report + mileage

Mon Apr 16, 2012 10:27 am

Hi Jonas,
i will be waiting for your reply. Thank you.

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

RE: Site visit report + mileage

Mon Apr 16, 2012 2:42 pm

sg09,
In SiteAdmin -> Reporting you can open up your report SQL query
and see the line
EventProvider01.EventState != 'Off'

The POI report uses an event rule to create report data rows. Rows are created when the event rule is true (inside POI. The value of EventProvider01.EventState is 'Finished') or when the event rule is false (outside POI. The value of EventProvider01.EventState is 'Off').

Notice that the current SQL only reads data rows when the event rule is true.

To solve this problem you should get to learn the MySQL conditional statement CASE. You can see an example of how the CASE statement can be used from the TR1000 Trip & Idle (Detailed) report:

CASE Distance01.FatPointState WHEN 'idle'
THEN TIME_TO_SEC(TIMEDIFF(Distance01.SamplingEnd, Distance01.SamplingStart))
ELSE 0 END AS IdleDuration,
...

This way, IdleDuration gets the value 0 for rows when FatPointState is not 'Idle', otherwise it gets the duration.

So for the Time on Site report you could remove the SQL statement
EventProvider01.EventState != 'Off'

so that all rows are read, and use CASE statements to create named columns for the 'Off' state (count your milage when outside POI) and 'Finished' state (count duration inside POI etc).

Best Regards,
Jonas J
GpsGate Support

sg09
Posts: 95
Joined: Mon Feb 01, 2010 12:33 am

RE: Site visit report + mileage

Wed Apr 18, 2012 12:25 pm

Hi Jonas,
i;ve been trying to make some modifications on the report but unsuccessful. I tried removing the EventProvider01.EventState != 'Off' but i still get the same data.
Can you please send me an example ?

I did also try modifying the TR1000 Trip & Idle (Detailed) by only allowing Geofence as geocoder, and it is almost what i'm looking for, but with the exception that there are some rows with empty addresses, either the start address or the end address. Is there any what to group these rows ?

sg09
Posts: 95
Joined: Mon Feb 01, 2010 12:33 am

RE: Site visit report + mileage

Wed Apr 18, 2012 1:42 pm

I modified the query of the TR1000 by only showing "run" rows. How can I group rows now to eliminate those with "empty" start/stop address and also add/sum Duration and GpsDistance ?

Max Speed and Avg Speed are not important.

Please see image below:
Image

http://www.flickr.com/photos/77336744@N05/6943789718/in/photostream

sg09
Posts: 95
Joined: Mon Feb 01, 2010 12:33 am

RE: Site visit report + mileage

Fri Apr 20, 2012 3:12 pm

Hi Jonas,
We need some more help on modifying the report to show what we need. Will you find some time and send us an example ?

Thank you.

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

RE: Site visit report + mileage

Mon Apr 23, 2012 4:46 pm

sg09,

It is strange that you get the same data (you should get more rows) when removing the line
EventProvider01.EventState != 'Off'

Double check that you modified the SQL for the correct database type? If your database is MSSQL you must first select that radiobutton so that the correct SQL text is modified (Note that MySQL is selected by default).

If the event rule is properly set up you should now also get the data rows where the vehicle is NOT inside a POI (that is, it is between POIs). This type of report depends on that the event rule is properly set up, i.e. that the expression represents the condition (Inside POI) that you want, and nothing else.

To test this, you can see the raw report data from the reporting data store (before localization of time & units etc) in site admin -> Reporting tab -> Report Queries -> and selecting period start, period stop, tag etc for parameters and clicking "Execute".

To keep things simple you can try removing irrelevant SQL columns and try and create only the distance when EventProvider01.EventState == 'Off' and see that you get the expected resulting distance between POIs. When you get this to work you can add back the other columns.

For example, you can have the lines

CASE EventProvider01.EventState WHEN 'Off'
THEN Distance01.DistanceGps ELSE 0 END AS OutsidePOIMilage,
CASE EventProvider01.EventState WHEN != 'Off'
THEN Distance01.DistanceGps ELSE 0 END AS InsidePOIMilage

Test it directly in SiteAdmin by clicking "Execute" with some selected parameters (you must previously have processed data for this time period and user).



Best Regards,
Jonas J
GpsGate Support

sg09
Posts: 95
Joined: Mon Feb 01, 2010 12:33 am

RE: Site visit report + mileage

Tue Apr 24, 2012 7:31 pm

Hello Jonas,
Please find below query string that im using. The closes report is the one with geofence exit rule, but i'm still haveing two issues:

1.Start address is empty when start and stop address are different.
2.Kilometers have 6 digits after coma,how can i round it on the report.

SELECT
CAST([PeriodStart] AS DATETIME) AS PeriodStart,
CAST([PeriodEnd] AS DATETIME) AS PeriodEnd,
round(EventProvider01.DistanceGps / 1000,2) AS Kilometers,
Users.Name,
Users.Username,
EventProvider01.SamplingStart,
EventProvider01.SamplingEnd,
EventProvider01.RuleName,
EventProvider01.StartAddress,
EventProvider01.StopAddress
FROM EventProvider01
JOIN Users ON Users.UserID = EventProvider01.UserID
WHERE
EventProvider01.UserID IN (SELECT TagUsers.UserID FROM TagUsers WHERE TagUsers.TagID IN [Tag])
AND EventProvider01.EventRuleID IN [EventRule]
AND (
EventProvider01.SamplingStart BETWEEN [PeriodStart] AND [PeriodEnd] OR
EventProvider01.SamplingEnd BETWEEN [PeriodStart] AND [PeriodEnd]
)
ORDER BY
EventProvider01.EventRuleID,
EventProvider01.UserID,
EventProvider01.SamplingStart

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

RE: Site visit report + mileage

Tue Apr 24, 2012 8:17 pm

sg09,
Please explain in more detain what you mean when you say:
"1.Start address is empty when start and stop address are different."
What is it that you want to do considering this?

2. Rounding is done using the measurement file found in IIS\Resources\Languaged\en-us.measure

In that file, copy & paste the XML for TRACK_DISTANCE, and rename it to REPORT_TRACK_DISTANCE. Change the Format to for example
<Format Format="0.0" />
to round to one digit after the decimal point.
For more info on numering format strings see
http://msdn.microsoft.com/en-us/library/0c899ak8.aspx

Save the measure file. Since measures are cached in the IIS process you may have to recycle the IIS application pool for the changes to take effect.

Now, in the report layout in any the cells that show distance, you should now change unit from TRACK_DISTANCE to your custom formatted REPORT_TRACK_DISTANCE.


Best Regards,
Jonas J
GpsGate Support

sg09
Posts: 95
Joined: Mon Feb 01, 2010 12:33 am

RE: Site visit report + mileage

Wed Apr 25, 2012 2:16 pm

Hi Jonas,
i want to create a report that shows only POI Names or Geofence Names under start and stop address, whichever is easier, but no other rows with empty start/stop address or different than POIs/Geofences. Customer wants to see mileage when vehicle left Site A and reached Site B.

Thank you

Return to “GpsGate for Developers”