Understanding How to use GetSamples Method

When you create a trend chart and have data logging defined on 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

Customizing Getting Samples

The trend chart object calls the Historian server to get the data to plot the charts. In some scenarios, you may want to override that configuration and define a .NET code to provide the values. This is used, for instance, to plot recipe calculated data, future data, data from other SQL tables, or any custom scenario.

The custom GetSamples method should be defined for any Script Class, and this method must be defined for the column GetSamplesMethod on Tags → Historian.

When creating tables that are used on time charts, the StartRange and EndRange are DateTimeOffset type. When getting data to X-Y charts, the Range arguments are double variables.

The Returned DataTable for time Charts shall have the columns:

• DateTime: Date and time of the sample
• TagName: The name of the tag is used as the FieldName to the column with Double values of the tag.
• _TagName_Q: Optional column with the quality of the data
  
  

The Returned DataTable for XY Charts shall have the columns:

• X: The name X is used for the column with Double values
• TagName: The name of the tag is used as the FieldName to the column with Double values of the tag
• _TagName_Q: Optional column with the quality of 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.

In this section...