Advanced Formula Reference

This section contains additional resources for building advanced formulas. Follow the links below.

Importing Libraries

Libraries must be imported into the Script Editor in order to write Advanced Scripts. The following libraries can be used to cover most situations: Microsoft.VisualBasic, Microsoft.Win32, System,System.Math, System.IO, Microsoft.VisualBasic.FileIO.FileSystem, Microsoft.VisualBasic.Constants, System.DateTime, PARCCalcScript, Opc.Da.Quality, Opc.Hda.

To add these libraries, add the following code to the top of a script:

Imports Microsoft.VisualBasic

Imports Microsoft.Win32

Imports System

Imports System.Math

Imports System.IO

Imports Microsoft.VisualBasic.FileIO.FileSystem

Imports Microsoft.VisualBasic.Constants

Imports System.DateTime

Imports PARCCalcScript

Imports Opc.Da.Quality

Imports Opc.Hda

Structuring an Advanced Formula

An Advanced Formula uses VB.NET syntax, which means that the script will be subdivided into a module and then within the module into subroutines (subs). It is inside of these subroutines where the manipulation of tag values occurs.

The create a module and a subroutine, add the following code:

Module PARCModule

Public Sub Main()

End Sub

End Module

The script can contain only one module, and the module must be named “PARCModule”. This code belongs after the library imports.

C Object Methods and Properties

The C Object is used to return and manipulate tag values.

AddInputTag(string UTag): Adds in the given (fully qualified) tag name for use when pulling normalized data.

AddOutValues: Takes type Opc.Hda.ItemValueCollection and builds an output array. Generally using OutValues.Add to build an output array with PARCValues is easier than using AddOutValues.

CurPosition: An integer that functions similar to IV. If not given an array index, it will default to pulling values from within an array of values. It needs to be manually incremented up within code if used.

CurTime: A DateTime representing the local time at execution of the script.

DataUTag: Returns the fully qualified name of the input tag.

DataTag(int Index):Takes an integer representing an array element at its argument and returns that value for the tag the script is attached to.

EndTime: Returns the HDA time that represents the right/ending bound on time for which a script has been called. Use AbsoluteTime method to convert the HDA time to a readable format.

iifobj (bool BoolExpr, object TrueValue, object FalseValue): Returns the TrueValue object if the Boolean BoolExpr is returns true, otherwise returns the FalseValue object.

IQ(), IQ(string UTag), IQ(string UTag, int Index): Returns the quality for either the given UTag at the given index in the Normalized array. If the index isn’t specified then CurPosition is used as the index.

IVO(string UTag, int Index): Returns the value of the given tag at the indicated index.

GetProcessTag(string uTag): Retrieves process information about an input tag. Does not currently have a stable wrapper in the script editor and thus cannot be used.

GetTagAttribute(string uTag, string attributeName, ref object attributeValue): Gets attributes relating to a data series. Currently does not have a stable wrapper so use is not documented. Reading array data requires several attributes to be set first.

SetTagAttribute(string uTag, string attributeName, object attributeValue): Sets attributes relating to a data series. Currently does not have a stable wrapper so use is not documented. Reading array data requires several attributes to be set first.

GetTagDescription(string uTag): Returns description of indicated tag.

GetTagUnits(string uTag): Returns units of indicated tag.

MaxPosition: An integer representing the largest element in a Normalized array. This can be used as the bound to loop through points, and does not need to have 1 subtracted from it.

Normalized

• .FindTag(string TagName)

• .Values(integer i).Count

• .Values(integer i).Item(integer i).Value

• .Values(integer i).Item(integer i).TimeStamp

• .Values(integer i).Item(integer i).Quality

  • Type HdaMgr.Normalized.

  • Used to reference arrays of normalized values. Normalized.Values(element) contains values for an input tag with each element representing a different tag. Normalized.Values(element).Item(element) represents a PARCValue for a given time in a normalized array. Each Item has a Value, TimeStamp and Quality property. It is possible to fetch the array position of an input tag with FindTag and to get a count of values for a given tag with Count (although for all normalization types in the script editor the count should be equal for all tags).

NormalizeToAllTimes: Pulls data for all added input tags, then interpolates a point for every tag at every other tag’s raw values.

NormalizeToTag(string UTag): Normalizes data for all input tags to given (fully qualified) tag name.

NormalizeToStep(decimal Seconds): Interpolates data for all added input tags at intervals of indicated step size starting at C.AbsoluteTime.

NormalizeToTimes(List<DateTime> times): Interpolates data for input tags to given list of times. Time list should be in ascending order and not contain any duplicate values.

OV: OV can be used to set the output value by setting OV equal to a PARCValue type object similar to using OutValues.Add(PARCValue), however it will not take a timestamp argument. Even if supplied one it will always give the new output value a timestamp equal to the timestamp stored in the Normalized array at element CurPosition.

OutValues: A PARCValueCollection type object that represents the output values for a script. Build the array up using the .Add method. Point timestamps should be in ascending order.

ProjTime: Returns the HDA time that represents the right bound on time for which a script has been called that has projection data. Use AbsoluteTime method to convert the HDA time to a readable format.

ReadProcessed(string uTag, int aggregateValue):

• ReadProcessed(string uTag, int aggregateValue, Opc.Hda.Time readStartTime, Opc.Hda.Time readEndTime):

• ReadProcessed(string uTag, int aggregateValue,Opc.Hda.Time readStartTime, Opc.Hda.Time readEndTime, decimal step):

  • Type: ItemValueCollection

  • Read processed allows pulling aggregate data similar to TagStat in that it requires an OPC aggregate value (code). Where it differs is that it allows the user to give it a decimal step that allows the formula to pull an array of aggregates. For example, if a day is set for its total time frame, one hour for its step, and then asked for averages, it would output 24 hourly averages.

PARCValues

Creating a new PARCValue object can be done in multiple ways. Creating a new PARCValue with no arguments makes a point with a quality of “bad” by default. Here are ways to create a PARCValue with some information filled in as it is created:

PARCValue()

PARCValue(double v, short q)

PARCValue(float v, short q)

PARCValue(string v, short q)

PARCValue(DateTime v, short q)

PARCValue(DateTime timestamp, double v, short q)

PARCValue(object v, short q)

PARCValue(object v, Opc.Da.Quality q)

PARCValue Methods and Properties

Value: When this is set the script editor will attempt to determine what the data type it is being set to is and change the Value object’s type accordingly.

TimeStamp: Type DateTime

Quality: Type Opc.Da.Quality

HasValue: Will return true if the value of the PARCValue is not null.

IsGoodQuality: Will return true if the quality is good (Greater than or equal to 192).

IsBadQuality: Will return true if the quality is Bad (Less than 64).

IsUncertainQuality: Will return true if the quality is good (Greater than 63 and less than 192).

Aggregate Calculations

The following functions return an aggregate value.

ReadPVAverage(string uTag, DateTime readStartTime, DateTime readEndTime)

ReadPVTimeAverage(string uTag, DateTime readStartTime, DateTime readEndTime)

ReadPVMinimum(string uTag, DateTime readStartTime, DateTime readEndTime)

ReadPVMaximum(string uTag, DateTime readStartTime, DateTime readEndTime)

ReadPVStart(string uTag, DateTime readStartTime, DateTime readEndTime)

ReadPVEnd(string uTag, DateTime readStartTime, DateTime readEndTime)

ReadPVPercentGood(string uTag, DateTime readStartTime, DateTime readEndTime)

ReadPVStDev(string uTag, DateTime readStartTime, DateTime readEndTime)

ReadPVRange(string uTag, DateTime readStartTime, DateTime readEndTime)

ReadPVTotal(string uTag, DateTime readStartTime, DateTime readEndTime)

ReadPVAtTime(string uTag, DateTime readTime)

ReadRaw(string uTag,DateTime readStartTime, DateTime readEndTime, bool includeBounds)

ReadRaw(string uTag,Opc.Hda.Time readStartTime, Opc.Hda.Time readEndTime, bool includeBounds)

• Type Opc.Hda.ItemValueCollection

• Returns an array of raw values in a similar way to how NormalizeToTag returns raw values for a given tag. However ReadRaw allows for raw (not interpolated) points to be pulled for however many tags as necessary. See also Read Multiple Raw example in Appendix II.

StartTime: Returns the HDA time that represents the left/starting bound on time for which a script has been called. Use AbsoluteTime method to convert the HDA time to a readable format.

TagStat(int aggregate):

TagStat(string uTag, int aggregate)

TagStat(string uTag, int aggregate, DateTime startTime, DateTime endTime)

Returns a PARCValue whose value is an aggregate based on the OPC standard (with the exception of the “Last” aggregate which is non-standard). Also returns a quality but not a timestamp. See Appendix III for a list of aggregates and their corresponding integer code values. Returns NULL if no values found for given inputs. If no tag is provided it takes the tag that DataUTag returns. If a start time and end time are not provided, it will return the specified aggregate calculated from all normalized values.

TagStat Aggregate Codes

• NOAGGREGATE = 0

• INTERPOLATIVE = 1

• TOTAL = 2

• AVERAGE = 3

• TIMEAVERAGE = 4

• COUNT = 5

• STDEV = 6

• MINIMUMACTUALTIME = 7

• MINIMUM = 8

• MAXIMUMACTUALTIME = 9

• MAXIMUM = 10

• START = 11

• END = 12

• DELTA = 13

• REGSLOPE = 14

• REGCONST = 15

• REGDEV = 16

• VARIANCE = 17

• RANGE = 18

• DURATIONGOOD = 19

• DURATIONBAD = 20

• PERCENTGOOD = 21

• PERCENTBAD = 22

• WORSTQUALITY = 23

• ANNOTATIONS = 24

• LAST = -2147483645

Writing and Calling Libraries of Functions

Although only one module is allowed in an advanced script, special syntax can be used to reference another advanced formula as if it were a module. To do this type ‘#INCLUDEFORMULA: <Advanced Script Name Here> at the beginning (before the import statements) of the calling script. Note that the comment is intentional, this will cause the script to no longer work in the script editor as this syntax will not be understood by the script editor itself; it will however work at runtime.

This feature is mainly used when several scripts all rely on the same function calls; having all the function calls in one place makes maintaining the code much easier (although debugging becomes more difficult). However one of the most useful applications is that a library module can be called from a simple formula. This allows a function to be created using all the power of an advanced script (importing VB.NET libraries, etc.) and then made available to users in simple formulas.

In this example a new advanced script is created and called “LibraryModule”.

Function DoubleNumericValue(ByVal Input As Double) As Double

DoubleNumericValue = Input * 2

End Function

This function returns twice the input. In this example a single input function is modified to display double the output by first adding in the call to the new function library, then defining two new variables, and then calling the Library Module function:

'#INCLUDEFORMULA:LibraryModule

Imports Microsoft.VisualBasic

Imports Microsoft.Win32

Imports System

'And then defining a two new variables:

Dim Input As Double

Dim OutPut As Double

For i=0 To ArrayCnt-1

If C.Normalized.Values(DPos).Item(i).TimeStamp >= dtStartTime.AddMinutes(-1) Then

Input = C.Normalized.Values(DPos).Item(i).Value

'And then adding in the function call:

Output = DoubleNumericValue(Input)

PV = New PARCValue(C.Normalized.Values(DPos).Item(i).TimeStamp,Output * Factor,CShort(192))

C.OutValues.Add(PV)

End If

Next i