Examples of Calculation Formulas

Important: You do not have the latest version of Historian! You are missing out on the newest capabilities and enhanced security. For information on all the latest features, see the Historian product page. For more information on upgrades, contact your GE Digital sales agent or e-mail GE Digital Sales Support. For the most up-to-date documentation, go here.

Converting a Collected Value

This example converts a temperature from degrees Celsius to degrees Fahrenheit.

Example Code

Result=CurrentValue("Temp F")*(9/5)+32

Calculations Inside Formulas

This example demonstrates how you can perform a calculation within a formula. In this specific case, we are taking the average of tag Simulation00001 over the previous hour. Typically, use a polled trigger to schedule the execution of the formula.

Example Code

Result=Calculation("Simulation00001","Average","Now-1hour","Now",Quality)

Conditional Population

This example demonstrates how you can create conditional collection configurations in which a calculation tag is only populated under certain production conditions.

Example Code

IF CurrentQuality("Simulation00001")=100 THEN 
Result=CurrentValue("Simulation00001")
END IF

Combining Tag Values and Assigning a Trigger

This example adds current values from many tags, outputs one value, and has two calculation triggers assigned.

Example Code

Result=CurrentValue("SERVER1.Simulation00003")+CurrentValue("SERVER1.Simulation00006")

The calculation triggers for the example are as follows:

SERVER1.Simulation0003
SERVER1.Simulation0006

This calculation will fire if tag Server1.Simulation0003 or tag Server1.Simulation0006 changes.

Use CreateObject in a Formula

This example reads data from another Historian Server using the Historian OLE DB provider and stores it in the destination tag. When using this example, specify username and password.

Example Code

'connection and recordset variables
Dim Cnxn
Dim rsCurrentValueFromOtherServer
'open connection
Set Cnxn = CreateObject("ADODB.Connection")
'connect to default server using current username and password
'establish connection
Cnxn.Open "Provider=ihOLEDB.iHistorian.1;User Id=;Password="
'Create and open first Recordset using Connection execute
Set rsCurrentValueFromOtherServer = CreateObject("ADODB.Recordset")
'Get the value from the other server
Set rsCurrentValueFromOtherServer = Cnxn.Execute("select value from ihRawData 
where SamplingMode=CurrentValue and tagname = Simulation00001") 
'Set the result to the current value of other tag
Result=rsCurrentValueFromOtherServer("Value")
'Clean up
IF rsCurrentValueFromOtherServer.State = adStateOpen THEN
rsCurrentValueFromOtherServer.Close
END IF
IF Cnxn.State = adStateOpen THEN Cnxn.Close
END IF
Set rsCurrentValueFromOtherServer = Nothing
Set Cnxn = Nothing

File I/O

This example shows how to read and write text files during a calculation. You may have data in a file to use as input to a calculation, or you may want to write debug values to a text file instead of using the LogMessage function.

Example Code

Dim filesys, writefile, count,readfile
'need to create a file system object since there is no
'file I/O built into VBScript
Set filesys = CreateObject("Scripting.FileSystemObject")
'open the text file, or create it if it does not exist
set readfile = filesys.OpenTextFile("C:\somefile.txt", 1, true)
'try to read from the file
IF readfile.AtEndOfLine <> true THEN
count= readfile.ReadAll
END IF
'add one to the number stored in the count count = count+1
'close the file for reading 
readfile.Close
'open the same file but for writing
Set writefile= filesys.OpenTextFile("C:\somefile.txt", 2, true)
'write the updated count writefile.Write count
'close file for writing 
writefile.Close
Result = count

Convert a Number to a String

If your device and collector expose data as numeric codes, you can change to a string description. This examples also demonstrates that a calculation can output a string.

Example Code

DIM X
x=CurrentValue ("tag1")
select case x
case 1
Result="one"
case 2
Result="two"
case else
Result="other"
End select

Detect Recovery Mode Inside a Formula

This example shows how to detect recovery mode or recalculation inside a formula. If there are individual tags, you do not want to perform a recovery.

Example Code

Dim MAXDIFF, TimeDiff
'Maximum difference in timestamps allowed (Must be > 2,
'units = seconds) MAXDIFF = 10
'Calculate time difference
TimeDiff = DateDiff("s", CurrentTime(), Now)
'Compare times, if difference is < MAXDIFF seconds perform calc
If TimeDiff < MAXDIFF Then
'Place calculation to be performed here:
Result = CurrentValue("DENALI.Simulation00001") Else
'Place what is to be done when no calc is performed here
Result = Null
End If

Looping Through Data Using the SDK

This example shows how to use the SDK to perform a query on a data set. The following code determines the minimum raw value over a one hour time period.

Example Code

on error resume next
                Dim MyServer 'As Historian_SDK.Server
Dim I
Dim J
Dim K
Dim strComment
Dim lngInterval
Dim TagCount
Dim strDataQuality
Dim iDataRecordset
Dim iDataValue
Dim lEndTime, lStartTime, lNumSamples
Dim lNumSeconds, lNumSamplesPerSecond
Dim RawMin
'Instantiate The SDK
Set MyServer = CreateObject("iHistorian_SDK.Server")
'Attempt Connection
If Not MyServer.Connect("DENALI", "administrator","") Then
result = err.description
else
Set iDataRecordset = MyServer.Data.NewRecordset
'Find the number of samples.
'build query
With iDataRecordset
.Criteria.Tagmask = "EIGER.Simulation00001"
.Criteria.StartTime = DateAdd("h",-1,Now)
.Criteria.EndTime = Now
.Criteria.SamplingMode = 4 'RawByTime
.Criteria.Direction = 1 'forward
.Fields.AllFields
'do query
If Not .QueryRecordset Then
result = err.description
End If
'Some Large number so that real samples are less
RawMin = 1000000
For I = 1 To iDataRecordset.Tags.Count
For J = 1 To iDataRecordset.Item(I).Count
Set iDataValue = iDataRecordset.Item(I).Item(J)
' if the value is good data quality
if iDataValue.DataQuality = 1 then
if iDataValue.Value < RawMin then
rawMin = iDataValue.Value
end if
end if
lNumSamples = lNumSamples + 1
Next
Next
End With
End If
Result = RawMin
'Disconnect from server
MyServer.Disconnect

ADO Query

This example demonstrates a query combining Historian data with ADO data. In the example, you convert a collected value, number of barrels per day (BarrelsUsedToday), to a dollar amount. The code then obtains the price per barrel (CostOfBarrel) from the SQL Server, and finally stores the total dollars in an integer tag (TotalCostToday).

You could also do this with a linked server and the Historian OLE DB Provider, but this example keeps a history of the results.

Example Code

Dim CostOfBarrel, BarrelsUsedToday, TotalCostToday
'Calculate the total number of barrels used over
'the previous 24hours.
BarrelsUsedToday = Calculation("BarrelsUsedTag","Total","Now 1Day","Now",Quality)
'Retrieve cost per barrel used
Dim SQLExpression
Dim Cnxn
Dim rsCurrentValue
SQLExpression = "SELECT Barrel_Cost AS Value1 FROM RawMaterial_Costs WHERE Barrel_Type = CrudeOil and
samplingmode = CurrentValue"
'open connection
Set Cnxn = CreateObject("ADODB.Connection")
'connect to default server using current username and password
'establish connection
Cnxn.Open "Provider=SQLOLEDB.1;User ID=sa; Password=;Initial Catalog=Northwind"
'Create and open first Recordset using Connection execute
Set rsCurrentValue = CreateObject("ADODB.Recordset")
'Get the value from the other server
Set rsCurrentValue= Cnxn.Execute(SQLExpression)
'Set the result to the current value of other tag
CostOfBarrel = rsCurrentValue("Value1")
'Clean up
If rsCurrentValue.State = adStateOpen then 
rsCurrentValue.Close
End If
If Cnxn.State = adStateOpen then
Cnxn.Close
End If
Set rsCurrentValue = Nothing
Set Cnxn = Nothing
'Retrieve number of barrels used
BarrelsUsedToday = Calculation("BarrelsUsed","Count","Now 1Day","Now",Quality)
'Calculate total cost of barrels today
TotalCostToday = CostOfBarrel * BarrelsUsedToday

Windows Performance Statistics Physical Memory Usage

This example demonstrates how to create a formula that collects data reflecting private byte usage.

Example Code

`Get a reference to the local data archiver process object
Set RawProc = GetObject("winmgmts:Win32_PerfRawdata_Perfproc_process.name='ihDataArchiver.'")
`Scale the virtual bytes number to a value within
`the tag's EGU range
result =RawProc.PrivateBytes *.001

Windows Performance Statistics Virtual Memory Usage

This example demonstrates how to create a formula that collects data reflecting virtual byte usage.

Example Code

`Get a reference to the local data archiver process object
                Set RawProc = GetObject("winmgmts:Win32_PerfRawdata_Perfproc_process.name='ihDataArchiver.'")
`Scale the virtual bytes number to a value within the
`tag's EGU range
result =RawProc.VirtualBytes *.0001

Writing Data to iFIX

This example demonstrates how to call the FixDataSystem control from a VBScript to write values out to iFIX.

Example Code

Dim FDS
                Dim the_sec
on error resume next
the_sec = Second(CurrentTime())
Set FDS = CreateObject("FixDataSystems.Intellution FD Data System Control")
if err.number = 0 then FDS.Groups.Add ("Group1") 
FDS.Groups.Item("Group1").DataItems.Add "Fix32.T20.TAG1.F_CV" 
FDS.Groups.Item("Group1").DataItems.Item(1).Value = the_sec 
FDS.Groups.Item("Group1").Write
FDS.Groups.Clear Result = the_sec SET FDS = Nothing 
else
' just some number outside 0 to 59 so we know it is invalid result = 99
end if

Determining Calculation Collector Downtime

This example demonstrates how to determine the amount of downtime, in seconds, the calculation collector has experienced over the last day. Downtime occurs when there are two consecutive bad quality data points for the pulse tag. If the last known data point for the pulse tag is bad quality, all time between its timestamp and now is regarded as downtime. In the below example, the pulse tag would be configured to be polled, with a collection interval of one day.

Example Code

Dim pulseTag, totalDownTime, startTime, endTime
Dim prevTime, prevQuality, lastPrevTime, lastPrevQuality 
pulseTag = "calcPulseTag"
totalDownTime = 0 
endTime = CurrentTime()
startTime = DateAdd("d", -1, endTime)
lastPrevTime = curTime lastPrevQuality = 0
Do
  'get the timestamp and quality of the tag value previous to the last one we checked
  On Error Resume Next
  prevTime = PreviousTime(pulseTag, lastPrevTime) 
  If Err.Number <> 0 Then
    'no more values for this tag exit gracefully
    Exit Do
End If
prevQuality = PreviousQuality(pulseTag, lastPrevTime)
'if we have two consecutive bad data points, add to the downtime
If prevQuality = 0 And lastPrevQuality = 0 Then
  If prevTime > startTime Then
    totalDownTime = totalDownTime + DateDiff("s", prevTime, lastPrevTime) 
Else
    totalDownTime = totalDownTime + DateDiff("s", startTime, lastPrevTime) 
End If
End If
  'store the timestamp and quality for comparison with the next values 
lastPrevQuality = prevQuality
  lastPrevTime = prevTime
Loop While lastPrevTime > startTime
Result = totalDownTime

Analysis of Collected Data

This example demonstrates how to analyze collected data to determine the amount of time that a condition was true and had good quality in the last day.

Example Code

Dim tagName, startTime, endTime
tagName = "testTag"
startTime = "Now 1Day" 
endTime = "Now"
Result = CalculationFilter(tagName, "TotalTimeGood", startTime, endTime, 100, tagName, "AfterTime", "Equal", 1)

Simulate Demand Polling

This example simulates demand polling. In order for it to function, the following tags must be created.

Tag Description
Polled Tag A polled tag with a collection interval of the longest period you want between raw samples. Do not enable collector or archive compression. This tag should point to the same source address as the UnsolicitedTag.
Unsolicited Tag An unsolicited tag with a 0 or 1 second collection interval. This tag ensures you will be notified whenever changes occur. This tag should point to the same source address as the PolledTag.
Combined Tag An unsolicited calculation tag that is triggered by either the Polled Tag or Unsolicited Tag, and combines the raw samples of both into a single tag. Use a 0 or 1 second collection interval and use the following formula:
dim timetag1 
dim timetag2 
dim tag1
dim tag2
Dim x
tag1 = "T20.di-1.F_CV"
tag2 = "t20.T20.DI-1.F_CV"
x = DateAdd("s", 1,CurrentTime) ' add 1 second to calc time
' Get the timestamp of the newest raw sample for tag1:
timetag1 = previousTime(tag1, x)
' Get the timestamp of the newest raw sample for tag2:
timetag2 = previousTime(tag2, x)
if timetag1 > timetag2 then
' If tag1 triggered me, then:
result = PreviousValue(tag1,CurrentTime)
else
' If tag2 triggered me, then:
result = PreviousValue(Tag1, CurrentTime)
end if