OSIsoft: Exercise on the PI BatchView add-in to Excel. v3.1.2

Uploaded by OSIsoftLearning on 24.05.2011


Exercise 12 has us filling
in a spreadsheet in which we have already defined the formatting for you.
If you will go to your
zip folder that you downloaded, you will find
the Exception Report Template. This is in either the
Downloaded Template or the .docs folder on your
CD. And we have a
spreadsheet here that's already filled out. Let me change the
view to make it a little bit more readable.
The cell wants to try to fit everything in.
And what we have got are some Report Parameters
going back four hours, using our Digester
example. And,
first thing we need to do is fill in some Batch details.
We will then use DataLink to get
the Temperature Averages and Max and the
Pressure Averages and Max, and finally, when all that's
filled in, we will talk a little bit about how the Trend works. So,
I am going to click under Batch Details
and then scroll up just a little
bit so I can see the Report Parameters 'cause I am going to want to see
those to fill in my, to fill in my spreadsheet.
So, I will select PI and Batch Search.
And what I am going to do is use cell references
for all these, all these topics. So, the first
one I am going to use is Batch ID
and there's my Batch ID. It's an *. My
Product Name is here on the spreadsheet, as well as my
Unit Name. My Start Time
is now minus four hours, and my End
Time is Now.
And I can click Search and I will see
all of the results here. Now, I need to
tweak the Settings a bit because the way the spreadsheet's
formatted we do not want the PI Server. And, I want
the Product to be displayed directly
after the Batch ID,
before the Time. So, I am going to move Product with my little Up
Arrow. I am going to move that up until I
have Batch ID, Product, Start Time, End Time,
Unit Name and Procedure.
And I also want to show two Sub-batch Levels
and I want to use, use Indentations
with separate Columns. So, I want three Columns because I have
got a Unit Level and then two Sub-batch Levels.
So, when you get that filled out, you can click OK.

and when the spreadsheet returns,
your fields should be filled in. Now you are going to get some errors
in the Temperature and Pressure Columns.
That's okay for right now. All we want to do is get our Batch
details in place. Now, I could have also used a cell
reference for my Server Name. To do that I would have had
to change that before I started the,
before I started the spreadsheet. So,
now that I have got my Batch Data here,
what I want to do is I want to find out
my Temperature Data. So
the formula's already
filled in. It's using the Calculated Values.
But what I need to do is resolve -- I need to resolve
to a Tag Name, and the Tag Name out here is --
I have got Alias1 and Alias2. Well, Alias1
is Temperature, so I am going to click in the row
of my first results, and I am going to use
the PI Alias Function,
and I am going to use the Alias
of Temperature. And the Module
Path I am going to use has a
cell reference to
the Unit Name that comes up, and that
will get me the proper Tag Name.
You will see it's PP.Digester1.Temp.
And I need to fill that down.
I am going to use the Excel Fill Function,
but, before I do that, I notice that my
Reference to my Unit is Absolute,
and I do want to change that. So, I am going to take the
dollar signs off that Reference,
that G41, because that will make it a
Relative Reference, and that will allow me to be able
to just double-click in the little cell --
in the lower right of the cell -- and that will fill it down
for as far as there's data. If
you get some more data that's too far down,
you can simply highlight that and delete it if you'd
So, let me go out to the right,
scroll down, and I can see that I do have some data.
It was just taking a few moments to recalculate.
And, I need to do the same thing for Alias2. Alias2
is the exact same process. We go --
we use PI Alias.
The Alias I am going to use in this case is
Pressure. And the Module Path
I am going to use in this case -- let me just scroll left just a little
bit. First line is the Digester.
And I should have that filled in.
I change my G41
to be a Relative Reference.
And then, double-click to fill down.
Now, if you want, after,
after everything recalculates -- I will give
Excel a few seconds for all the numbers to come in --
if you want, you can take the Columns M and N,
highlight them, right click, and say, Hide. And that
will appear to your users as if you are just resolving
these values based on this line item.
So, we have got our Query done,
and we are getting our Calculated Data Results. And, what we have done
is we have put a little VBA, or
Visual Basic for Applications, behind the scenes.
So if you select a row and you click on Update
Trend, it should update
the, the Trend over here on the right with some data
from that particular Digester. And,
I am not getting any data for that
one. Let's try a different
Ah, okay.
Let's go into the Trend, define Trend, and I
see that we are -- our Trend was hard-coded
with these Values, so I need to delete those.
And, what we need to do is pick up
the data from PI. And the Tag
Name that I am going to use is going to be the Tag
Name that happens to be in our Hidden Columns.
So I need to -- well, I will
cancel out of this for a moment. I will go back in
and Un-hide these Columns.
And what I want to use in my Trend Definition
data from PI. And the Tag Name that I want to use
is right there on the spreadsheet. So I will add that one
and then I will use a
second Tag. I will add that so
we have our Temperature and Pressure. I want to make
sure Updates are Enabled. I can finish.
And there you see the Trend. So, now,
if I were to select a different Row,
it would, in fact, update the Trend
with that particular data, and you can see
that, depending on the Row we select -- if it's a
Unit Level, it's got a long Start Date and End Date --
Start Time and End Time -- that will get us more data.
So, you, you will have to change the
Server Name that's, that's included in this,
in this segment here.