Overview

When you create a trend chart and have data logging defined on Edit > Tags > Historian or are using an OSIsoft PI System, the product will automatically look for the archived data to plot the chart.

When you want to draw the trend chart mapping to external data, you have the following options:

  • You can populate the Historian archive (SqLite Database, Microsoft SQL, PI System, or another database) from another application
  • You can customize the GetSamples method used by the trend chart when plotting the data


Configuring the GetSamples Method

The first step is to create the method itself. On Scripts > Classes > ServerMain, the following method must be created:

public DataTable GetSamples(string[] tags, DateTime start, DateTime end, TimeSpan interval, out bool hasMoreSamples)
{
// Insert Code here
}

Now, we need to fetch data from the external database. This can be done by executing a select command in a Query and saving the result on a temporary table.

In this example, we will fetch data from a table called ”externalDbValues’ that is formatted as follows:


The SQL Statement executed in our class will be:

int sts = 0; string msg = "";
@Dataset.Query.FetchData.SqlStatement  =  @"SELECT  *  FROM  externalDbValues";
DataTable TemporatyTable = @Dataset.Query.FetchData.SelectCommandWithStatus(out sts, out msg);


For the next step, we must start creating our final table with the same format as the default Historian Table. The columns that are required for the GetSamples method to work properly are:

  1. Timestamp (in ticks) or DateTime
  2. Item Value
  3. Item


A similar table is manually created using the code below:

hasMoreSamples = false;
DataTable table = new DataTable("Values"); table.Columns.Add("DateTime", typeof(DateTime)); foreach (string tag in tags)
{
table.Columns.Add(tag, typeof(double)); table.Columns.Add("_"  +  tag  +  "_Q",  typeof(double));
}


Now, we need to populate the newly created table with the information fetched from our external database and return that table to our project.

foreach (DataRow foundRow in TemporatyTable.Rows)
{
DataRow row = table.NewRow(); foreach (string tag in tags)
{
row[tag] = TConvert.To<double>(foundRow["<Column Name - Tag Value>"]); row["DateTime"] = new DateTime(TConvert.To<long>(foundRow["UTCTimestamp_Ticks"])); row["_" + tag + "_Q"] = 192;
}
table.Rows.Add(row);
}
return table;


At ’foundRow[”<Column Name - Tag Value>”] ’, the inserted column name will be the one with the data that will be trended.

To plot a tag value into a TrendChart element, you will need to create a Tag, add it to Tag Historian Table, and insert it to the Pens list.


In order to avoid saving the fetch sampled data to the local historian database, you can disable the ’Save On Change’ configuration option.

You also need to add the newly created method into the GetSamplesMethod field.


  • No labels