OSIsoft: Exercises on DataLink for Excel Services (DLES). v2.2

Exercise 17.
In this exercise, we are
going to be using the Excel Web Access WebPart
to exploit DataLink for Excel services by putting a
spreadsheet on a WebPart page. So we'll start
by either opening an existing spreadsheet or creating a
brand new one. We are going to publish it to a SharePoint
Document Library. And we are going to make sure that we have those named parameters setup
so that we can pass tag name and start time from the WebPart page.
Now we'll have to create a new WebPart page to do this.
Which is something that we'll take for granted that you already know how to do.
If you don't know how to do that, go back
to the earlier exercises and we demonstrate how to do that.
Now in addition, we've got
some optional things,
some extra credit.
If you can add a choice filter,
that's going to be just choosing
one of these four tags and then a
text filter to that page, then we'll connect those two
WebParts to the Excel Access WebPart.
So entering text or making a choice is going to
affect what is on your spreadsheet.
So let's get started. If we take a look,
this is an existing spreadsheet.
And on this existing spreadsheet,
and you'll find this in your Files directory on your CD.
We just have a
tag and a time stamp and
a couple of functions. The first function is
the Current Value function. The second one is the
Compressed Data function in which we are specifying
we want to get 30 values after this
start time. So the start time is right here, t+15h.
And if I change that to a different start time, we'll get a different set of data.
Now in order to prepare for this,
we have to create a named
range. Probably the simplest way to do
that is just by typing in
here, tag_name. And this has now created
a named ranged called tag_name.
I'll also create a name range called time_stamp. I'll do that
over here if you go into the Formulas tab, and choose Define Name.
I'll call this one start_time.
Now I'll go ahead
and save this. After we save it,
we need to upload this to a SharePoint Document Library.
Now with certain versions of Excel, you
do have the option to publish this directly
to that SharePoint Document Library.
And I've demonstrated that before.
You'd have to make sure that you know where the Document Library is.
But in any event, I'm just going to do it the simple way.
I've saved this
into my Labs directory. So I simply need to find
that and put it into my SharePoint Document Library.
So let me exit out of Excel.
Now here is the WebPart page that I'm
going to be working on. It's simply called
Exercise 17. Before I can start with the Excel Web Access
WebPart, I need to make sure that I've uploaded
this to a Document Library.
So let me go out to my
solutions site.
It is currently not listed here in my Document
Library so I haven't tested this before. But in order
to do this, I can just go ahead and add a
new document here. So it doesn't matter which
Document Library you put it on, just as long as it's a
Document Library that you have access to.
And here it is, it's called ProductionReport_completed.
So as I said, you'll find that in your Files directory or you can create
a brand new one. So there it is.
Now in order to exploit this and
to make use of it, I
go into my Solutions... Go back to Exercise 17,
this is a WebPart page. And I'll just
add this WebPart right here.
So the WebPart I would like to add
is the Excel Web Access WebPart.
This is a WebPart that comes from
Microsoft. Now again, the way I did that was by clicking on
Add a WebPart, and then finding that WebPart.
It's like most WebParts, it's quite useless without
other configurations. So let me go ahead and modify this WebPart.
First thing we'll do is
point this to the right Document Library.
We'll point this to the right spreadsheet.
So I just clicked on the little browse
button there. I'm going to look for the appropriate
Document Library.
And there it is, it's called
Solutions. Here is the report that I just uploaded.

And the grand finale here
should be if I go ahead
and say OK
and there we go.
Now if we say OK at this point, we can see
that that spreadsheet is there.
We aren't finished yet though
because the parameters that we wanted to pass to
this, they are not listed here. Normally we would see the
parameters appear within that WebPart.
The reason I have passed there is because
we didn't use
the publishing options to do that. So I'm going to open
this up in place. I can either open it up
in place or go over here and open it up from here.
Either way, I need to open up
this spreadsheet. And I think in this case what
I'd like to do is open this open up from my SharePoint
Document Library. And this is the file right here.
I'll go ahead and click on this.

Depending on your security settings, you
may see this in your version of SharePoint.
This is identified as a server workbook. Do we want to edit this?
Yes I do want to go into edit mode for this.
And within here, we see something we don't
normally see when we are opening local files-- we see server options.
This is where I would specify my Excel Services
options. So again I found that
within the Windows button
here. Server and then we want Excel Services Options.
This is where we can specify what it is that we
are going to be sharing through Excel Services.
We are just going to specify Sheet 1.
And under parameters, I want to
add the start time
and the tag name. These are two parameters
that I would like people to be able to enter from the
WebPart page, the Excel Access WebPart.
That looks good.
I need to make sure I save this.
And now going
back to my WebPart page,
let me go ahead and refresh this.


And as you can see, the configuration changes that
we made to the file have been
picked up by this WebPart page.
We now recognize that there is a start time and a tag name
associated with this. And I can start typing in the names of
new tags. Those tag names are passed
over and we are retrieving the appropriate data. And the
same for start time, let's go with
* - 1d.
Or actually this is just the start time,
I can just say * and this will
be... How about
t + 7h. That would be today
at 7am. As you can see, this starts
from 7am going forward. So if I were
to go to say, t which stands for today at midnight.
Now we are seeing data from today at midnight.
The extra credit portion of
the exercise is to add two WebParts
and then connect them. We are going to use Choice Filter,
and Text. Choice Filter is going to give you these four choices.
So let's go ahead and do that. Here is our exercise
as it stands. As we saw, it is working fine with the
parameters right now. We are going to change that around
and use connections instead. So to
get it to this first, we go into
edit page. We are going to add a couple of new WebParts. I'm going
to put them down here on the left. These are
going to be filter WebParts. You should find them
fairly close to the top.
There we go. I want a Choice Filter which is
brand new, we haven't used that before. And a
Text Filter which we've seen in demos before.
And let's do the Choice Filter
first. What I'd like to do is
specify what the choices are. So I'll go into Edit,
Modify Shared WebPart.
And let's call this,
the name of this is going to be Choose Tag
or Tag Choice or something like that.
And this is where we are going to put the names of
our Tags. So we'll just use some of the standard
tags that we saw listed there, CDT-158.
Third we'll go with
BA:level.1

And fourth was CPEP-158.
Now that right there
is what
is supplying the choices.
What I need to do now is indicate
that we are going to be sharing with that
EWA WebPart. So I do that as
we've seen before through
the connections.
So if I do Modify WebPart, excuse me,
choose Edit then Connections. We sent to send filter values
to Excel Web Access.
And we are specifying
here, you kind of have to read this carefully.
Think of it as from the point of view
of the receiver of this. The receiver is going to
get filtered values from this WebPart
and we are going to stuff
that into, in this case, the tag name.
And again, that is the named values that
were defined in the Excel spraedsheet.
You should notice
something that happened automatically within SharePoint.
You'll notice that it no longer
uses the tag name as a parameter
as we are making that connection now using this
connection here. So finally let's
go ahead and modify
this. We just verify that we've got
this working the way that we
want. It has four tags that we can
choose from.
And let's see if there are any advanced filter options
that we'd like to choose here.
In this case that
looks good.
The title for this is just going to be called
Choose Tag. And we'll go ahead and say
OK. So the big finish to this is
let's exit edit mode and let's see what happens when we
make our choice. As you can see under
here, we see Choose Tag. If I click on this
I can see a list of the choices.
And for the choice,
I'd like to choose BA:level1 and I'll say OK.
And the connection
has now resulting in stuffing that
value, My Choice, into here which changes
the spreadsheet. OK, let's
move on to the next part of this
which is to configure that text
filter. And as we do this, I want to
show you something that I just noticed which appears
to be a bit of an anomaly. Here is my
filter for making a choice, and that worked just fine.
Here is my filter for making a
text entry. Now watch what happens when we take a look at
connections. I don't see
the send option, send filter value. I've just noticed in
testing that I don't see that option until
I name this WebPart. So I'm
going to say something like enter tag, or tag name
Let's just put it like that. And I'll go ahead and
say OK.
And since that now has been
given a name, under connections
you'll notice now those other options become available.
Now I'm not exactly sure, I haven't dug into the
reasons for that. I assume it's because
the other WebParts don't
know what the identity of that is. So you cannot
make that connection until it's named.
But in any event, that is something that you may run into, so
watch out for that. I'll go into
send filter values to the EWA WebPart.
Now again, the first thing we do
is we specify where this is going
within that EWA WebPart.
From the point of view of the WebPart, the
recipient here, it's going to get filter
values from this WebPart and we
are going to put that into,
in this case we'll put it into the start time.
So that's what we are going to actually be putting here.
I'm sorry, I labeled this wrong. I said we were going to
call this tag name. So this is where we are actually going to put
in the start time.
So that's the destination. Let me change this and
call it start time.
And now that we've made that
connection, you
may have noticed a couple of things.
First of all, the parameter is no longer there for start time.
And again, watch what happens when we
make an entry in there.
Here is my start time field. I'll go ahead and just say, let's
go with t. T stands for today at midnight.
Currently we are seeing starting at today at 3PM. And I press
return and it should update this to show today as of
midnight and we see that value has been passed there.
So that's how we would implement both this
choice option and the option of doing
a text input.