Skip navigation
All Places > PI Developers Club > PI Visualization Development > Blog > Author: pthivierge

This post is related with many questions we receive were people wants to know how to manipulate PI ProcessBook DataSets Dynamically.

Here I am specifically providing an example for ODBC Datasets.


Here are few more  remarks if you push this example further yourself I would recommend that

  • You do not create too much datasets as this can impact performances.  ThisDisplay.DataSets.Remove() method can help.
  • After you are done with your dataset, you should either remove it with code (ThisDisplay.DataSets.Remove()) or use the same dataset(s) and modify them programmatically, is use this second approach in the example here.
  • You specify a time period when querying data in your SQL data source so you don't retrieve unecessary data. Something like this:
    • SELECT time,value from DataTable WHERE ValueName='ValueA' and time=>'2015-03-11' and time<'2015-03-12'


The following manual, in addition with Processbook help may also help:

PI ProcessBook VBA Language Reference


Edit: I have attached a T-SQL script to create and table and populate it with random SQL data.   If you are not sure about what it does, don't run it!

Edit: Updated the example to add a GetDataSet statement when creating a new DataSet as per Eugene Lee's comment.


To test this example, just add a trend and 3 buttons on the display.

Then copy the code below in a module.

Assign methods: ValueA, ValueB and ValueC to the buttons.  So when you press the button the sub is called.

Finally, you will need to create a DSN to your database and change the SQL queries that are hard coded in ValueA,ValueB and ValueC methods.

2015-03-12_13-02-02_PI ProcessBook - [Dynamic ODBC Dataset.PDI_].png


' Module    : Module1
' Purpose   : Examples that shows how to manipulate the ODBC dataset dynamically
'             to update a trend.
'             This example requires 3 buttons to call ValueA,ValueB and ValueC Methods.
'             It also requires a trend called Trend1 (default), and an existing DSN (created with odbcad32 command)
'             In this example DSN is called localSQLServer, just change DSN_Connection_Name constant to modify it.
'             It uses a single PB dataset, that is updated to change the values showed on the trend.
'             With you SQL query, it is important to return the value with name "value", this is why in the query
'             you see value as value, you may change it for data as value for example, this depends on your table structure of course.

Const DSN_Connection_Name = "localSQLServer"
Const PBDataSetName = "SQL_ODBC_Dataset"

Sub ValueA()
    Dim query As String
    query = "SELECT time, value as value FROM TimeSeriesData where Tag='ValueA'"
    ChangeDataSet query, "ValueA"
End Sub

Sub VAlueB()
    Dim query As String
    query = "SELECT time, value as value FROM TimeSeriesData where Tag='ValueB'"
    ChangeDataSet query, "ValueB"
End Sub

Sub ValueC()
    Dim query As String
    query = "SELECT time, value as value FROM TimeSeriesData where Tag='ValueC'"
    ChangeDataSet query, "ValueC"
End Sub

' this is the main function that : Create or modify the dataset; updates the trend.
Private Sub ChangeDataSet(query As String, DataSetDescription As String)

    On Error GoTo Error

    Dim oOdbcDataset As dataset

    ' if this fails, it resumes to the next statement, and oOdbcDataset will be nothing
    Set oOdbcDataset = ThisDisplay.Datasets.GetDataset(PBDataSetName)

    ' the dataset does not exist, it needs to be created
    If oOdbcDataset Is Nothing Then
        Set oOdbcDataset = ThisDisplay.Datasets.Add(PBDataSetName, oOdbcDataset, True, 1, True, pbDatasetODBC)
        Set oOdbcDataset = ThisDisplay.Datasets.GetDataset(PBDataSetName)
    End If

    ' modify the dataset - we use the description to provide the displayed value name on the trend.
    ' this requires to modify the Trend properties: Display format -> Legend -> uncheck tagname and check desription instead.
    With oOdbcDataset
        .DataSourceName = DSN_Connection_Name
        .query = query
        .Description = DataSetDescription
    End With
    ThisDisplay.Datasets.SetDataset oOdbcDataset

    ' we clear the trend a recreate the trace(s)
    ' this is required to have the description updated.
    ClearTrendTraces ThisDisplay.Trend1
    ThisDisplay.Trend1.AddTrace PBDataSetName & ".Value"
    Exit Sub
    If Err.Number = -2147213132 Then
        ' Data Set was not found, we just overcome this error if it occurs
        ' the object oOdbcDataset will be empty so we manage that with the statement: If oOdbcDataset Is Nothing
        Resume Next

        MsgBox Err.Number & " - " & Err.Description, vbInformation, "Error"
    End If
End Sub

' Clears all traces on a trend
Public Sub ClearTrendTraces(t As Trend)
    For i = 1 To t.TraceCount
        ThisDisplay.Trend1.RemoveTrace i
End Sub


Hope this helps.


Best Regards



Today I was working on a question someone asked me, this person had created a wind turbine animation in PI ProcessBook but he could not figure out how to "Run" his animation.


So the question is how could we do that in the most effective way?

So certainly, you can call your animation in the Display_DataUpdate method, it is executed every five seconds.  But this is not suitable if you need more control over the refresh rate.


We need an event mechanism, and this is not so easy because VBA does not contain any delegates or fancy timer objects.  I also want this solution to stay as simple as possible.


So first I started looking at existing posts on the subject, thanks to David Hollebeek that made the suggestion about the Application.OnIdle event that resides within the ProcessBook VBA Model.

That gave me an idea to create my own timer class in ProcessBook.  I firstly implemented the Application.OnIdle Event, but after few tests, it turned out that performance was very bad and was consuming a lot of CPU.


So I have finally written my own Timer class. This class contains a waiting Loop that is combined with a DoEvents and a Sleep.  DoEvents lets ProcessBook update the User Interface (UI), so be sure that you do not remove it! Otherwise ProcessBook will seems to hang, the only way to get it back it to hit Ctrl+Brk.  The "Sleep 25" limits the CPU usage by giving time to the CPU so everything runs smoothly without high CPU usage.


To use the class, copy and paste the following code into a new Class in PI ProcessBook Visual Basic Editor:


' Class    : clsTimer1
' Date      : 2014-12-16
' Purpose  : Class for PI Processbook, to create a timer

Private lastTrigger As Single
Private timerPeriod As Single
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private isRunning As Boolean

' Procedure : Start
' Purpose  : Start the timer and defines at which interval it ticks
Public Sub Start(TimeInMilliseconds As Single)
    timerPeriod = TimeInMilliseconds
    isRunning = True
End Sub

' Procedure : Stop
' Purpose  : Stops the timer
Public Sub StopTimer()
    isRunning = False
End Sub

' Procedure : OnTimer
' Purpose  : This is the function called when the timer reaches the configured duration
'            You can manually change what is called here
'            To create a timer that calls another method,
'            you need to create a new clsTimer. (unfortunately there is no easy way for delegation in VBA... )
Private Sub OnTimer()
End Sub

' Procedure : RunTimer
' Purpose  : Loop, wait and fires OnTimer when duration has elapsed...
Private Sub RunTimer()

    ' infinite loop
    Do While isRunning

        If ((Timer - lastTrigger) * 1000 >= timerPeriod) Then
            lastTrigger = Timer
            Call OnTimer
        End If
        ' refreshes the UI
        ' add a small pause in the loop so CPU does not goes up...
        Sleep 25

End Sub


So we have our Timer class, now to use it I chose to create a Module, my Module would contain all my animations. I also have added two methods: StartAnimation and StopAnimation, that I can call with a button to start and stop the animation.


Create a new Module called ModAnimation  and paste this code into it.

You will need to change, at least the line 37, depending on which object(s) you need to animate, and add more objects depending of the complexity of your animation.


Option Explicit

Private animation1Timer As clsTimer1

Const ANIMATION_SPEED = 500 ' speed in milli seconds

Public Sub StartAnimation()

    If animation1Timer Is Nothing Then
        Set animation1Timer = New clsTimer1
        animation1Timer.Start ANIMATION_SPEED
    End If

End Sub

Public Sub StopAnimation()
    Set animation1Timer = Nothing
End Sub

Public Sub ExecuteAnimation1()
    ' static value remembers the value between sub calls...
    Static i As Integer
    ' do the animation(s)
    ' this is a very simple example...
    ThisDisplay.Symbols.Item("OSILogo").Rotation = i
    ' rotation
    i = i + 1
    If i > 359 Then i = 0
End Sub



In my example, the module makes the following image rotate, this is a very simple example.

But you can easily change the content of ExecuteAnimation1 to create your own animation.

2014-12-16_16-31-50_PI ProcessBook - [ProcessBookAnimationTimer.pdi_].png



I hope this may help you with PI ProcessBook, when you need to create your own events and control when they occurs.  Also let me know what you think and if you have other ideas to improve this solution.


A working example is also attached to this post.