Back
Excel

Building an Asset Tracking Application in Excel Services – Part 4 of 5

Today’s author, Dan Parish, continues on his discussion of building an Excel Services solution.

In Part 3b I walked through creating a User Defined Function (UDF) that pulls information from a SharePoint List into Excel Services. In today’s post, I will describe how to create the two web part pages that make up the actual application: the page that end user’s see, and the page that the admin uses.

Overview

Now that we have our spreadsheet running on the server and displaying data entered into the SharePoint List, all we need to do is present this information in a user-friendly way. Using Excel Web Access (EWA) – Excel Services’ web part – we can seamlessly integrate this information into any existing site.

Creating the end user’s web part page

As a refresher, here is the page we are going to create:

The first thing we need to do is actually create the web part page itself. Virtually every page in SharePoint is a web part page, which is a page that you can add web parts to and remove web parts from. You can also create your own web part pages from scratch, and that’s what we are going to do here.

To create a new web part page, go to the site in which you created your SharePoint List in Part 3a, and click the Site Actions button located on the top right of the page, and then click Create. If you can’t see a Site Actions button, then you don’t have sufficient permissions to create a web part page on that site.

On the Create page you’ll see a wide assortment of things you can create. On the right hand side, under Web Pages, click Web Part Page to create a new web part page. Call this page Machines and pick the Full Page, Vertical layout. You can save the web part page to any Document Library you have (such as Shared Documents). Then click Create.

You’ll be taken to your new web part page in edit mode. The first thing we need to do now is add web parts. The page we want to create is actually composed of five web parts which are, from top to bottom:

  1. A Current User Filter which detects the current user’s name and passes it into connected web parts
  2. An EWA web part displaying the WelcomeMessage named range
  3. A Content Editor web part displaying the link to enter new information into the SharePoint List
  4. An EWA web part displaying the Laptops named range
  5. An EWA web part displaying the Desktops named range

To add a web part just click the big orange bar that says Add a Web Part and then select the web parts you want to add.

Once you’ve added all of the web parts, we need to start configuring them, which we’ll do starting from the top.

Configuring the Current User Filter

The Current User Filter is actually hidden when the page is rendered not in edit mode. What it does is detect the identity of the user visiting the web part page and send it into any connected web parts. We’ll connect the web parts in the next step, but first we want to change the format that the Current User Filter will send the user’s identity in.

By default, the Current User Filter sends the user’s identity in the form DOMAINUserName. We want it to send a friendly name (like Dan Parish) though. To change this, open the tool pane for the web part by clicking Edit > Modify Shared Web Part at the top right of the Current User Filter. When the page reloads you’ll be presented with the tool pane for this web part.

Select the radio button for SharePoint profile value for current user, and then select Name as the value. Now, if your SharePoint farm is configured correctly, the Current User Filter will send the friendly name instead of the username.

When you are done, click OK to apply your changes.

Configuring the EWA Web Parts

To configure the first EWA web part (the welcome message) start by clicking the Click here to open the tool pane link in the EWA. That will reload the page and display the EWA’s web part tool pane.

The first thing we need to do is pick the workbook we want to display. Do this by clicking the … to the right of the Workbook textbox which will launch a file picker. Use the file picker to browse for the workbook we created and published in the previous parts of this series. Once you’ve found it and selected it click OK (or double-click on the workbook).

The next thing we need to do is tell the EWA what we want to display in the workbook using the Named Item field. We could leave this blank to display the entire workbook, but in this case we only want to display the welcome message, so enter WelcomeMessage (which is the name of the named range encompassing that message). This will cause the EWA to only render that named range.

If you click Apply now, you’ll see the welcome message render in the EWA saying welcome to Mr. Nobody. However you’ll also see that the toolbar displays, and the web part isn’t sized correctly. To really make the EWA blend into the web part page, we need to tweak some more settings.

First, in the Toolbar and Title Bar section, set Type of Toolbar to None. This will turn off the toolbar for the EWA. Next, scroll all the way down in the tool pane and expand the Appearance section. Select that the web part should have a fixed height, and set it to 50 pixels. Finally, at the bottom of the Appearance section, set the Chrome Type to None.

Click OK, and you’ll see the EWA render just as you’d expect it to: sized correctly and integrated into the web part page. There is only one final issue: the Parameters Task Pane in the EWA is still showing the EmployeeName parameter. The EmployeeName parameter is the cell that we will be sending the user’s friendly name from the Current User Filter into. To learn more about parameters, see the Change workbook parameters in Excel Services article on Office Online.

There are two ways to hide the Parameters Task Pane: there is a web part property we could turn off in the EWA’s web part tool pane, or, if all parameters in a workbook are having their values sent in from other web parts, the EWA automatically hides the task pane. Every time you connect a parameter in an EWA to another web part it is removed from the task pane, and once they are all connected the task pane is automatically hidden. So, since we need to connect the EWA to the Current User Filter anyway, we’ll take that approach.

To connect the EWA to the Current User Filter, click Edit > Connections > Get Filter Values From > Current User Filter on the EWA. This will display the Configure Connection dialog box where you can select the parameter that you want the Current User Filter to send its value into. Select EmployeeName and click Finish.

You should now see Welcome <Your Name>! and no Parameters Task Pane.

To configure the other two EWA web parts follow the exact same steps (including connecting them to the Current User Filter, which is required so that they know which user to show the laptop/desktop results for) but for the Laptops EWA web part the named item to display is Laptops, and for the Desktops EWA web part it is Desktops. Also, enter 210 pixels for the height of both of these web parts.

Configuring the Content Editor Web Part

The final web part to configure on the end user’s web part page is the Content Editor web part. This is the web part that will contain the link to the SharePoint List where people will enter their machine information.

We want this web part to do two things:

  1. Link users to the SharePoint List where they will enter their machine information in such a way that when they submit their machine information they will be taken back to this web part page
  2. Convey information about what this page is for

The Content Editor Web Part allows you to enter information in one of two ways: using a Rich Text Editor, or by entering source code. We’ll use the Rich Text Editor.

First, open the tool pane for the Content Editor web part by clicking Edit > Modify Shared Web Part at the top right of the web part. When the page reloads and shows the web part tool pane, click Rich Text Editor… to launch the Rich Text Editor.

Enter the following text:

Add a new machine
Please ensure you add all machines currently in your possession, including desktops and laptops.

Shrink the size of the second line by highlighting it, clicking the Font Size button, and then selecting 1 as the font size.

Next, make Add a new machine into a link by highlighting it and clicking the Hyperlink button. This will open a new window where you can enter the hyperlink. The hyperlink you want to enter is:

http://<server>/<site>/Lists/Machines/NewForm.aspx?Source=/<site>/Shared Documents/Machines.aspx

Make sure to change <server> to your server and <site> to your site. The Source parameter is what tells the SharePoint List where to send the user after they have entered a new item. In this case, we want to send them back to our end user web part page.

You can now change any other formatting you like, and then click OK in the Rich Text Editor and again in the web part tool pane to commit your changes. You can then click Exit Edit Mode in the top right of the web part page to exit edit mode and see your web part page the same way that everyone else will.

Creating the Admin’s Web Part Page

As a refresher, here is the page we are going to create:

Since you just finished building the end user’s page you already know everything you need to know to build this page as well. There are only a few differences:

  1. When creating this web part page call it Machine Overview and pick the Header, Right Column, Body layout. We’ll add a custom web part to the right column in Part 5.
  2. Since the admin page shows data for all users it doesn’t need a Current User Filter. The web part page simply consists of three EWA web parts.
  3. From top to bottom, the named range each EWA web part displays is:
    1. EmployeeList (the list of which employees have and have not completed the survey)
    2. LaptopListing (the list of all laptops entered with conditional formatting showing which need to be upgraded or replaced)
    3. DesktopListing (the list of all desktops entered with conditional formatting showing which need to be upgraded or replaced)
  4. Each of the three EWA web parts should have a height of 300 pixels

The only thing that is different that we didn’t do in the end user’s web part page is that since we aren’t connecting the Current User Filter to each of the web parts on the admin page, we need to explicitly tell each EWA to not show the Parameters Task Pane. To do that, in the Navigation and Interactivity section of the EWA’s web part tool pane, uncheck Display Parameters Task Pane.

Once completed, you should now have both web part pages displaying all the relevant data in EWAs that are seamlessly integrated into your new web part pages.

In Part 5, we’ll walk through how to create a custom web part that archives these reports to a separate Document Library for record keeping.