Skip navigation
All Places > PI Developers Club > PI Visualization Development > Blog > 2015 > March

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