Hi all,

This is my first post on the RJK Solutions forum. Just graduated with my bs in chemical engineering in May of 2010 and started my first full time job a few months ago with the company I interned with in college; so, in short, I am a PI newbie.

Anyways I've been learning some VBA and working particularly with excel 2007 and PI DataLink as well as a little bit with PI ProcessBook.

I developed an Excel VBA / datalink macro which automates the tracking of flowrates for certain process units in the plant I work in; whenever a user clicks a form control button the macro runs and a couple of graphs (tabs in the Excel worksheet) update dynamically with flowrates so we can track performance over time (data is also dropped into another worksheet). The macro involves dropping .FormulaArray 's into cells and using crazy functions like =PINCompFilDat and =PIAdvCalcFilVal. I don't have much programming experience since in college I mainly took chemical engineering and business classes, but from reading the forums it sounds like it's much smarter to use PI SDK in your Excel VBA code. Apparently this is because osisoft PI SDK provides an object oriented approach to pulling / working with data from the PI Server? As opposed to datalink, which involves using functions in excel (and using VBA with DataLink gets pretty ugly as it tends to involve a lot of "" """ """ ).

DLResize for the win.

Anyways, back to my original question:

This macro updates dynamically an Excel chart/graph to track process unit flow rates over time. Is there a way to embed this Excel graph into a pi processbook or pi processbook display pdi file? The reason for this is because our plant uses VBA code to drive a slide show in pi process book which cycles through slides (display files?) which have different information for operations and engineering like batch progress etc. My manager would like to see the dynamic graphs I have created in one of the slides. I tried going into build mode and inserting an Excel object; also tried copying and pasting the graphs from Excel into the processbook display file. Everything I have tried so far has failed to work.

In short, I'm wondering how to embed this dynamically updating Excel graph into a display file so we can throw it into the slideshow which cycles on various monitors throughout the plant. Thanks in advance for any help.

OSIsoft vCampus is a subscription-based, online offering that consists of providing everything people need to develop applications on the PI System.
We invite you to take a "tour" of the OSIsoft Virtual Campus - also feel free to consult the FAQ  or contact OSIsoft vCampus for more details.

RJK thanks for your reply.

I played around with PI ProcessBook DataSets, but I'm not sure this can do what I want.

Basically what I have is a process unit that runs occasionally, and I'd like to know what the average value for the feed flowrate is for each run.

Then, I'd like to track that average flow rate over time (in other words each point on an Excel scatter plot would represent the average flow rate for a run) where y-value is L/min average flowrate and x-value is the timestamp corresponding to the start of that specific Run.

How I did this in Excel (all automated via excel vba macro):

Used PI DataLink Filtered Compressed Data Function to generate an array of every "On" value for this "active and processing tag" for the process unit along with the corresponding timestamp. These timestamps represent the start of each run.

Using these timestamps, I used PINCompFilDat to give me the very next value for the "active and processing tag" assuming it is equal to "Off"; I now have timestamps for every off value.

Now, I essentially know the timestamps corresponding to the starts and stops of every run in existence for this process unit.

All I had to do was use the PI Advanced Calculated Data function (along with run start timestamps and run stop timestamps) to calculate the average value for the flowrate tag. I also filtered out some bad data so I used PIAdvCalcFilVal.

Basically the excel macro finds start and stop times for every run up to * and uses the timestamps to calculate average feed Flowrate. And I have a graph where each point represents a run's average flowrate and x value for the point is when the run started.

______________________

I really want to find out a way to put this in the processbook display file and have it update dynamically. Perhaps I could embed an Excel macro enabled workbook object and copy and paste the code and modify it to work that way somehow and display the worksheet tab with the graph?

or is there somehow a way to do what I did with excel datalink instead using the dataset plot trend feature you talked about?

but it's like i would need to figure out a way to plot every point given by:

TagAvg('feed flowrate tag','timestamp corresponding to first "On" value ever','timestamp corresponding to first "Off" value ever')
TagAvg('feed flowrate tag','timestamp corresponding to second "On" value ever','timestamp corresponding to second "Off" value ever')
...etc....


Any ideas you could provide would be much appreciated, thanks !

Hello...apologies for the delay getting back to you, had to find some spare time!

So if you had a dataset like the following, you get to see what the average was when the processing ends.



If you had this as a performance equation that is scheduled to run when the value of your "Processing" tag changes you will get what you need. I used a 100 day search period as a default, you can adjust it to what is typical for a processing run.

Basically the PE does the following:
- Checks to see if the value is now False. (Or "Off" in your case)
- Searches backwards in time (for the last 100 days) for when the value was True. (Or "On" in your case)
- An average is calculated from the current time to the time when true was first encountered.
- If the value is not False, then no average value is calculated.

You can put this in ProcessBook as a dataset and trend it to see the behaviour. If you do so, then you will notice it will calculate for every value and not how it would calculate as a Performance Equation that calculates only when the processing flag changes.