Back
Excel

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

Today’s author, Dan Parish, a Program Manager on the Excel Services team, continues on with his multi-part post about building an application using Excel Services.

In my last post I gave an overview of the asset tracking application that I am going to spend the next four posts describing how to build.

The core of the application is the workbook it is based on. The workbook is what really makes this application work, since it is what pulls in the data users entered, figures out who has and hasn’t filled out their machine information, and applies all of the conditional formatting that makes analyzing the data so easy.

This post will detail how the workbook is constructed, but won’t get into how the machine information entered by users is pulled into the workbook from SharePoint. That’s the next post.

The workbook is available for download in the attachments at the bottom of this post.

Structure of the workbook

The workbook itself consists of three sheets: Employees, Machines, and Data. The Employees sheet has references to the Machines sheet, and the Machines sheet has references to the Data sheet. So, in order to best describe how everything in the workbook works together, I’m going to explain the sheets in reverse order, up the dependency chain.

The Data sheet

The Data sheet is where the data in the SharePoint List is pulled using a User Defined Function (UDF). However for now, since we aren’t building that until the next post, I’ve entered some fake data in for Laptops so you can see the spreadsheet working.

Here’s what the sheet looks like with some data in it:

The Laptops portion of the sheet is exactly what it will look like when it is pulling in live data in Excel Services from the SharePoint List using the UDF. The Desktops section shows you what the workbook will look like in Excel.

Both the Laptops section and the Desktops sections are array formulas that call the GetMachines UDF. GetMachines takes one parameter, which is the type of machine to fetch information for. So, for Laptops, the function is {=GetMachines(“Laptops”)} and for Desktops the function is {=GetMachines(“Desktops”)}. I’ll get into more detail regarding how the UDF works in the next post, but for now, the important thing to note is that even when it is bringing in data (as shown for Laptops), rows that don’t have any data will show #N/A.

That’s the Data sheet. It pulls in data from the SharePoint List using a UDF, and is then referenced by the Machines sheet.

The Machines sheet

The Machines sheet does three things:

  1. References the data on the data sheet, but eliminates the errors and adds conditional formatting
  2. Averages all of the laptop data and desktop data
  3. Averages the laptop and desktop data for the current user

Here’s what the Machines sheet looks like, continuing with the sample data entered above:

The Laptops and Desktops sections on the bottom simply reference their corresponding cells on the Data sheet, but wrap them in IFERROR functions that show blank if there is an error. A sample formula from these sections is the following, for Employee from cell B16: =IFERROR(Data!B5, “”).

These sections are shown on the admin web part page, so this helps to keep the display looking nice. There is also a custom conditional formatting rule applied to the data in these sections. The rule colors anything that is < 50% of the average value for all machines red, since if a machine is at or above average it doesn’t need upgrading, and this helps our admin to easily spot which components of certain machines are in need of an upgrade.

The Laptops and Desktops sections at the top of the page are shown on the page end users see before and after they enter their machine information. Each of the averages in the Division Averages portion simply uses the AVERAGE function to average all of the values in the corresponding section below. It is wrapped in an IFERROR formula in case there are no values, in which case it shows “No Laptops” or “No Desktops”. A sample formula for Average CPUs in cell C5 is the following: =IFERROR(AVERAGE(D16:D30), “No Laptops”).

The Your Averages portion averages all of the machines where Employee Name matches the name of the person viewing the web part page that is passed into a cell on the Employee sheet (which I’ll discuss in the next section). To do this, it simply uses an AVERAGEIF function, again wrapped in an IFERROR formula in case there are no values. A sample formula for Average RAM in cell F9 is: =IFERROR(AVERAGEIF($B$16:$B$30, EmployeeName, $F$16:$F$30), “No Laptops”).

That’s all that the Machines sheet does. It references the Data sheet to present its data nicer and with conditional formatting, and does some quick reporting on the data using the AVERAGE and AVERAGEIF formulas.

The Employees sheet

The Employees sheet does two things:

  1. Takes a parameter input for the name of the current user and generates the welcome message shown on the first page
  2. References the Machines sheet to calculate which users have entered their machine information and which haven’t

Here’s what the Employees sheet looks like, continuing with the sample data from above:

When Excel Services is rendering a workbook in a web part page, you can set it up to receive values into cells specially marked as ‘parameters’ from other web parts. You can see a previous post of mine titled Using Parameters in Dashboards to understand how to create parameters and connect them to other web parts. I’ll also walk through some of that in Part 3 of this series.

On the Employees sheet, the named range EmployeeName, which is located in cell E3, is a parameter. It will be connected to another web part that will send in the name of the current user. By default, the value of the cell is set to Mr. Nobody. You can change that cell to see the workbook start working. For example, enter “Employee #2″ and you’ll see the welcome message change to “Welcome Employee #2!”. If you then switch back to the Machines sheet, you’ll see that there are now values in the Your Averages portion of the Laptops section.

The welcome message is created by simply concatenating ‘Welcome’ with EmployeeName, and then with ‘!’ using the following formula: =CONCATENATE(“Welcome “, E3, “!”).

The Employee Information section below has three columns: people who have completed the survey, people who haven’t, and the full list of employees. The first two columns are shown on the admin’s web part page.

The full list of employees is just data entered directly into the spreadsheet. It could be pulled from an external data source, but for this example, I just entered it in manually. Note that it is important that this data is entered in alphabetical order. The list of employees that have completed the survey is simply a unique list of all of the employees that have entered either a laptop or a desktop, and is created by examining the two Employee columns (one in the Laptops section and one in the Desktops section) on the Machines sheet. The list of employees that haven’t completed the survey is then the list of employees that are in the full employee list but that aren’t in the list of employees that have completed the survey.

Now, if I wasn’t as concerned with the presentation of this information, I could stop here. However, to get the presentation I wanted, which has no duplicate entries and no spaces between entries, I needed to do some more advanced formula manipulation.

In order to not clutter up the UI that is shown to the admin, several columns are hidden. When unhidden, you can see that Column A contains a list titled ‘Uber List’ and Column B contains a list titled ‘No Dups’.

The Uber List is the listing of all employee names that are in the Employee columns in the Laptops and Desktops sections on the Machines page. This means that names are repeated, and that there are blank cells followed by cells later that have names. For example, cell A8 contains the following formula: =IF(Machines!B18=0, “”, Machines!B18). So, if there is a value in Machines!B18, it fills in the same value, otherwise it fills in a blank space.

The No Dups list is the Uber List with all of the duplicate names removed. This works by only filling in an employee name if that name has only appeared once in the list before it. The following table demonstrates how this works:

Uber List Value

Count of this name earlier in the list

Dup List Value

Dan Parish

1

Dan Parish

Dan Parish

2

 

Joseph Chirilov

1

Joseph Chirilov

Kerry Westphal

1

Kerry Westphal

Kerry Westphal

2

 

Each cell in the Dup List column simply counts the occurrences of the cell next to it in the Uber List, and if the number of occurrences is one, it shows the value. If it is anything else, it shows nothing. For example, the formula in cell B10 is: =IF(COUNTIF($A$8:A10, A10)=1, A10, “”).

Finally, I wanted to show the Completed Survey column without the blank cells. To remove the blanks cells, I used a technique shown excellently here. This also meant that the Completed Survey column ended up with a named range of CompletedSurveyNoBlanks.

The next thing I needed to do was create the inverse list: the list of people who are in the Full List of Employees, but who aren’t in the Completed Survey list. This is done in the hidden Column F. The formula used does exactly what I just described. For each employee in the Full List of Employees, it looks to see if they are listed in the Completed Survey list (using the VLOOKUP function). If they are, nothing is filled into the cell. If they aren’t, then the employee’s name from the Full List of Employees is filled in. For example, the following formula is used in cell F8: =IF(IFERROR(VLOOKUP(I8,CompletedSurveyNoBlanks,1,FALSE), “”) = “”, I8, “”).

The list we just created of people that haven’t filled out the survey has blank cells though. Like for the Completed Survey list, I wanted the Haven’t Completed Survey list to not have any blank cells. So, to remove those blank cells I used exactly the same method I used to remove the blank cells for the Completed Survey column.

I then hid Column A-C and Column F, producing the sheet shown above.

Conclusion

As I stated at the beginning of this post, this workbook is really the core of the asset tracking application. The vast majority of the workbook is quite simple to build, and can be maintained by a business user rather than a developer since the entire model is in Excel.

In my next post I’ll be writing about how to build the SharePoint List that users will enter their machine data into, and how to write the UDF that pulls that information from the SharePoint List into Excel Services.