Browse Search Login
Home : Supported Variable Configurations for Interface Q12180
Q12195 - HOWTO: Supported Variable Configurations for Interface Q12180

Wonderware Historian Variable Cross Referencing

To configure a variable to summarize values from Wonderware Historian (InSQL), select Edit/View Variables in the client and select the Interface tab.  Then click Interface To radio button. The drop down box next to the option is now enabled, click the drop down arrow and choose the Wonderware Historian interface name (name given when configuring the interface).

Note: Tag is CASE Sensitive

FIELDS

Tag: The SCADA Tag to import data from. Note: Tag is CASE Sensitive.  Data will be pulled from the Wonderware Historian table named History by default.  To specify a different table in the Historian to pull data from, simply put the table name in square brackets ("[tablename]") followed by a dot (".") then the tag name.   Note:  Wonderware InSQL version 7.1 did not have a "History" Table/View, therefore if using 7.1 use the v_AnalogHistory table.  Later versions (8.x, 9.x,...) of Wonderware Historian have the History view.  

Example Tag Settings:

WTP_WW_FLO_VLV.PosEU - Will pull data from the History table in Wonderware Historian where TagName = WTP_WW_FLOW_VLV.PosEU

[v_AnalogHistory].WTP_RAW_TURB.TuEU - Will pull data from the v_AnalogHistory view in Wonderware Historian where TagName = WTP_RAW_TURB.TuEU

If the Browser is configured (see Browser Setup), the ? button can be used to browse the available tags:

 

The tag browser lists all tags listed in the Historian Tag table.  The TagType indicates the type of data (analog, discrete, etc...).  The following table describes the TagType field (SELECT * FROM TAGTYPE):

TagType TagTypeName
1 Analog
2 Discrete
3 String
5 Event
7 Structure

Select a tag and then click OK and you will be back at the Edit Variable screen.  Click on the column headers to sort by column and use the Search function to filter the list to find a particular tag (Search looks at both the TagName and Description and is not case sensitive). 

 

NOTES:  You do not need to specify the tablename if you want to pull data from the History Table/View.  If you specify the table/view in square brackets, the table MUST have the following fields:  DateTime, Value, TagName, wwRetrievalMode, wwRowCount.  The History, v_AnalogHistory, and v_DiscreteHistory views have these required fields. 

 Statistic:  Sets how to summarize the Wonderware data for import. The following table shows the supported statistics with the actual query used for a daily parameter variable with Start Time set to 00:00 and Stop Time of 23:59.  Tag is set to 'ETP_RW_FlowTotal.FlowTotalOS', therefore the History View will be used.   

Stat

Description

 SQL Query                                                                                                        

AVERAGE

Take the average of the data points

select Avg(Value) from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null
AND wwRetrievalMode = 'average' AND wwRowCount = 1 AND wwTimeStampRule='start'
AND DateTime >= '2011-01-04 00:00:00' AND DateTime < '2011-01-05 00:00:00'

AVERAGE.CYCLIC

 Average using Cyclic Retrieval Mode

select Avg(Value) from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null
AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 1440
AND DateTime >= '2011-01-04 00:00:00' AND DateTime < '2011-01-05 00:00:00'

NOTE: This was used in versions prior to 1.2.4 for Average statistic.

TOTAL

Take the sum total of all values

select  SUM(Value)  from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null 
AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 1440
AND DateTime > '2011-01-04 00:00:00' AND  DateTime <= '2011-01-05 00:00:00'

MINIMUM

Get the minimum value

select  MIN(Value)  from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null 
AND wwRetrievalMode = 'minimum' AND wwRowCount = 1 AND wwTimeStampRule='start'
AND DateTime >= '2011-01-04 00:00:00' AND  DateTime < '2011-01-05 00:00:00'

MINIMUM.CYCLIC

 Minimum using Cyclic Retrieval Mode

select  MIN(Value)  from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null 
AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 1440
AND DateTime >= '2011-01-04 00:00:00' AND  DateTime < '2011-01-05 00:00:00'

NOTE: This was used in versions prior to 1.2.4 for Minimum statistic.

MAXIMUM

Get the maximum value

select  MAX(Value)  from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null 
AND wwRetrievalMode = 'maximum' AND wwRowCount = 1 AND wwTimeStampRule='start'
AND DateTime >= '2011-01-04 00:00:00' AND  DateTime < '2011-01-05 00:00:00'

MAXIMUM.CYCLIC

 Maximum using Cyclic Retrieval Mode

select  MAX(Value)  from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null 
AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 1440
AND DateTime >= '2011-01-04 00:00:00' AND  DateTime < '2011-01-05 00:00:00'

NOTE: This was used in versions prior to 1.2.4 for Maximum statistic.

MAXCOUNTER

 Maximum using Counter Retrieval Mode

select MAX(Value) from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null
AND wwRetrievalMode = 'Counter' AND wwTimeStampRule='start'
AND DateTime >=  '2011-01-04 00:00:00' AND DateTime < '2011-01-05 00:00:00'

FIRST

Get the first value. Note: if FIRST is used with a Text Parameter it will import the DateTime of the first value.

select TOP 1 Value, DateTime from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null
AND wwRetrievalMode = 'Full' AND wwTimeStampRule='start'
AND DateTime >= '2011-01-04 00:00:00' AND DateTime < '2011-01-05 00:00:00' ORDER BY DateTime asc

FIRST.CYCLIC

 First using Cyclic Retrieval Mode

select  TOP 1 Value, DateTime  from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null 
AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 1440
AND DateTime >= '2011-01-04 00:00:00' AND  DateTime < '2011-01-05 00:00:00'  ORDER BY DateTime asc

NOTE: This was used in versions prior to 1.2.4 for First statistic.

LAST

Get the last value. Note: if LAST is used with a Text Parameter it will import the DateTime of the last value.

select TOP 1 Value, DateTime from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null
AND wwRetrievalMode = 'Full' AND wwTimeStampRule='start'
AND DateTime >= '2011-01-04 00:00:00' AND DateTime < '2011-01-05 00:00:00' ORDER BY DateTime desc

LAST.CYCLIC

 Last using Cyclic Retrieval Mode

select  TOP 1 Value, DateTime  from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null 
AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 1440
AND DateTime >= '2011-01-04 00:00:00' AND  DateTime < '2011-01-05 00:00:00'  ORDER BY DateTime desc

NOTE: This was used in versions prior to 1.2.4 for Last statistic.

DIFF

Calculate the difference between the first and last values. If the first value is larger than the second then it will perform the following calculation: (10 ^ (Ceil(LOG(first_value) / LOG(10)))) - first_value + last_value

Ceil will cause the value to round up
  1. Get FIRST value using Retrieval Mode Full
  2. Get LAST value using Retrieval Mode Full
  3. RESULT = LAST - FIRST
  4. IF RESULT < 0 THEN (10 ^ (Ceil(LOG(first_value) / LOG(10)))) - first_value + last_value

DIFF.CYCLIC

 Difference using Cyclic Retrieval Mode
  1. Get FIRST value using Retrieval Mode Cyclic
  2. Get LAST value using Retrieval Mode Cyclic
  3. RESULT = LAST - FIRST
  4. IF RESULT < 0 THEN (10 ^ (Ceil(LOG(first_value) / LOG(10)))) - first_value + last_value

RANGE

Calculate the absolute value of the difference between the minimum and maximum values
  1. Get MIN value using Retrieval Mode Minimum
  2. Get MAX value using Retrieval Mode Maximum
  3. Get ABS(MIN - MAX)

RANGE.CYCLIC

 Range using Cycle Retrieval Mode
  1. Get MIN value using Retrieval Mode Cyclic
  2. Get MAX value using Retrieval Mode Cyclic
  3. Get ABS(MIN - MAX)

INVENTORY*

Return the total use based on a tank level signal. For example, the level is 10 feet at midnight and slowly goes down to 2 feet at 2:00 PM, then gets filled to 15 feet and slowly goes down to 8 feet at midnight. The inventory function would return ((10 - 2) + (15 - 8)) = 15.
  1. select  DateTime, Value from dbo.History
    where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null 
    AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 1440
    AND DateTime >= '2011-01-04 00:00:00' AND  DateTime < '2011-01-05 00:00:00'
  2. Loop through the records returned and sums the downward trends greater than the Deadband

MA10M

Intended to be used with a minutely variable type.  Returns a 10 minute moving average. 10 minutes is subtrated from the start time, and the end time is left as is.

select  AVG(Value) from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null 
AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 11
AND DateTime >= '2011-01-04 07:50:00' AND  DateTime < '2011-01-04 08:01:00'  ORDER BY DateTime asc

MAXTIME

 Returns the Date and Time of the maximum value. Intended to be used with Text Parameter Variables.  The 'Date' portion is returned in the format of your Windows Regional and Language Options, Short Date Format. The time portion is returned in hh:mm:ss format.

select TOP 1 convert(varchar(30),Datetime, 120), Value from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null
AND wwRetrievalMode = 'Maximum' AND wwTimeStampRule='start' AND DateTime >= '2011-01-04 00:00:00' AND DateTime < '2011-01-05 00:00:00'

MAXTIME.CYCLIC

 Returns the Date and Time of the maximum value using Retrieval Mode Cyclic. Intended to be used with Text Parameter Variables.  The 'Date' portion is returned in the format of your Windows Regional and Language Options, Short Date Format. The time portion is returned in hh:mm:ss format.

select  TOP 1 DateTime, Value from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null 
AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 1440
AND DateTime >= '2011-01-04 00:00:00' AND  DateTime < '2011-01-05 00:00:00'  ORDER BY VALUE desc

NOTE: This was used in versions prior to 1.2.4 for Maxtime statistic.

MAXTIMECOUNTER

 Returns the Date and Time of the maximum value using Retrieval Mode Counter. Intended to be used with Text Parameter Variables.  The 'Date' portion is returned in the format of your Windows Regional and Language Options, Short Date Format. The time portion is returned in hh:mm:ss format. select  TOP 1 CONVERT(VARCHAR(30), DateTIME, 120), Value from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null 
AND wwRetrievalMode = 'counter' AND wwTimeStampRule='start'
AND DateTime >= '2011-01-04 00:00:00' AND  DateTime < '2011-01-05 00:00:00'  ORDER BY VALUE desc

MINTIME

 Returns the time of the minimum value. Intended to be used with Text Parameter Variables. The 'Date' portion is returned in the format of your Windows Regional and Language Options, Short Date Format. The time portion is returned in hh:mm:ss format.

select TOP 1 convert(varchar(30),Datetime, 120), Value from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null
AND wwRetrievalMode = 'Minimum'  AND wwTimeStampRule='start' AND DateTime >= '2011-01-04 00:00:00' AND DateTime < '2011-01-05 00:00:00'

MINTIME.CYCLIC

 Returns the Date and Time of the minimum value using Retrieval Mode Cyclic. Intended to be used with Text Parameter Variables.  The 'Date' portion is returned in the format of your Windows Regional and Language Options, Short Date Format. The time portion is returned in hh:mm:ss format.

select  TOP 1 DateTime, Value from dbo.History
where TagName = 'ETP_RW_FlowTotal.FlowTotalOS' AND value is not null 
AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 1440
AND DateTime >= '2011-01-04 00:00:00' AND  DateTime < '2011-01-05 00:00:00'  ORDER BY VALUE ASC

NOTE: This was used in versions prior to 1.2.4 for Mintime statistic.

* The INVENTORY statistic will ignore non-numeric values and will hold last value for the time slot difference. In the following example, the 12:03 AM value will be skipped and the difference will be between 9.67 (12:02 AM) and 9.25 (12:04 AM) when evaluating the 12:04 AM inventory value.

1/1/2014

 10.01
1/1/2014 12:01:00 AM 9.85
1/1/2014 12:02:00 AM 9.67
1/1/2014 12:03:00 AM Bad Reading
1/1/2014 12:04:00 AM

9.25

1/1/2014 12:05:00 AM 9.03

 

Scale Factor: This is the value to multiply the result by when using parameter variable types. Commonly used to convert from one unit base to another. For example to convert gallons per minute (GPM) to gallons per day (GPD), set the scale factor to 1440 (1440 minutes per day).



Filter: All three fields must have numeric values or the word None (as shown). The Low Range and High Range will crop data from the source. For example to eliminate negative numbers from a particular tag, set the Low Range to 0 (zero) - this will get any values equal to or greater than 0 (zero). The Deadband is used for the statistic Inventory and will elliminate noise levels up to the value specified. For example, if you enter .5 next to Deadband, any value change of .5 or less, will be ignored.



Start/Stop Time:   Sets the time period within the variable's frequency to summarize data for.

    • Start Time will set the beginning of the time slot for this variable.
    • Stop Time will set the ending time for the time slot.
    • Same Day as Start is only used by daily variables and it means the stop time is on the same day as the start time.
    • Day After Start is also only used by daily variables and it means the stop time is a day after the start time.

Note: Start and Stop Time can be used for daily detail variables (i.e. Hourly, 15 Minute, etc...) to set the part of each slot to get data from.  For example, for an hourly variable, set to Start Time to 00:15 and Stop Time to 00:45 to only look at data between 15 minutes and 45 minutes of each hour. 

 

Filter Data:  Used to filter what times the SCADA data should be analyzed.  Example, you want to get the maximum filter turbidity but ONLY WHEN the Filter to Waste value is closed (i.e. the tag F1_WASTE_VALVE_POS = 0). 

Condition Description
<, >, =, <=, >=, <> Summarize values based on statistic when the Filter Tag is <, >, =, <=, >=, or <> to the value specified.
CYCLESTO Summarize values based on statistic when the Filter Tag changes to Value specified.
ACT(n) After Cycles To - Summarize values based on statistic looking at data that is n minutes after the Filter Tag changes to the value specified.   
BCT(n)

Before Cycles To - Summarize values based on statistic looking at data that is n minute before the Filter Tag changes to the value specified.
NOTE: There is a work around setting for this filter function to work with the City of Ventura's Interface.  The setting: BCT_TWEAK_VENTURA must be set to 1.  Ex: 1

GOES>(n) Summarize values based on statistic looking at data that is offset n minutes from when the Filter Tag goes above the value specified.  N of Zero (0) means get data in the same minute, 1 means 1 minute after, -2 means 2 minutes before.
GOES<(n) Summarize values based on statistic looking at data that is offset n minutes from when the Filter Tag goes below the value specified.  N of Zero (0) means get data in the same minute, 1 means 1 minute after, -2 means 2 minutes before.

NOTE: The CYCLESTO, ACT, etc... conditions are not on the pulldown list, simply type the condition. 

How filtering works:

The Interface grabs data for the source tag and the filter tag once a minute using retrieval mode of cyclic and pairs up the values for each minute.  It then loops thru the records and only analyzes source tag values when the fitler tag meets the criteria. 

Example: The following example gets the Minimum Chlorine Residual when the Tank Level is above 16.5:

Query:

select source.timestamp,savg,favg from
(select MIN(Value) as savg, cast (cast(month(DateTime) as varchar) + '/' + datename(day,DateTime) + '/' +datename(year,DateTime) + ' ' +  datename(hour,DateTime) + ':' + datename(minute,DateTime) + ':00' as datetime) as timestamp
from dbo.AnalogHistory
where DateTime >= '2015-04-29 00:00:00' and DateTime < '2015-04-30 00:00:00' 
and TagName='NPS_36IN_TTL_CL_RES.PvEU'  AND value is not null  AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 1440
group by cast (cast(month(DateTime) as varchar) + '/' + datename(day,DateTime) + '/' +datename(year,DateTime) + ' ' +  datename(hour,DateTime) + ':' + datename(minute,DateTime) + ':00' as datetime)) source
left outer join 
(select MIN(Value) as favg, cast (cast(month(DateTime) as varchar) + '/' + datename(day,DateTime) + '/' +datename(year,DateTime) + ' ' +  datename(hour,DateTime) + ':' + datename(minute,DateTime) + ':00' as datetime) as timestamp 
from dbo.AnalogHistory where DateTime >= '2015-04-29 00:00:00' and DateTime < '2015-04-30 00:00:00'
and TagName='WFN_WELL37_1B.PumpingLevelEU'  AND value is not null  AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 1440
group by cast (cast(month(DateTime) as varchar) + '/' + datename(day,DateTime) + '/' +datename(year,DateTime) + ' ' +  datename(hour,DateTime) + ':' + datename(minute,DateTime) + ':00' as datetime)) filter
on source.timestamp=filter.timestamp

 

Query Output:
Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 		 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 5/8/2009 3:57 PM.
Last Modified on 3/17/2021 4:24 PM.
Last Modified by Ryan Rhoten.
Article has been viewed 10463 times.
Rated 0 out of 10 based on 0 votes.
Print Article
Email Article