OSIsoft: Insert and configure a PI Trend with Excel data and PI data. v4.0

Uploaded by OSIsoftLearning on 10.05.2011


Now if you've worked with Excel for a while you know that
within Excel you can insert "traces." I can choose
a location, insert lines,
bar graphs, columns, charts, etc. Within DataLink,
though, we do have an option to insert a "trace" or
a "trend", much like the "trends" you see in Process Book.
So all the nice features you're used to in Process Book, things like
dragging a trend cursor across and dropping
it in a, on, somewhere on your display. All that stuff's
available by using this "Insert Trend" option.
And the "Insert Trend" can end up trending
a tag that you specify in the spreadsheet, it can
trend a stream of data that you show on the
spreadsheet that's got nothing to do with PI. So, it's basically
taking advantage of the functionality that we
have in Process Book "trends" with anything that you've
got already existing in Excel or things you'd like
to pull from the PI server and put into Excel in that "trend."
To insert the "trend" first make sure you've
selected the cell where you want the "trend" to appear. That's going to be the
upper left-hand corner of the 'trend," and then choose "Insert
Trend." Now what this is going to do is it's going to insert an Active X
Control. We've implemented this as an Active X Control.
Excel is an Active X Control container which
is something you could ignore, but that's just a little bit of trivia about
this. This is, this is how we've implemented it.
And, when I click on "Insert Trend" it brings up a
dialog box where I can specify the definition of the "trend."
Now what I'd like to do here is specify
a, an array range here of
what's already on the Excel spreadsheet. Now, I could
choose to bring the data in directly from PI and I'll demonstrate
that a little later on. But first, let's just go ahead and select
everything. Yeah, this is all my data.
This can be data from PI already or it can be
anything: actuarial tables,
whatever. It doesn't matt -- it doesn't need to come from PI. It just needs
to have a "timestamp value," a
pair, like we say "timestamp" and then "Value."
Now we can specify that the first element is going to be the "Name"
if you choose. In this case that's not going to work. I'm
not going to choose that. But, if you had this, you know, as
an array, you can also specify if you want
to include all the cells in the array.
Now that has the effect that
if you just select any cell in an array of
cells then it'll select the entire
array. In this case I already selected the array. And then we
can choose whether this is a stepped, or stair-step plot
or just a regular plot in which we connect the dots.
So this looks good so far. I'll go ahead and add this.
At this point I can go ahead and use this or I
can specify "Other
Areas," or I can bring in data from PI. It's really up to me.
I think I'll just go with this. Now, this turns On and Off
the markers. We'll turn them Off for right now. We'll take a look
at the end result. We get to name this.
So I can call this "My First
Trend" and then,
if you recall, we have these "Value" attributes, whether
a "Value's" been substituted or
annotated or is marked as
questionable by the interface. Those are things that you can display
on the "trend" itself. They show up as
little icons on the "trend." And then we can specify
whether this result is going to show up in a new worksheet or in
the existing worksheet. I had already selected
I wanted this to appear right over here, so I'm going to put "In the
Existing Worksheet." It will appear, but
the upper left-hand corner, there we go, where I
selected. So there's our "trend." Now
I can go back in and define this "trend"
and add other things. For example, I can change this
so that we turn on "markers." So you can see that's the difference between
"markers" and "no markers." And then I can
also add other data. For example, I can add data
from PI to this as well. So to add
data from PI there's two things I can do. I can either make a cell reference
to the name of a tag, or I can
go out and do a tag search. For example, I'm
going to put in the tag CDEP-158.
I go ahead and search for that.
And notice what happens when you click on "OK." Click on "OK",
it shows up automatically here. Now just so you know, if you do,
if you do want to just type
the name of the tag, that's not a problem. You can just type the name of the tag you're
interested in,
and click on "Add" or press "Return." It
will add that and, as well, you can also
go out and search as we demonstrated.
So having done all this I should be able to just say
"Finish." We're going to show some of these with "markers," some without.
Yeah, there we go. If you notice, the green trend has "markers"
and I did that by the choice of "markers"
here, or not. So, I can turn that
"On" and "Off." You see the different "markers" show up now?
I'd like to demonstrate that one more time, and let's look at
the usefulness of that option to select all,
everything, in the array. We'll use two
different "trends" for this. We'll use
this "trend" right over here, or this set of data, and
then we'll use this set of data. In order to do this I need to
move this first. Notice you can't move this
by default, you, or just by
picking up and moving it. You have to choose the "Move
Resize" option and then select the destination. I think I'll
put it down here, and we'll go ahead and move it. So
I'd like to add another "trend" and demonstrate
what happens when we try to select the entire array
using this structure versus this structure.
So I'll put the "trend" right over here.
I'll choose "Insert Trend." As you can see, the default
is to choose all the rows -- or all the
cells in the array. That works fine if I
select something like this in which the
only cells in that array
are cells that have "Values" and timestamps. As you can see we've
selected everything, N6 through O30,
and that works just fine.
Now compare that, let me go ahead and delete this again,
compare that to what happens if we try to insert,
and we're going to include all cells in an array
from here. If you try to add that you'll
notice that it does not conform to the format we require.
That's because we have these "values,"
number of "values" up here and, of course,
the way to get rid of that if you do
have that situation, you can always hide the count.
If we hide the count this now
conforms to something that will work with this,
with this "trend" format. So, if I insert a "trend"
now -- I'll just select that. "Add."
It conforms just fine at this point.
There's a right mouse-click option called
"Scrollbar" that brings up a scrollbar that you can use to scroll backward
and forward. That's not showing as much there because
we're currently looking at just
this column of data. If I were to add this scrollbar down here,
though, since some of these are looking at
process data that's coming straight from PI -- there we go.
As you can see you can go back as far as you need.
And, it's going to go back to the PI server to get that data.
Now once you're finished using the scrollbar
there's an option here called "Revert." It's going to revert back to the
default settings, to the settings that are currently configured
for that trend. Another right
mouse-click option is "Format" and this brings up
the formatting, basically the formatting for all the different
things that you'd find in this "trend." This is where you
can change, for example, the color of the traces
and things like the number of decimal places we show
the data with,
as well as just some global options like do we put grid lines, etc.
I mentioned earlier that you can actually have these update
in real time. That's going to be something that
will work with the data that's coming from PI. The data
on the spreadsheet, of course, is the data on the spreadsheet. That's not going
to update. So, what I need to do, is first of all, let's get
rid of, on this particular "trend," let's get rid of that
data that's coming off of the spreadsheet. So I'll go into the "Define
Trend" option and I'll just take this and
remove it. And now everything's coming from PI.
If I go forward through this wizard here,
we have the option to specify the time range. If I
choose "Enable Updates," and my end time is the current
time, then what I'll see is that the same mechanism
that updates Process Book, you know, at the default
intervals, is going to be updating this. So,
as you can see, every 5 seconds this is
going to go out and get a new update, and
so this is now building in real time.