Examples of Calculation Formulas
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:
|