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:
Month
to column A in cell A1.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.Help Desk Tickets
as the header in cell B1. Enter random numbers between 0 and 200 for cells B2 through B13.Average Time to Resolve
as the header in cell C1.Number of Resources
in cell A15.NumberofResources
and click OK. The results should look like Figure 26.13, with the exception of the actual values entered in cells B2 through B13.
Figure 26.13. Naming a cell in an Excel 2010 worksheet.
=B2/(NumberofResources*4)
Figure 26.14. Setting the publish options when publishing using Excel Services.
http://abcsp1004/sites/chapter26/shared documents/HelpDeskTickets
). Verify that Open with Excel in Browser is checked. Click Save.Figure 26.15. 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.