Compare daily values

Blog Post created by andreas on Mar 2, 2012

From time to time people need to compare today's trend for a PI tag with yesterdays, or even several days in the past. One way to achieve this is applying a time offset via a PI Calculation dataset in PI ProcessBook.


So if you want to compare yesterday's SINUSOID with todays, you could use a PI Calculation dataset with the formula "TagVal('SINUSOID','*-24h') and trend this along with SINUSOID. Creating such a dataset and adding it to PI ProcessBook is just a couple of lines of VBA code - so please enjoy:


First - we have a ProcessBook display with a trend, a button and two combo boxes:




To populate the comboboxes at the beginning, let's add some code:


Private Sub Display_Open()
    ' Fill in the shifts
    Dim aa As Integer
    For aa = 1 To 31
        ComboBox_Shifts.AddItem (aa)
    ' Fill in the offsets
    For aa = 1 To 24
        Combobox_Offset.AddItem (aa)
End Sub

Now we build a procedure to build the dataset. We need the servername, the tagname, the offset and how many times we want to apply the offset:


Private Sub CreateAndAddDS(ServerName As String, TagName As String, OffSet As String, OffsetNum As Integer)
    Dim MyDataset As PIExpressionDataset
    Dim MyColumn As String
    Dim MyDataSetName As String
    Dim MyDate As Date
    ' Find a reasonable name for the dataset
    MyDate = DateAdd("h", -OffsetNum, Trend1.StartTime)
    MyDataSetName = TagName & "_" & MyDate
    MyDataSetName = Replace(MyDataSetName, ".", "_")

    ' Create the dataset
    Set MyDataset = Datasets.Add(MyDataSetName, Nothing, False, 1, True, pbDatasetPIExpression)
    Set MyDataset = Datasets.GetDataset(MyDataSetName)
    ' Adjust the dataset to our needs
    MyDataset.ServerName = ServerName
    MyDataset.Expression = "tagval('" + TagName + "','*-" + OffSet + "')"
    MyDataset.RefreshInterval = 3600000
    MyDataset.ColumnName = "Value"
    MyDataset.Interval = "10m"
    Me.Datasets.SetDataset MyDataset
    MyColumn = MyDataset.Name & "." & MyDataset.ColumnName
    ' Add the dataset to the trend
    Trend1.AddTrace MyColumn

End Sub



What we need to do finally is executing this on button click. But before building the datasets we have to clean the existing ones. Than you can start building the datasets for each tag in the trend:


Private Sub CommandButton_Draw_Click()
    Dim MyFullTagname As String
    Dim MyServername As String
    Dim MyTagname As String
    Dim MyOffset As String

    Dim aa, bb As Integer
    Dim MyOffsetNum As Integer
    MousePointer = pbMPHourGlass
    ' Remove all datasets from the trend
    For aa = Trend1.TraceCount To 1 Step -1
        If (InStr(1, Trend1.GetTagName(aa), ".Value")) > 0 Then
            Trend1.RemoveTrace (aa)
        End If
    ' Remove all PIExpression datasets from the display
    Dim MyDS As Dataset
    For Each MyDS In Datasets
        If MyDS.Type = pbDatasetPIExpression Then
            Datasets.Remove (MyDS.Name)
        End If
    ' For all traces in the trend
    For aa = 1 To Trend1.TraceCount
        ' Get tagname and servername
        MyFullTagname = Trend1.GetTagName(aa)
        MyServername = Mid(MyFullTagname, 3, InStr(3, MyFullTagname, "\") - 3)
        MyTagname = Right(MyFullTagname, Len(MyFullTagname) - Len(MyServername) - 3)
        ' For the number of shifts
        For bb = 1 To (ComboBox_Shifts.Value - 1)
            ' Calculate the "real" offset by
            ' multiplying the shift with the offset
            MyOffset = (bb * Combobox_Offset.Value) & "h"
            MyOffsetNum = (bb * Combobox_Offset.Value)
            Call CreateAndAddDS(MyServername, MyTagname, MyOffset, MyOffsetNum)
        Next bb
    MousePointer = pbMPDefault

End Sub



And this is how it looks now:




The complete PB is attached - and I have to mention Dan Fishman and Michael Ramella from field service that initiated all of this.


At some point this functionality could be replaced with PI Event Frames (in PI ProcessBook and other client tools). The PM's plan to address this in a more built-in fashion but meanwhile feel free to build on this .