Skip navigation
All Places > PI Developers Club > Blog > 2009 > January

The PIArchive..PICalc Table

PI ProcessBook features datasets. These allow an user to create a calculation that is calculated on the fly when somebody uses it in a Trend, Value, etc. In RtWebpart an administrator can create PI Calculation datasets but an ordinary user is not capable of doing this (you need to have access to the RtBaseline Services administration).


However, with the help of PI OLEDB we can create a simple relational dataset that takes the expression as a placeholder:


SELECT expr, time, CAST (value AS float32) result FROM piarchive..picalc WHERE expr = ? AND time > ? AND time < ? AND timestep = ?


This simple dataset can be used either with the expression defined at the level of the RtWebpart as well as the RtWebpart can consume the expression from another web part (for example the Form web part).


Note that the Relational Dataset first needs to be created in RtBaseline Services by the administrator. Because it contains a placeholder that allows end-users to use it with any expression in their Web Parts, it is only done once and then becomes a conveniences to end-users.


Read more in my next post AdHoc Calculations in RtWebparts (2)!


Querystring Fun

Posted by pkaiser Employee Jan 27, 2009

Querystrings have long offered the opportunity to make configurable web pages, allowing name-value pairs to be tacked on to the end of a page's URL. RFC 1738 (December 1994) describes a "searchpart" as a "query string" that follows a "?" delimiter in an HTTP URL. As a tried-and-true mechanism for passing parameters in URLs, there is of course some support for querystrings in the RtWebParts product.


Many of our customers are familiar with the querystring parameters that can be passed to instances of the RtTimeRange and RtTreeView web parts (if you're not familiar with these, check the product documentation). However, fewer realize that RtWebParts v2.0 introduced a querystring-enabled version of our ad-hoc trend page. Sure, you can invoke an ad-hoc trend from many of our web parts, but there is also a well-known URL to an ad-hoc trend page that can receive start time, end time, and a list of PI tags to trace via the URL. This mechanism is applied by our PI Notifications product to include a link to an applicable trend within a notification email.


The well-known URL for this querystring-enabled ad-hoc trend page is:




Where the <SharePointServer> placeholder represents the name of the SharePoint server where RtWebParts has been installed.


As stated, this page supports three querystring parameters to configure it's appearance:

  • StartTime - UTC seconds or any other time format supported by RtWebParts, such as "*-2h"
  • EndTime - UTC seconds or any other time format supported by RtWebParts, such as "*"
  • Data - A list of one or more PI tags with no delimiter, of the format \\server\tag[\\server\tag]

For example, if your SharePoint server is named "mywebparts", and you want to trace the "sinusoid" and "cdt158" tags from a PI server named "mydata" over the last 12 hours, you would use the following URL:




The StartTime and Data parameters must be provided in the querystring, but if the EndTime is omitted the ad-hoc trend will assume an end time of "*".


As I mentioned, ad-hoc trends are available from the pull-down and right-click menus in several of our web parts. However, the configurability of the ad-hoc trend through its URL provides substantial integration opportunity through basic web mechanisms that need not originate from within web parts or even SharePoint. Of course the RtWebParts product must be installed to a SharePoint server, but once installed this ad-hoc trend is available without ever building a web part page. In short, if you've installed RtWebParts version 2.0 or later, the ability to integrate ad-hoc trending into your web applications using basic querystring parameterization is already available to you. How's that for Value Now? 

Bryan Owen

Pubs with PI?

Posted by Bryan Owen Jan 20, 2009

Oregon has joined the “nerd bar” scene (a.k.a.  Science pubs).  Maybe this trend is just something else for Portlander’s to do when it’s raining. On the other hand, tech history is full of engineering designs sketched on napkins…some good and some that are quickly forgotten.


For sure it is quite rainy here, so the idea sparks some intrigue.  Fun at the pub while discussing solutions for global problems sounds ok.  Checking out the modern day equivalent of pen protectors and slide rules could also be interesting but alas, the substance would not real enough for me.


Now blogging about PI, that’s a different story.  I know folks are using PI to make plants run smarter: higher efficiency and yield; less waste and pollution; focusing maintenance and capital improvements; securing plants from cyber attacks…right on! The scene on vCampus is a way I can help and something I want to be a part of, so welcome to Bryan’s pub blog on PI!



One feature I think might be unknown or underestimated is the support for WebServices in PI ACE 2.x, which I'd like to cover here in a brief paper. Depending on the feedback you'll give me and how people like it, I might enhance it and turn it into a Tutorial destined for the vCampus Library. Happy reading and coding!




The What and the Why


On one hand we have web services, which are gaining popularity as they allow for very simple client-side applications that can work over the Internet (i.e. across firewalls) and that are interoperable across platforms, applications, and programming languages (because they use standards such as HTTP, SOAP and XML).


On the other hand we have PI Advanced Computing Engine (ACE), which allows people (developers and non-developers) to create simple or complex calculations that can be make use of PI data. In PI ACE 2.x, these calculations can be streamed on demand by invoking the exposed web services. This means a very easy (1 simple procedure for all ACE calculations) and efficient (calculations are done on the server) way to extend what yourself or somebody has done in PI ACE already.


Part 1 below is also outlined in the PI ACE User Guide, available on the vCampus Library (under Chapter 6: ACE 2.x Features, see Exposing Calculations via Web Service). That's why I'll focus a little more on Part 2 and 3, which explain how to "consume", how to use those web services.


Also, if you are not familiar with the installation, configuration or usage of PI ACE, please consult the PI ACE User Guide in the Library and don't hesitate to post in the PI ACE Development discussion forum.




Part 1 - Setting up the PI ACE web services


The first few steps consist of activating the streaming of PI ACE calculations via web services:

  1. Install and configure Internet Information Services (IIS) as per Microsoft's instructions.
  2. Install PI ACE 2.x – at least the Scheduler part.
  3. Create a directory in the wwwroot directory (such as PIACEWebService) and copy the file \PIPC\ACE\PIACEWebService.asmx to the directory.
  4. Create a subdirectory called bin and copy the file \PIPC\ACE\PIACEWebService.dll to it.
  5. Open the Microsoft IIS management console.
  6. Select the web service directory created in Step 1 (i.e. PIACEWebService), right-click it, and select Properties.
  7. Click Create and set appropriate security settings.

At this point the ACE web services are ready to be accessed and perform on-demand streaming of the ACE calculation results.


2 web services are exposed by PI ACE 2.x:

  1. GetACEContexts: retrieves all available ACE Contexts available for execution.
  2. Calculate: executes a given calculation (i.e. Context) with the specified Start Time, End Time and Interval.

The Calculate web service returns 3 pieces of information:

  1. Timestamp (in UTC Seconds)
  2. Value
  3. Type (this value gives precision on the type of the Value field: -2 is for text string, -1 is for numeric and a value of 0 and higher represents the code of a corresponding PI digital state)



Part 2 – Add a reference to the PI ACE Web Services
(example using Visual Studio 2008)


Using web services is very similar to using methods contained in regular assemblies that reside on your computer: you 'add a reference' to it in your project, you instantiate them and then use their methods. The only difference is that those are 'web references' or 'service references' as opposed to regular ones.

  1. Open Microsoft Visual Studio 2008
  2. Create a new "Windows Forms Application" Project (I called mine 'ACEWebServiceExample')
  3. In the Solution Explorer, right-click on the project and click Add Service Reference:
    (note the option is called Add Web Reference in Visual Studio 2005)
  4. 090119.01_2D00_AddServiceReference1.png

  5. On the Add Service Reference window, click Advanced:
  6. 090119.02_2D00_AddServiceReference2.png

  7. On the Service Reference Settings window, click Add Web Reference:
  8. 090119.03_2D00_AddServiceReference3.png

  9. On the Add Web Reference window:
    1. Navigate to the web service virtual directory (i.e. http://YourWebServer/PIACEWebService/PIACEWebService.asmx)
    2. Choose a name for your reference (i.e. myACEWebSrv)
    3. Click Add Reference
    4. 090119.04_2D00_AddServiceReference4.png

  10. You should now see the reference (with the name you gave in the last step) appear under Web References in the Solution Explorer:



Now is time to use this reference.




Part 3 – Consuming/Using the PI ACE Web Services
(example using VB.NET under Visual Studio 2008)

  1. Make the form bigger and place the following objects on it:
  2. 090119.06_2D00_ACEWebServiceForm.png

  3. Go in the code behind this form – use the View Code tool at the top of the Solution Explorer: 090119.07_2D00_ViewCode.jpg

  4. At the class level, declare and instantiate a variable that will hold and execute the ACE web services:

Private ws As New myACEWebSrv.PIACEWebService

  1. Get in the btnListContexts button's Click event and add the appropriate code to:
    1. declare the appropriate variables (a single string and a 1-dimension array of string)
    2. invoke the GetACEContexts method of the web service (and fill the array of string)
    3. clear the content of the drop-down list
    4. loop through the retrieved contexts (with the help of the single string variable and a For..Each structure)
    5. add these contexts to the drop-down list

Dim strContext, strContexts() As String
strContexts = ws.GetACEContexts()

For Each strContext In strContexts

  1. Get in the btnCalculate button's Click event and add the appropriate code to declare the following variables:
    1. A 1-dimension array of myACEWebSrv.PIACETagValues objects
      (this is the top-level object that will be used to retrieve the values)
    2. A single myACEWebSrv.PIACETagValues variable
      (to help looping through the array)
    3. A date initialized to January 1st, 1970
      (remember timestamps in PI are expressed in seconds since January 1st1970)

Dim ACEValues, arrayACEValues() As myACEWebSrv.PIACETagValues
Dim ACEEvent As myACEWebSrv.PIACEEvent
Dim date1970 As New Date(1970, 1, 1)

  1. Still in the btnCalculate button's Click event, we then we execute the Calculate method from the web service, and we:
    1. pass the values from the various fields on the form
    2. fill the 1-dimension array of PIACETagValues objects

arrayACEValues = ws.Calculate( _
    cmbContexts.Text, txtStartTime.Text, _
    txtEndTime.Text, Double.Parse(txtInterval.Text))

  1. Then we loop through the results and add them to the ListBox element:
    1. use a For..Each structure to loop through the series of values
      (there might be more than 1 output tag for the executed ACE Context)
    2. use a For..Each structure to loop through the values of a given series
    3. add these values to the list
      (note the .AddSeconds().ToLocalTime construction, where we add the retrieved timestamp – a number of seconds since 1970 – to our reference date, and we then transform it into local time  - as opposed to UTC)

For Each ACEValues In arrayACEValues
    lstResults.Items.Add("Values for tag: " & ACEValues.TagName)
    For Each ACEEvent In ACEValues.PIACEEvents

        lstResults.Items.Add( _
            date1970.AddSeconds(ACEEvent.TimeStamp).ToLocalTime.ToString() _
            & " - " & _




Voila! You now have a good idea of how you can make use of some of the methods and objects exposed by the PI ACE 2.x web services.






Only a few minutes and 30 lines of code later, you enabled and exercised the consumption of PI data over web services in a cross-language, cross-platform, very lean and efficient environment.


Also important to note, is the fact that the PIACETagValues object is based on XML and is serializable; this means one can use it in controls such as the DataGridView (set its .DataMember property) and manipulate it in many different ways. Who knows, maybe that'll be in 'Part 2' :)


Let me know what you think!


Hi there!
Have you ever thought about a manual input to PI from SharePoint?

So here is what you need (at least if you do it like I did it ):

  1. Microsoft Office SharePoint 2007
  2. A SQL Server
  3. The PI OLEDB Provider
  4. And RtWebParts – because entering data is not so much fun if you cannot trend it!

O.k. now – what are we going to do?


Microsoft Office SharePoint Designer 2007 allows you to create a custom view of data – a Data View. The Data View supports various sources, in this example we will use a SQL query. The interesting part is that the Data View allows you to have an insert query as well.
And here starts the fun; we are going to insert a value from a Data View in a SQL table.

Wait a moment - this is vCampus, so what about PI? Well, we will create a job that moves that data from the SQL table to the PIArchive (via a linked server) – and voila – we have a manual input to PI from SharePoint (somehow ).
You will not get bored with the preparation - I have already a tag called ManualInputSQL, a table in my SQL server called ManualInputTest (with a datetime column as primary key and a value column) and I have created my linked PI Server called RtDemoPI.

Create a Web Part Page

So I am starting from a blank SharePoint Web Part page – and as I told you, we want to do some RtWebParts.

  1. We add an RtTrend and an RtTimeRange:

  2. RtWebpartPage1.jpg

  3. Now we are going to open this page in Microsoft Office SharePoint Designer:

  4. SharepointDesigner1.jpg

Create a Database Connection

To use a Data View we have to create a Database Connection first.

  1. We go to Data View > Manage Data Sources
  2. On the right hand column we can now expand Database Connections and select Connect to a database.
  3. We configure the database connection (in this case the server is the SharePoint server and I use the "sa" account to connect to the database.

  4. ConfDBConnection1.jpg

Note that this authentication option saves the username and password as clear text in the data connection, but for our test here this should be o.k.

  1. In the following dialog I specify the database and the table:

  2. ConfDBConnection2.jpg
  3. I am done with this part.

  4. DataSourceProp1.jpg
  5. Finally we press OK to finish the configuration of the database connection.

Put the Data View on the Web Part Page

  1. We Drop the Database connection to the Web Part Page to use the Data View:

  2. SharepointDesigner2.jpg
  3. And we change the Data View properties so that we can insert a value:

  4. DataViewProp1.jpg
  5. So it is time to see how it looks like and how we can insert data.

  6. RtWebpartPage2.jpg

The SQL Server

Now let us look at the job to move the data from SQL to PI:

  1. Open the Microsoft SQL Server Management Studio.
  2. Expand the SQL Server Agent and create a new job.
  3. Give the job a meaningful name and click the Steps page.
  4. The first step is to copy the data (remember, the PI server is a linked server called RtDemoPI, rtdemogmbh is the database, manualinputtest is the table and ManualInputSQL is the tag):

  5. INSERT rtdemopi.piarchive..picomp (tag,time,value)
    SELECT 'ManualInputSQL'AS tag,time,value

  6. The second step to delete the data:
  7. DELETE FROM rtdemogmbh..manualinputtest

  8. And we schedule the job to run every minute. If we have done this – we have our manual input to PI from Microsoft SharePoint!

  9. RtWebpartPage3.jpg

2009: the "contents" year

Posted by spilon Jan 8, 2009

Happy New Year to all vCampus members!


Now that the "launch frenzy" has passed (remember we launched vCampus on November 17th) and the year-end Holidays are behind us, other vCampus team members and myself will be focusing on adding contents in the different areas of vCampus. 2009 promises to be rich in new contents!


While already have some very interesting elements in the Library, we have more in the works right now, with plans for exciting new components throughout the year!


Here are a few things I would like to suggest for starting the New Year on the right foot:


·         If you haven't attended it, make sure you check out the recording of our last webinar in the Auditorium, to answer your questions about the vCampus program.


·         Should you have more questions or comments, please feel free to post in the Generic vCampus Discussions forum


·         Register for the next webinar on January 29th if you have plans to develop on AF and Notifications


·         Submit your ideas for webinars in this discussion thread


Looking forward to reading you soon!

Filter Blog

By date: By tag: