SharePoint 2010 : Publishing to Excel Services (part 2) - Allowing Parameter Input in Excel Web Access

Allowing Parameter Input in Excel Web Access

Another feature to be familiar with is the ability to name a cell in Excel 2010, publish it using Excel Services, and users will then be able to input a value through the Excel Web Access interface. Although somewhat time-consuming to configure, it can allow for interactivity through the Excel Web Access web part that might be very useful for encouraging end users to interact with the data. Bear in mind that any data input by end users in the Excel Web Access web part is not saved to the published spreadsheet, so is really only for “what if” scenarios.

The following example reflects a situation where an IT manager wants to show senior management the impact of total number of help desk resources on average time to resolve the help desk tickets that come in based on numbers from the previous year. She creates a base spreadsheet with the months and number of help desk tickets per month and then creates an equation for the Average Time to Resolve (Hrs) column that divides the total number of tickets by the variable that will be input by the Excel Web Access web part users. This allows users of the published worksheet to enter in different numbers in the Number of Resources cell and see the results over the course of the year. She is hoping this will enable her to convince senior management that the organization needs more help desk staff at certain times of the year if they want to meet their SLA of no more than four hours average time to revolve help desk tickets. This example also allows the IT manager to show off her Excel Services skills to show senior management another capability of SharePoint 2010.

Follow these steps to test the process:

1.	Create a new spreadsheet in Excel 2010.
2.	Provide the heading Month to column A in cell A1.
3.	Enter the text January in cell A2. Grab the lower-right corner of the cell while highlighting cell A2 and drag downward, until all the fields are populated with the months up to December.
4.	Enter Help Desk Tickets as the header in cell B1. Enter random numbers between 0 and 200 for cells B2 through B13.
5.	Enter Average Time to Resolve as the header in cell C1.
6.	Enter the text Number of Resources in cell A15.
7.	Select cell B15 and access the Formulas tab.
8.	Click Define Name in the Defined Names section of the Ribbon. Enter a name of NumberofResources and click OK. The results should look like Figure 12, with the exception of the actual values entered in cells B2 through B13. Figure 12. Naming a cell in an Excel 2010 worksheet.
9.	Select cell C2 and enter the following formula, which will use the named cell: =B2/(NumberofResources*4)
10.	The error #Div/0 error will display because there is no value in cell B15.
11.	Click cell C2 and drag down to cell C13, and all cells should display the #Div/0 error.
12.	Click the File tab, click Save & Send, and then click Save to SharePoint in the Save & Send column.
13.	Click the Publish Options on the right side, above the Recent Locations list.
14.	Click the Parameters tab, click Add, and select the box next to NumberofResources. Click OK, and the results should resemble Figure 13. Figure 13. Setting the publish options when publishing using Excel Services.
15.	Click the Show tab and access the drop-down menu and select Sheets. Select Sheet1 and click OK.
16.	Back on the Save & Send page, scroll down if needed and select Save As.
17.	Enter the full path for the SharePoint site and document library in the Filename field and include the filename of HelpDeskTickets (so in this example, it is http://abcsp1004/sites/chapter26/shared documents/HelpDeskTickets). Verify that Open with Excel in Browser is checked. Click Save.
18.	A browser window will open, and will display the worksheet, with a field on the right side titled Parameters, with a field next to the label NumberofResources. Enter a number such as 5 in the field and click Apply. The results will look like Figure 14. Figure 14. Results of entering a parameter value in the browser.

This example shows the steps involved with defining the name of a specific cell and then including that cell in the publishing process using Excel Services, as well as showing the interface that results. Multiple named cells can be defined in a workbook, which can then essentially be used as variables in the resulting content that is surfaced in the Excel Web Access web part. Combine this with tools such as charts and pivot tables and conditional formatting, and the results can be very powerful.