Microsoft 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 DOMAIN\UserName. 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.

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

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

In Part 3a I walked through how to create the SharePoint List that stores the data users enter. In this post, I will walk through how to create the Excel Services User Defined Function (UDF) that will pull this information from the SharePoint List into Excel Services.

Overview

Out of the box, Excel Services does not support pulling in data stored in SharePoint Lists. However, you can add this functionality using one of Excel Services' extensibility methods: User Defined Functions.

User Defined Functions (UDFs) are custom worksheet functions that can be used to extend the capabilities of Excel Services. Once implemented they work just like any other function, except that they run your own code. For more information on UDFs, see the blog posts Excel 2007 investments in UDFs Part 1, Part 2, and Part 3.

While you can build UDFs that work on both the client and server, for this post we are just going to create one that works on the server and that fetches the information from the SharePoint List and returns it into the Excel workbook.

The code for the UDF and the workbook is included at the end of this post.

Writing the code

Before you can write any code, the first thing you'll need to do is to create a Visual C# Class Library in Visual Studio. Once you've done that, you'll need to add references to Microsoft.Office.Excel.Server.Udf.dll, and Microsoft.SharePoint.dll (both of which are located your Office SharePoint Server's ISAPI folder).

When writing a UDF, the first step is to mark the class as a UDF class, like so:

…

[UdfClass]

public class Class1

{

…

This ensures that Excel Services knows that there are UDF functions within this class. If you remember back to Part 2 of this series, the function that we want to create is called GetMachines, and it takes as a parameter a string that specifies whether we want data for laptops or for desktops. It returns an array of values which we then input into our spreadsheet using an array formula. The function definition then looks like:

…

[UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]
public object[,] GetMachines(string MachineType)
{
}

…

You'll notice that I've marked it as a UDFMethod which tells Excel Services that this is the function that will be usable in your worksheet. Setting it as volatile ensures that it calculates all the time and won't have its values cached by Excel Services, while saying that it returns personal information is required to impersonate the user that is loading the workbook, which we do in order to access the SharePoint List as them, and not as the Excel Calculation Services' process account.

To impersonate the user, we'll use a simple function called impersonateUser(). All it does is try to get the Windows identity of the user accessing the workbook, and if it can, impersonate them. The function is:

// Causes the UDF to impersonate the user that loaded it through the EWA
private WindowsImpersonationContext impersonateUser()
{

WindowsIdentity wi = Thread.CurrentPrincipal.Identity as WindowsIdentity;
if (wi == null)
throw new InvalidOperationException("Can't get Windows identity.");
return wi.Impersonate();

}

Using that function, the first thing we need to do in our GetMachines function is impersonate the user like so:

…

// Impersonate the current user using the EWA and not the ECS
using (WindowsImpersonationContext wiContext = impersonateUser())
{

…

Now that we've created the class and method and impersonated the user, we need to go ahead and get the requested data from the SharePoint List:

…

try
{

// Declare constants
string serverName = http://server/;
string siteName = "site";
string listName = "list";
string viewName = ""; 

if (MachineType.Equals("Laptops"))

viewName = "Laptops";

else

viewName = "Desktops";

// Get objects corresponding to the SharePoint Site, List, and List View
SPSite site = new SPSite(serverName);
SPList list = site.AllWebs[siteName].Lists[listName];
SPView view = list.Views[viewName];

// Get a collection of the values in the specified List View
view.Update();
SPListItemCollection values = list.GetItems(new SPQuery(view));

// Create an object array to return to the grid
object[,] toExcelGrid = new object[values.Count, view.ViewFields.Count];        

…

In this example I am hard coding the server name, site name, and SharePoint List name. These could just as easily be passed in as arguments to the function, but this approach eases the usage for the end user. Also note that I use the MachineType parameter to determine the view that we will be getting list items from. We created the views when creating the SharePoint List in Part 3a, and the views only contain items for the type of machine specified by the MachineType parameter (laptops or desktops).

Now we just need to iterate through the list items, put them into the toExcelGrid object, and then return all that data to the Excel workbook:

…

// Iterate through each of the SPListItems in the view
int a = 0;
foreach (SPListItem currVal in values)
{

// Add every field value of current item to the array that will be returned
for (int i = 0; i < view.ViewFields.Count; i++)

{

// Get the current field
SPField field = currVal.Fields.GetFieldByInternalName(view.ViewFields[i]);

// Add the item to the array
toExcelGrid[a, i] = field.GetFieldValueAsText(currVal[field.Id]);

}

a++;

}

return toExcelGrid;

}

…

Finally, we need to add our catch event to return an error message if something goes wrong:

…

catch (System.Exception ex)
{

object[,] error = new object[1, 1];
error[0, 0] = ex.Message;
return error;

}

…

That's it!

For more details on UDFs, and a step by step walkthrough of how to create one, see the Excel Services User-Defined Functions article on MSDN.

Deploying your UDF

In order to ease management of your UDF files, start by creating a special folder for them on your server, something like C:\UDFs. Next, copy the Debug DLL that you just created into that folder. Now all we need to do is tell Excel Services about it and enable it.

To get to Excel Services' administration pages, click Start > Administrative Tools > SharePoint 3.0 Central Administration. Your default web browser will launch showing Central Admin. Click on the name of your shared service on the left (SharedServices1 by default), and then in the Excel Services Settings section, click on User-defined function assemblies.

This will take you into the UDF management page. Click Add User-Defined Function Assembly to add the one we just created. Enter the file local path to the UDF (C:\UDFs\GetMachines.dll), and pick File Path as the assembly location. Click Ok, reset your server (using iisreset), and your UDF is ready to go!

Testing your UDF

If you've created the SharePoint List and updated the static variables in the UDF correctly, then all you should need to do is upload the workbook we built in Part 2 and you should start see the values from the SharePoint List in your Excel workbook when it is rendered using Excel Services.

Community Clips

The folks over in Office Labs are testing out a new idea called Community Clips and I, personally, think it's a fantastic idea. It's a community site where anyone, not just Microsoft employees, can upload "how to" videos that take you step-by-step through accomplishing a particular task. Think of it as YouTube meets Office Online. If, like me, you're a visual learner, this is a great way to learn new tips and tricks on how to do any number of things with Microsoft products.  There are already quite a few Excel-related topics, from using freeze rows to creating your own UDFs in VBA.

There are a lot of Excel experts out there who have been sharing their knowledge via blogs, newsgroups, and the like.  My hope is that Community Clips catches on as an additional venue for these experts to share their knowledge. For those of you interested in contributing content, there's an Office Labs blog post that has tips for making good videos.

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

Today's author, Dan Parish, continues his discussion on building an Excel Services solution. Part 3 turned out to be a bit longer than we had anticipated so we've split it up into two parts: 3a and 3b.

Thus far in this series, I have given an overview of the solution, and detailed how to build the Excel workbook that is the core of how it works.

In today's post, I will walk through building the SharePoint List that users will input their machine information into.

Overview

In order to collect the information we want about each user's machine, we will have them enter that information into a SharePoint List. SharePoint Lists can store many different types of data, and can have some amount of data validation applied to them as well.

By using a SharePoint List we also get backup and restore capabilities, permissions, versioning (if enabled), and more, all for free.

To get started, you'll need a SharePoint site, and you'll need to be an owner of that site. Note that from here on for the screenshots, I assume you have a basic Team Site, however the same steps apply no matter what type of SharePoint site you are using.

Creating the SharePoint List

To create a SharePoint List, select Site Actions at the top right of the page, and then click Create. If you don't see a Site Actions button, or if there is no Create in the Site Actions menu, you aren't an owner on that site.

The next page that appears is a page detailing all of the different types of things you can create. We just want a simple list, so in the Custom Lists column, click on Custom List.

Finally, you'll be brought to the page on which you actually create your list. Call the list Machine Information, and click Create.

Adding columns to the List

Now that we have our List created, we need to add in the columns for the types of information we care about. Specifically, we want to collect the following information:

• Asset Number
• Number of CPUs
• CPU Speed (Ghz)
• Amount of RAM (GB)
• Hard Drive Space (GB)
• Machine Type (Laptop or Desktop)

To add these columns to your new List, click Settings > List Settings. You'll be taken to the settings page for your List, and you should notice a Columns section midway down the page. There are three columns in there by default:

• Title
• Created By
• Modified By

What we are going to do is to repurpose Title to be Asset Number, and we'll then add the other columns we require, leaving Created By and Modified By alone.

To repurpose Title, simply click on its name. You'll be brought to the Change Column: Machine Information page where you can change the column name. Change it to Asset Number and click OK.

Now we just need to add the remaining columns. To add a column, on the List settings page click Create column. You'll be taken to the Create Column: Machine Information page. Enter Number of CPUs for the column name, and select Number as the data type. Notice this changes the available settings further down the page.

Set the minimum number of CPUs to 1, the maximum to 8, and click OK. You have now created your first column! The next three are very similar. Go ahead and create the next three columns in the same way with these settings:

Once you've created those columns, there is only one more column to add, and that is for the Machine Type. Click Create column again, and on the Create Column: Machine Information page, enter Machine Type as the column name.

This time however, select Choice (menu to choose from) as the data type. This makes the available settings further down the page change. For the choices, enter Desktop and Laptop, each on a separate line, and click OK.

You've now finished creating your list!

Creating Views on the List

We do want to do one more thing however. In order to ease pulling only the Laptops or only the Desktops into our Excel workbook via the User Defined Function (UDF) we will be building in the next post, we need to create two 'views' on the list. Views can be applied to a list and can specify the columns to show (and hence return to our UDF), the order in which the entries should appear, what items to return, and more.

At the very bottom of the settings page, you'll see the Views section. It has one view in it already called All Items.

To start creating a new view, click Create view. On the next page, select Standard View as the type of view to create. You'll then be brought to the Create View: Machine Information page. We will create the Desktops view first, so go ahead and enter Desktops as the View Name.

In the Columns section you'll see three columns: one of checkboxes that specifies if the column should be shown in the view, one of the column names, and one of dropdowns that specifies in which order the columns appear.

Check and uncheck columns, and change the dropdowns on the following items to match the table below:

The next thing we want is for the results to be sorted by Created By. This is because we do a VLOOKUP in the Excel workbook on the user's name, and VLOOKUP requires the names to be in ascending order. So, in the Sort section, select Created By as the first column to sort by, and make sure that Show items in ascending order is selected.

The final thing we want to do is to have this view only return desktops (it is the Desktops view after all). To do this, we will use a filter on the list. So, in the Filter section, select the Show items only when the following is true option. Then, select Machine Type as the column to check, is equal to for the operation to perform, and enter Desktop in the textbox. This means that only items where Machine Type = Desktop will be shown in this view.

Click OK to create this view.

You will be returned to the List with the view you just created active (which you can see at the top right of the List).

We still need to create one more view however, for Laptops. Click the view dropdown that is currently showing Desktops, and select Create View. Now go through exactly the same steps as you did to create the Desktops view, except call the view Laptops, and for the filter you want to match on the word Laptop, not Desktop.

Once you've create the Laptops view you are finished! You can try entering sample data by clicking New in the List.

Conclusion

The SharePoint List is the data repository for all of the machine information that the users will be entering. By using a SharePoint List we get all of SharePoint's backup and restore, permissions, versioning, etc. functionality for free. By adding some data validation and views onto the list, we've made it easy for users to fill out the information, and easy for admins to get information out of it and maintain it.

In my next post I will walk through building the User Defined Function (UDF) that will pull this information out of the SharePoint List and into Excel Services.

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:

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.

VBA Focus Group at TechEd

Do you use VBA (or any other extensibility mechanism) in Office and are you headed to TechEd? If so the we would like to talk with you in an "invitation only" meeting. This is your chance to share your feedback and influence the work that Microsoft is doing in the programmability space. You must be available for either:

• TechEd Dev: Held at 4:30 on Thursday, June 5th
• TechEd ITPro: Held at 3:00pm on Monday, June 9th

If interested, please respond to the following questions to Amanda.Silver[at]microsoft.com (replace [at] with @):

• Dev week or ITPro week session?
• Please describe your current role.
• What's the size of your organization? Workgroup?
• How/when do you use VBA?
• Are you familiar with .NET and Visual Studio Tools for Office? If so, do you use those tools? If not, why not?

We want to hear from you -- come and make yourself heard!

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

Today's author, Dan Parish, a Program Manager on the Excel Services team, will discuss over the next few posts how he built an asset tracking application using Excel Services.

At the SharePoint Conference in Seattle in March, I did a presentation entitled "Leveraging Excel Services to Build Rich SharePoint Applications". The talk included a demonstration of how to build a SharePoint application that used Excel Services to solve a data collection scenario in which group administrators had to maintain a list of all the machines in their ever changing organization.

I promised at the end of the talk that I would put the source code that I showed online, but I've decided to take it one step further and create a series of five blog posts that will describe how to build this solution from the ground up. The last post will also include all the code for all five posts. The five posts will be:

1. The Overview (this one)
2. Creating the Workbook
3. Creating a SharePoint List and a User Defined Function
4. Building the Web Part Pages
5. Creating the Archive Web Part

The solution: Data collection of an organization's machine information

In our group it can be challenging keeping track of who currently owns a given machine. While this may be a problem specific to the way we work, it is generally applicable to everyone since it really boils down to a data collection problem.

If I have a machine that I'm not using anymore (maybe I got a new one, or my responsibilities changed) I can just give it to someone else in my group who may need it instead. This exchange happens all the time for various reasons, and is great for making sure everyone has the equipment they need. However, for our group administrators, this creates a problem: they need to know where all those machines are, and they need to know if they need replacing.

There have been many solutions to this in the past: we've emailed details of all our current machines to our admin, we've entered data into SharePoint Lists, and we've even filled out InfoPath forms sent through email. There are two problems with all of these solutions though. First, there is little motivation to fill them out quickly, and second, once our admin has all of the data, they still need to put it all together and analyze it, which they usually do in Excel.

This solution addresses both of those problems by leveraging the power of SharePoint combined with Excel Services. With this solution, our admin simply needs to send out an email with a link and tell everyone to follow it and fill out their machine information. After filling out our machine information, we get feedback on how our machines compare to the rest of the organization's machines. Our admin gets her own web part page that shows who has and hasn't filled out the survey, and a listing all of the machines that have been entered, color coded to show which need upgrades and which don't. There is no need to collect and analyze the information, it's all done automatically. Further, our admin can at any time archive a copy of the report for future record keeping.

Here is what the first page looks like:

Here is what the second page looks like:

In my next post I'll detail how to create the workbook that is displayed both to the people that fill out the form and to the admin, and to analyze the machine information and display the results, both to the people that fill it out and to the admin.

Ten Tips for Using Excel Services

A new MSDN article about Excel Services was just released. If you do any kind of work with Excel Services, whether you are just getting started with it or trying to do something a bit more advanced, this article is a pretty good read because it covers some of the more common pitfalls that we hear from customers.  The following topics are discussed:

• Making an Excel Workbook Compatible with Excel Services
• Configuring External Data Connections
• Configuring Excel Services to Work with Kerberos
• Saving an Excel Workbook to a SharePoint Document Library Programmatically
• Avoiding Exceptions in a UDF When Accessing the Caller's Identity
• Handling Dimension Issues for UDFs That Return Arrays
• Calling 32-Bit Native DLLs from UDFs on 64-Bit Operating Systems
• Quick Tips for Common Excel Services Issues

The article can be found here.

New Technical Article on Excel Services

There's a new article up on MSDN that describes a custom solution built by the IT department here at Microsoft that uses Excel Services. Rather than re-paraphrase, I'll "borrow" liberally from Shahar's description:

The solution is probably one of the most advanced implementation of Excel Services I have seen to date and includes lots of ingenious customizations - the white paper, specifically, talks about how to use Excel Services as a landing pad for data and then get the file stream from it, customize it (add stuff into it) and serve it up to the user.

The article can be found here.

PivotTables: Calculated Items

Today's author: David Gainer, a Program Manager on the Excel team.

PivotTables are designed to help users make sense of large amounts of data by providing an easy way to build a summarized report. In addition, PivotTables can be rearranged easily, so that once you have some summary data in a PivotTable, you can look at the same information in many different ways with only a few mouse clicks. For a slightly less brief overview of PivotTables as well as a list of the improvements made to the application in Excel 2007, please check out this blog post.

Today I wanted to take a look at calculated items in PivotTables … specifically, what they are, how to create them, and some examples of how they can be useful. I find them to be very helpful, so I wanted to do a little evangelism, as well as point out some little-known features.

Let's start with what they are. According to Microsoft Knowledge Base Article 161882, "A calculated item is a user-defined item in a PivotTable field that can perform calculations using the contents of other fields and items in the PivotTable." While that's technically accurate, it may not be clear. Perhaps a simpler way to think about calculated items is that it is a feature that allows you to create new items (that are not part of the original source data) that can appear in fields in your PivotTable. Here's an example (lifted from the aforementioned KBA) – a picture may be worth 1000 words.

Assume that you have some sales data that you want to summarize in a PivotTable. You have data for four different sales regions (North, South, East, West) for a few months sales.

With a few clicks, you could create a summary PivotTable that looks like this.

So far so good, but let's now pretend there has recently been a reorganization, and the North and East regions are now the NorthEast region. How can you reflect that in your PivotTable without having to modify all the source data? By creating a calculated item which represents the NorthEast region, of course. To do that, you need to first click one of the "Regions" in the PivotTable report. This gives Excel the context of what you are going to add a calculated item to … specifically, that you are going to add an item to "Regions". Next, on the PivotTable "Options" contextual ribbon, select the "Formulas" drop-down and then select "Calculated Item …".

This should present you with a dialog thus:

This dialog is used to create calculated items (unsurprisingly enough). While it may look a bit cryptic, it is pretty straightforward. The bottom-left control shows you the fields available in your PivotTable, the bottom-right control shows you the items in those fields, and the top controls allow you to create your calculated items. For example, to create a new NorthEast region, you simply need to type "NorthEast" in the Name control, = North + East in the Formula control …

… press the Add button, and then press OK to dismiss the dialog. Now you should see your PivotTable with a new item – NorthEast – listed with the rest of the Regions. You can see that Excel simply aggregated the values for the two regions in questions, which is what we asked Excel to do with our "= North + East" formula. Essentially, you have added new information to your PivotTable, and that information behaves just like all the other data in the PivotTable … you can sort it, filter it, pivot it, etc., all without restriction.

If I now use standard PivotTable filtering functionality to filter out the North and East regions, I have a simple three-line PivotTable that reflects the reorganized sales regions, not the underlying source data.

The fun is not quite over yet, though. Once you have created a calculated item, you can continue to modify it without necessarily returning to the dialog we just looked at. For starters, you can type over the caption "NorthEast" and Excel will remember the new caption. Additionally, if you click on one of the calculated fields, you will see that you can see the formula you entered in the dialog is also available (and editable) in the Excel formula bar. Here is a shot, for example, of what happens when I select one of the cells reporting data for the new NorthEast region and hit F2 (the hotkey to edit a formula).

From here I could change the formula (=North+South), add a constant (=North+East+100), or use any of Excel's native functions, and the value in the cell would update when I pressed Enter. It is worth bearing in mind that this changes the formula for that intersection only (in this case, "Apr" of the "NorthEast" region only, so if you want to change all the cells in one fell swoop, best to return to the Calculated Items dialog.

So that is an overview of the feature set. Now let's look at a few examples of more interesting usage cases that were created by one of my co-workers. First, let's look at an example where calculated items are used to show relationships between different classes of products.

As you can see the PivotTable above summarizes sales, cost of sales, and margin for different classes of products – alcoholic beverages, dairy, beverages, etc. In this case, the report author wanted to add another piece of information – the percentage of overall sales that were related to alcoholic beverages. They accomplished this by adding a calculated item to the "Product Department" field that looked like this: =IF('Alcoholic Beverages'=0,NA(),'Alcoholic Beverages'/SUM('Alcoholic Beverages',Beverages,Dairy)). You can see this calculation uses a few Excel functions (IF, NA, SUM) in addition to referencing other items in the field in order to calculate the percentage in question. The reason for the IF and the NA functions is to return #NA if there are no alcoholic sales instead of a divide by 0 error.

Here is another interesting example. It turns out you can use a special syntax to refer to "next" and "previous" items in a field.

In this example, the PivotTable author had actual financial results for FY02 – FY04, and wanted to create a forecast for the next fiscal year based on the actual for those three years. You can see in the screenshot above that they used the following formula: =IF(Year[-1]=0,Year[-2],2*Year[-1]-Year[-2]). While at first this may look pretty cryptic, it is actually fairly straightforward. The formula says "if last year's sales (which is what "Year[-1]" means) is 0, the projection is sales from 2 years ago, else the projection is two times last year's sales minus the sales from two years ago." The [-1] modifiers are simply used to tell Excel to refer to previous and next values in that field, and it is particularly (but not exclusively) useful for financial calculations involving time periods.

That summarizes the calculated items feature. There is a closely related feature called "calculated fields" which should be simple enough to figure out after reading this post (and there are other articles out there on MSFT and other web sites you can refer to if you are interested). If you have any feedback on this feature, we are all ears. Thanks!

Append Multiple Text Files into a Worksheet without Code

Today's author: Mark Gillis, an Excel and SharePoint writer, who's been through six versions of Office, survived to tell the tale, and picked up a thing or two along the way.

Excel doesn't have an easy way to append multiple text files into one worksheet through the user interface. From time to time I hear customers asking how to do this in an easy way. Do you have to use Access or VB code to solve this problem? No. There's a way to do it in Excel by using a simple SQL statement in the connection string.

Here's the deal. You have three CSV text files containing sales data, Beverages.csv, Condiments.csv, and Confections.csv. Each file has the same number of columns, the same column headers for each column, and the same type of data in each column. In this case, you're not going to use the Text Import Wizard. Rather, you'll use the Data Connection Wizard to create a connection (either OLEDB or ODBC) to one of the text files. Then, you'll modify the command type and text portion of the connection string and turn it into an SQL UNION statement, a query that effectively appends all of the text files.

Import a CSV file by using an OLEDB provider

Phase 1: Import one text file

1. In a new workbook, on the Data tab, in the Get External Data group, click From Other Sources, and then click Data Connection Wizard.
2. In the Select Data Source dialog box, click New Source, to display the Data Connection Wizard.
3. In the Welcome to the Data Connection Wizard page, click Other/Advanced to display the Data Link Properties dialog box.
4. On the Provider tab, select Microsoft Jet 4.0 OLE DB Provider, and then click Next.
5. On the Connections tab, in the Select or enter a database name box, enter the full path to the folder that contains the text files (just the path, and not with any filename).
   
6. Click the All tab, select Extended Properties, click Edit Value, enter Text;HDR=Yes, and then Click OK.
   
7. To ensure that you entered the correct information, click the Connection tab, and then click Test Connection. If you see the message, "Test connection succeeded", Click OK twice. If not, recheck your steps.
8. In the Select Database and Table page, under the Name column, select one of the text files that you want to import, such as Beverages#csv, and then click Next.
   
9. In the Save Data Connection File and Finish page, enter a name for the ODC file in the File Name box, and then click Finish.
10. In the Import Data dialog box, accept the default option values, and then click OK.

You have now imported one of the text files as an Excel table with an underlying query table.

Phase 2: Add the SQL statement and import all the text files

• With one of the cells in the Excel table selected, on the Data tab, in the Connections group, click Properties, and then in the External Data Properties dialog box, click the Connection Properties button next to the Name box.

• Click the Definition tab, and then in the Command Type box, select SQL.
• In the Command text box, remove the string, Beverages#csv, and then enter the following SQL statement:
  
  Note Use UNION when you want a unique set of rows, and remove duplicates in the process, like those pesky addresses from folks who endlessly surf the Web and have entered their personal information more than once. Use UNION ALL when you want to keep the duplicate rows for whatever reason. In general, the UNION ALL phrase should be faster, because there's no extra checking for duplicate rows.
• Click OK.
  Excel displays a message warning you that the connection string in the workbook is different than the one in the connection file you saved in step 9 above. Click Yes to continue. You can fix this a bit later.
• To close the External Data Properties dialog box and run the query, click OK.

You have reached the moment of truth. You should now see data from all three text files in one worksheet, like so. The data is sorted in asending order by Quarter so you can see some of the data from the other two text files.

If you now want to make the connection string in the connection file the same as in the workbook, get back to the Definition tab in the Connection Properties dialog box, and click Export Connection File to re-save the connection file with the new connection string.

Import a CSV file by using an ODBC driver

Now that you get the general idea, here are the ODBC instructions, but in somewhat briefer format. The basic steps are similar, and the results are the same. The main difference to be aware of is the slightly different form of the SQL statement syntax.

Phase 1: Define a User DSN (If necessary)

1. Open Microsoft Windows Control Panel, double-click the Administrative Tools icon, and then double-click the Data Sources (ODBC) icon.
2. In the ODBC Database Administrator dialog box, on the User DSN tab, click Add.
3. In the Create New Data Source dialog box, select Microsoft Text Driver (*.txt; *.csv), and then click Finish to display the ODBC Text Setup dialog box appears.
4. Enter a name in the Data Source Name.
5. Clear the Use Current Directory check box.
6. Click Select Directory.
7. In the Select Directory dialog box, locate the folder that contains the text file that you want to import, make sure that the text file appears in the list below the File Name box, and then click OK.
8. Click OK twice.

Phase 2: Import one text file

1. In a new workbook, on the Data tab, in the Get External Data group, click From Other Sources, and then click Data Connection Wizard.
2. In the Welcome to the Data Connection Wizard page, click ODBC DSN.
3. In the Connect to ODBC Data Source page, select the User DSN that you just created, and then click Next.
4. In the Select Database and Table page, under the Name column, select one of the text files that you want to import, such as Beverages.csv, and then click Next.
5. In the Save Data Connection File and Finish page, enter a name for the ODC file in the File Name box, and then click Finish.
6. In the Import Data dialog box, accept the default option values, and then click OK.

Phase 3: Add the SQL statement and import all the text files

• With one of the cells in the Excel table selected, on the Data tab, in the Connections group, click Properties, and then in the External Data Properties dialog box, click the Connection Properties button next to the Name box.

• Click the Definition tab, and in the Command Text box, remove the SQL statement, and enter the following SQL statement:
  
  Note The grave accent character (`) is required as a delimiter for each filename. This character is usually located under the tilde character (~) on a keyboard.
• Click OK, and then click Yes.
• To remove the External Data Properties dialog box, and run the query, click OK.

Try it out for yourself. Get the sample text files from the attachments at the bottom of this post.

Improving Sheet Selection

Today's author: Sam Radakovitz, a Program Manager on the Excel team that enjoys creating VBA applications in Excel.

A bit ago I got a question through email asking if we could change the 'grid selection color' in Excel. Or at least I called it 'grid selection color', it's basically what the cells look like when you select a block or multiple blocks of them:

As you can see, the color is a bit light and hard for some folks to see, especially on a laptop if you're looking at it from the wrong angle. Unfortunately, in Excel 2007 you can't really change the color of the selection. You used to be able to change the 'Selected Items' color in the Windows appearance settings but that doesn't work with Excel 2007. This is something we're looking to address in the Excel … but until then I wanted to try and provide an add-in solution. I tried a couple of solutions and I wasn't 100% successful with either attempt, but maybe a code savvy blog reader can pick up where I left off and finish the task.

Approach #1, Shapes

My first approach was to use OfficeArt shapes to create the selection. It's pretty easy to do code wise: you get a selection change event, you loop through each area in the selection and drop a shape on it:

I take the shape and inset it a little bit in the selection so the drag fill is still available, then pick a darker c