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

GUIDE: Add custom fields to report EV1000

Wed Jul 31, 2013 7:49 pm

GUIDE: Add custom fields to report EV1000

Using GpsGate v4.0 read this guide instead (much easier now!)
http://gpsgate.com/blogComment/displayi ... le_reports

Below how to add custom fields using GpsGate v3.1

EV1000 makes a report from event rules. This is a very powerful way of making reports. However in the standard report no variables are included. This guide describes how you can add columns to EV1000 which can for example include GpsGate Variables from the Device mapper.

In this example we add start and end value for GpsGate Variable CustomAnalog1 as two extra columns to the EV1000 report.

This guide assumes you are familiar with the EV1000 report, that you know about Event Rules, and how to activate and use a report in an application.

Create new Data Provider

First we need to customize the data provider EventProvider01 and add data for the columns we need.

1. Login to SiteAdmin and go to Reporting
2. Go to "Manage Data Providers" (left menu).
3. Click on "New"
4. Select "Copy of EventProvider01" in drop down and click "Next".
5. Change name to "EventProvider02".

Now we want to add extra data to the provider, data that we later will include in the Report Definition.

1. Under "Select which data to be collected", select "Value" in drop down.
2. Set Name to StartAnalog1
3. Select CustomAnalog1 as Field
4. Select "First" in Option drop down.
5. Click "Insert".
6. Now add one more "Value" column, name it EndAnalog1 and select "Last" as option.

As you can see there are many other types of columns, like Max, Min, SUm and Avarge.

7. Now click "Save".

If you have test track data, you can check what the provider returns using the function under "Test your provider". See also "Tools for testing" for more tips on testing.

Create new Report Definition

1. Login to SiteAdmin and go to Reporting
2. Go to "Manage Reports" (left menu).
3. Click on "New"
4. Select "EV1000 Event Rule" as Template in drop down and click "Next"
5. Change name to "Analog Event"
6. Click Save.

Now you need to change the SQL Query to get data from EventProvider02

1. Click on "Query Editor".
2. Click on "QEventReport"

3. Replace EventProvider01 with EventProvider02
4. Add EventProvider02.StartAnalog1, EventProvider02.EndAnalog1 in the SELECT statement.
5. Save.

Like this:

Code: Select all

SELECT 
	EventProvider02.StartAnalog1,
	EventProvider02.EndAnalog1,
	CAST([PeriodStart] AS DATETIME) AS PeriodStart,
	CAST([PeriodEnd] AS DATETIME) AS PeriodEnd,
	[ApplicationID] AS ApplicationID,
	Users.Name,
	Users.Username,
	EventProvider02.SamplingStart,
	EventProvider02.SamplingEnd,
	TIME_TO_SEC(TIMEDIFF(EventProvider02.SamplingEnd,  EventProvider02.SamplingStart)) AS Duration,
	EventProvider02.EventRuleID,
	EventProvider02.RuleName,
	EventProvider02.EventArgument,
	EventProvider02.DistanceGps,
	EventProvider02.DistanceOdo,
	EventProvider02.StartAddress,
	EventProvider02.StopAddress,
	EventProvider02.EventState,
	EventProvider02.UserID
FROM EventProvider02
	JOIN Users ON Users.UserID = EventProvider02.UserID
WHERE
	EventProvider02.EventState != 'Off' 
	AND EventProvider02.UserID IN (SELECT TagUsers.UserID FROM TagUsers WHERE TagUsers.TagID IN [Tag])
	AND EventProvider02.EventRuleID IN [EventRule]
	AND (
		EventProvider02.SamplingStart BETWEEN [PeriodStart]  AND [PeriodEnd] OR 
		EventProvider02.SamplingEnd BETWEEN [PeriodStart] AND [PeriodEnd]
	)
ORDER BY 
	EventProvider02.EventRuleID, 
	EventProvider02.UserID, 
	EventProvider02.SamplingStart

5. Click "Back"

Now it is time to insert new columns for StartAnalog1 and EndAnalog1 in the report layout.

1. Right click on the Report Layout and select "Insert Column"
2. Use right click on the cells to edit them. Insert [StartAnalog1]
3. Create new column for [EndAnalog1]
4. Save.

Activate report

You activate your new report as any other report. For an example see how EV1000 is activated in an application http://gpsgate.com/reports/ev1000

Tools for testing

1. Display the report in VehicleTracker to get the final layout. To be logged into SiteAdmin and VehicleTracker at the same time use two different browsers. E.g. Chrome + Firefox

2. When you make a change to any event rule, you need to mark reporting data for re-processing to have the reports updated. More info:
topic.asp?TOPIC_ID=12914

3. How to use SimOne to test Event Rules
topic.asp?TOPIC_ID=14038



Regards,
Johan

GpsGate Support

Hus
Posts: 30
Joined: Thu Jan 05, 2012 9:22 pm

RE: GUIDE: Add custom fields to report EV1000

Sat Aug 03, 2013 11:39 pm

hi
thanks Johan this was helpful
i want to ask about the start analog1 and end analog1 are they related to Event rule trigger condition from application ?
on other words if this analogue was speed and event rule trigger expression was speed more than some value will the start analog1 be the same value triggered the Event rule ? is the end analog1 be the same end of event rule ?

if i put in report "from site admin" in this field the function { max} would the result be the max speed in this event period "local max" or it would be the max speed ever reached during report time? "global max"
regards

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

RE: GUIDE: Add custom fields to report EV1000

Sun Aug 04, 2013 8:48 pm

Hus: When you tested, what were the results?

Tools for testing

1. Display the report in VehicleTracker to get the final layout. To be logged into SiteAdmin and VehicleTracker at the same time use two different browsers. E.g. Chrome + Firefox

2. When you make a change to any event rule, you need to mark reporting data for re-processing to have the reports updated. More info:
topic.asp?TOPIC_ID=12914

3. How to use SimOne to test Event Rules
topic.asp?TOPIC_ID=14038

Regards,
Johan

GpsGate Support

Hus
Posts: 30
Joined: Thu Jan 05, 2012 9:22 pm

RE: GUIDE: Add custom fields to report EV1000

Wed Aug 07, 2013 11:56 pm

hi
no results , runtime error on both tests. the data provider and execute the report. did i make any mistakes here ?
regards

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

RE: GUIDE: Add custom fields to report EV1000

Tue Nov 05, 2013 6:28 pm

Here is how to do the same thing using GpsGate v4.0 (now much easier!)
http://gpsgate.com/blogComment/displayi ... le_reports

Regards,
Johan

GpsGate Support

Hus
Posts: 30
Joined: Thu Jan 05, 2012 9:22 pm

RE: GUIDE: Add custom fields to report EV1000

Sat Dec 07, 2013 11:49 am

thanks that was an ultimate solution [:)]

Return to “GpsGate for Developers”