Excel Services Overview

Excel Services allows you to publish spreadsheets and workbooks to a server in a controlled environment, where users can access calculated data and reports directly from the browser, but where you can set controls to limit the data users can see and safeguard confidential elements, such as formulas or connection details. This means users don’t have to have the Excel application on their computer to view, create, and author spreadsheets.

Users can also view a snapshot of the file in Excel, which protects source file and formulas but limits the user to a read-only view of the calculated data. Excel Services also lets users interact with pivot table reports; reports have filtering and sorting functions so users can generate a view to meet their requirements. For example, rather than creating individual reports for each project manager on the progress of your individual projects, you can create a single project status report, where users can filter results based on time, project manager, and project name. This means you can serve a greater audience with a single report, while still providing a useful tool to project managers so they can generate reports of their own.

This section reviews the various features of Excel Services including a special template called the Report Center that contains many of the elements available in SharePoint to create an interactive reporting environment.

The Report Center

SharePoint 2007 has a built-in site template containing many of the common elements that an organization might use to create a centralized reporting site called the Report Center. Using preconfigured elements saves you time as you build your organization’s reporting environment. In this section, you learn the components that make up this site template and the steps for creating a centralized reporting portal site for your organization. The Report Center contains the following elements by default:

• Instructional Information:   These are instructions in the Web Parts of the home page of the site. To help you get started, this information tells you how to effectively use the elements available in the Report Center template.

• Key Performance Indicators (KPIs):   Many organizations set goals for certain operational or strategic aspects of the business and then track progress against them using real-time information. The Report Center template features a KPI list containing sample content and a Web Part to display graphically the current status of the organization at meeting those goals. KPIs can be created based on manual information or by connecting to another information system using a data connection.

• Sample Dashboard and Associated Data:   A Dashboard is a web page containing multiple reports and graphical indicators in an easy-to-consume format. Dashboards can combine all the key reporting elements available in SharePoint.

• Sample Reports:   The Report Center contains a sample Excel document to demonstrate how a single spreadsheet can be viewed via the browser as a single report or within Web Parts in a Dashboard. The sample report contains a pivot table, which is a tabular report that allows for a high level of interaction by containing filter fields and customizable fields allowing users to drag, drop, and filter information on the fly.

• Data Connection Library:   Sometimes reports can feature information from other information systems such as a database. These information systems are called data sources. Using Excel 2007, users can connect to these data sources to include information directly in their spreadsheets. Rather than having every user create his own data connection for each file for which he wants to include information from a data source, a data connection library can be used as a central repository for data connection files.

The five Try It Outs in this section walk you through using the Report Center. First, you create a performance reporting site using the Report Center template that acts as the base location for many of the examples you complete in this chapter. Once you create the Report Center, it’s critical that you add it to the Trusted File Locations of the SharePoint environment, which you commonly do through the Shared Services Administration site. If you don’t perform this step, users will be unable to access the site. In the third Try It Out, you see how an Excel workbook will display in the site and perform in the browser by viewing the workbook sample that is provided as part of the Report Center site. This exercise shows that you can have users view and manipulate data in Read-Only mode without them seeing sensitive information or formulas. Next, you edit data in the sample spreadsheet using Excel and view these changes in the browser with versioning to see how History is affected. Only a select group of users in your environment will have Edit access to the workbooks, while the majority of users will be limited to either Read-Only access of copies of the workbooks, snapshots, or strictly viewing the files through the browser. For the final Try It Out, you see the various ways in which users with Read or View access to the files can interact with the workbooks.

Try It Out-Create a Report Center Site

In this example, you create a new site within a site collection. This becomes your central location for shared corporate reports. Although this site will contain many elements that are useful for your organization as a dynamic reporting portal, it will require a significant amount of customization and configuration before you can mold it to your corporate needs.

Tip 

Whenever a site collection is created from the Collaboration portal site template, it contains a Report Center site template already. In this example, your new site becomes something on which you build future exercises. You can create this site within any site collection. For more information on site templates, see Chapter 8.

For this exercise, you give your site unique permissions. Once you have properly configured your site, you may want to give others access to it. Also given the nature of this site and the type of content that you will make available, you will likely want to manage permissions independent of all other primary intranet sites.

1. From the home page of your Corporate Intranet site, select Site Actions Create Site.

2. Give your site a title. For this example, type Performance for the title.

3. Enter a description for your site. For this example, enter the following:

   Important 

   Central performance reporting site.

4. Name the URL for the site. For this example, use performance.

5. For the Template selection, click the Enterprise tab and select Report Center.

6. For Permissions, select Unique Permissions until everything is configured.

7. For Navigation Inheritance, select Yes.

8. Click the Create button.

9. Click the OK button to accept the default site groups for the site. You are redirected to a new site, as shown in Figure 11-1.

   
   Figure 11-1

How It Works

The site will contain instructional information on how you can get started, as well as sample reports and Dashboards that you can extend or review for ideas on presenting information. Before users can access reports, such as the sample Dashboard, you need to add the site to the Trusted File Locations in the Central Excel Services settings - the subject of the next Try It Out. Otherwise, users may receive “access denied” errors when they click Sample from the Quick Launch bar, as shown in Figure 11-2.

Figure 11-2

Try It Out-Add Report Center to Trusted File Location

In this example, you add the site you created in the last Try It Out to the Trusted File Locations of the SharePoint Server Farm for Excel Services. You cannot load or access an Excel Services workbook in the browser unless you store it in a location that the system trusts. When specifying a location, you may select from one of the following choices:

• Windows SharePoint Services Location:   Select this option when the location is a SharePoint site.

• File Share:   Select this option if the location is a shared file folder location on your network.

• Web Server Address:   Select this option if the location is a web address other than a SharePoint site.

When you specify the address of your SharePoint site, you can list the exact URL of your Performance site, but for this example you list the top-level site of the site collection, which allows you to create more Excel Services-based sites within the site collection in the future and have it automatically recognized as a trusted location.

1. From the Central Administration site of your SharePoint environment, click the name of the Shared Services Administration site that controls your settings for Excel Services.

2. Select Trusted File Locations from the Excel Services Settings group.

3. Click the Add Trusted File Location button from the toolbar.

4. Enter the address of your site collection into the Address field.

5. For Location Type, select Windows SharePoint Services.

6. Select the check box to trust children. This ensures that all future sites you add to this site collection are automatically added to the trusted sites location.

7. For workbook properties, change the maximum supported size to 8. This helps you manage the server’s overall performance by limiting the size of the workbooks that are added. You can identify the optimal size based on your server farm’s configuration and available resources, as well as by consulting with business users to determine the average size of a workbook.

8. Click the OK button.

How It Works

When you return to your performance site now and select the Sample link from the Quick Launch bar, the sample Dashboard should display as shown in Figure 11-3. If not, you may need to close any active browser sessions that are connected to the site and reload the site in a new browser session to refresh any security settings.

Figure 11-3

Try It Out-Interact with Excel Services Workbook

In this example, you load a sample workbook from the sample Reports document library for the performance site. Even if you have the Excel client application installed, the sample automatically loads in the browser. You see the report in a series of views, each of which are individual sheets in the workbook; you can switch between views by selecting a different view in a drop-down menu. You can update data to retrieve the latest workbook version and recalculate values available since the last time the owner uploaded the report. Multiple simultaneous launches of the same reports do not affect server performance because the server will cache much of the data. Users can filter or change the sort order of the data so they can create pivot-type reports. Pivot reports have a larger amount of data and allow users to drill down to details.

1. Notice that the Quick Launch bar of a site created from the Report Center site template is slightly different than the other standard site templates. Click the Reports heading.

2. Click the SampleWorkbook file in the library that exists when the site is created from the Report Center template. The workbook opens in the browser displaying the default view of the report as shown in Figure 11-4.

   
   Figure 11-4

3. Expand the Update menu item to view the available actions for this workbook. From this area, you can update any connections or recalculate the figures that are in the workbook.

4. From the row above the company names, select the down arrow to expand the Filter and Sort menu. Select the Filter menu item. The Filter dialog box appears, as shown in Figure 11-5.

   
   Figure 11-5

5. Unselect the check box next to Select All. This clears all other check boxes next to the company names. Select the check boxes for only Contoso, Inc. and Fabrikam, Inc.

6. Click the OK button. The report only displays information for each of the companies that you selected in step 5.

7. From the View drop-down menu on the right, select CC. The report once again changes to display a pie chart report from the same workbook.

8. To return to the document library, click the Go Back to Reports Library link at the top left of the page.

How It Works

You’ll notice that you cannot view formulas or specific connection details and that data is presented in a read-only format. Users with Reader rights can open a complete copy of the spreadsheet in Excel on their local drive to make changes, but the source file remains unaffected. If a user has rights beyond View Item, he or she can also open the file using the client application either as the complete workbook or a snapshot. Users with rights to edit the file can do so in Excel within SharePoint as well as change formulas.

Try It Out-Edit an Existing Publishing Workbook

For this exercise, you walk through the steps of editing the sample workbook that was published as a major version to a Reports Library. As discussed in Chapter 3, when a file is published as a major version, it becomes available to all users of a site. Changes can be made to the file and then republished. You will make changes to data in the published sample spreadsheet using Excel and then view these changes in the browser. To demonstrate the versioning settings of the library, you will restore the file to its original version.

1. From the home page of your performance site, select View All Site Content from the Quick Launch bar.

2. Select the Reports Library.

3. Hover your mouse over the SampleWorkbook document to expose the contextual menu and select the Edit in Microsoft Office Excel option.

4. If you receive a warning that the file may not be safe, select the OK button. Your worksheet opens with the various views seen through the browser in Figure 11-3, consolidated on a single page.

5. Review the values shown in the cell range of A1:E8, as shown in Figure 11-6. The values you receive will likely be different than those shown in the figure.

   
   Figure 11-6

6. Scroll over to Column S. If you select a cell such as S2 in this column, you notice that you can see the formula that drives the collection of data for the workbook. The Rand() function returns a random integer, and the formula converts the integer to a number by multiplying it by 100. The returned value is then rounded to a specific number of digits. Change the formula to something different such as:

     =ROUND(RAND()*100,4) 

7. Copy the formula down to each of the cells in the column by dragging the bottom-right corner of the selection box over those cells, as shown in Figure 11-7.

   
   Figure 11-7

8. Repeat the same process for Column R.

9. Select the Data tab in the Excel Ribbon.

10. Click the Refresh All button. The data should update and the values will be different than those in step 5. See Figure 11-8 for an example.

    
    Figure 11-8

11. Select the down arrow of the Filter and Sort control just below the B column label. Unselect Litware, Inc. as a value to display in the report and click the OK button on the filter options box.

12. Close the document.

13. You are prompted to save your changes. Select the Yes button.

14. Click the name of the document. The spreadsheet opens in the browser showing the data with four decimal places as changed upon your edits. Also notice that the Litware, Inc. values are still filtered out of the report.

15. Click the Go Back to “Reports Library” link.

16. Hover your mouse over the SampleWorkbook document to expose the contextual menu. Select the Version History item from the menu.

17. Hover your mouse over Version 0.1 to expose the contextual menu and click Restore. The original version of your document is restored. If you return to the document library and click the workbook, you should see the workbook in its original format showing items rounded off with no decimal places. You will also see that Litware, Inc. is displayed again by default when the report loads.

Tip 

For more on viewing a document’s history, see Chapter 3.

When reviewing the version history of the document, you’ll notice that the document is still in draft mode and has not been published as a major version. To publish the document, you must select Publish a Major Version from the contextual menu of the report from within the document library. This makes the document available for other users who have limited rights such as Read or View.

Try It Out-View a Snapshot of a Report in Excel

In this Try It Out, you add a new user to the site with the View Only right and complete the exercise logged in to the site as that user. Members with the View Only right can view pages and documents but only via the browser or as a snapshot. As mentioned earlier, a snapshot is a subset of the original workbook that allows limited-rights users to interact with the workbook without giving them access to formulas or data connections.

Before you can do these steps, you need to do the following:

• Make sure you publish the SampleWorkbook document as a major version by selecting the Publish as Major Version option from the document’s contextual menu.

• Add a test user to the site by assigning them the View Only right under the Site Permissions settings.

• Sign in as the test user by selecting Sign In as Different User from the Welcome menu.

Tip 

For more information on adding a user to a SharePoint site and signing in a different user, see the appropriate Try It Out sections in Chapter 9.

In this exercise, you first open a snapshot of the sample workbook from within the report itself and then open it directly from the library. This approach is useful if a limited-access user can open the actual workbook, but wants to perform what-if analysis. You then log in to the performance site as a user with limited access. For users with the View Only option, the only way to access reports is through the server-rendered versions or Excel Snapshots. These users can access the data but not the logic or calculations that are going on in the background to present the data.

1. From the home page of your performance site, click the Reports link from the Quick Launch bar.

2. Hover your mouse over the SampleWorkbook document to expose the contextual menu.

3. Select Snapshot in Excel.

4. You may receive a security warning asking you to confirm you trust the location. Click the OK button to continue loading the application. The various views of the report open in Excel as separate sheets.

5. Enter a value of 100 into cell B3. The cell updates. However, if you try to save the document, you receive a warning that the document is only open as a read-only document. You can optionally save a copy of the document locally; however, you cannot save the document back to the server.

6. Close the document without saving changes.

How It Works

You notice that when you select to view the snapshot of the document, a browser-based version of the spreadsheet opens as well. From the browser you have the ability to refresh data connections, filter data, and open a snapshot from that location as well.

Tip 

To complete the remaining exercises in this chapter, make sure you log back in to the performance site as yourself rather than the limited access user from this example.

Publishing a Workbook to SharePoint

So far, you’ve looked at ways you can interact with existing workbooks that had been added to your performance site as sample data. Now, it’s time to publish your own workbooks to a SharePoint site. You have two ways to publish a workbook:

• Uploading the workbook using the upload methods in a SharePoint document library. In cases where you need to publish the work of a colleague or someone outside the company, this is more appropriate.

• Publishing the workbook directly from Excel. This is usually more efficient to do as part of your creation processes.

In the next couple of exercises, you get a chance perform both of these methods. For this first method, shown in the first Try It Out, you want others to access the data of a document but not the calculations or connection details. You upload the workbook to the Report Center, but once it is uploaded, you can restrict access to View-Only. In the second Try It Out, you publish directly from Excel to the SharePoint site. This approach may be more realistic for scenarios where you are creating the document in Excel and want to make it available to others in the organization immediately.

Try It Out-Upload a Workbook to a Document Library

As the SharePoint manager for your company, you receive a request from the company’s accountant to publish financial projections to the Corporate Performance portal so that employees can stay up-to-date on goals and objectives for the next five years, which are expected to be critical for growth in the global marketplace. You upload the report using the same procedure for adding a document to a document library that you explored in Chapter 3, during which you are prompted to fill in content that’s associated with the Report content type. This information is displayed to users in the default view of the library.

Once you upload the document, you set permissions so that all site visitors have the View Item right, which allows users to view the document in the browser or a report snapshot in Excel. For users to view the document, you need to publish it as a major version. By default, the Reports Library has major and minor versioning enabled. Therefore, the majority of users will not be able to see the document in the library while it is in draft mode or saved as a minor version. When you publish the report as a major version, it would be available to all users.

1. From the Reports Library of your performance site, select the Upload button from the toolbar.

2. Browse to the location of the resource materials for this chapter (www.wrox.com) and select the file titled 5-year financial plan.xlsx.

3. Click the OK button.

4. Ensure that Report is selected as the Content Type.

5. Enter a title and description for the document. For this example, enter 5-year financial plan and the following for description:

   Important 

   This report highlights our organization’s goals and objectives for the next five years.

6. Select the check box to save to report history.

7. Enter your own name for the report owner. In a real-life scenario, you would likely select the Accountant as the owner because this is the person whom users would contact if they had any questions related to the report.

8. Select Category 1 for the Report Category. You can customize this column to suit your organiza-tion’s specific categories using the same method for updating any standard list column, as shown in Chapter 4.

9. For Report Status, select Preliminary.

10. Click the OK button. You are returned to the Reports Library.

11. Hover your mouse over the document and select Manage Permissions from the document’s contextual menu.

12. Select Actions Edit Permissions. Adialog box appears, click the OK button to continue.

13. Select the Performance Members and Performance Visitors groups, and select Actions Edit User Permissions.

14. Select the View Only right from the list of permissions, and click the OK button.

15. Return to the Reports Library and select Publish a Major Version from the document’s contextual menu. You can add comments to the document to describe the current version and click the OK button.

16. Click the name of the document (in this case, 5-year financial plan.xlsx) to open the report.

How It Works

The document opens in the browser, as shown in Figure 11-9. Users can access the data of a document but not the calculations or connection details.

Figure 11-9

Try It Out-Publish a Workbook from Excel

You have just finished creating a pivot table report outlining performance on a recent project that your team has completed. You want to publish a chart from the workbook to your performance site to share the information with others. Because you have the document saved on your computer, you will publish the document directly from the application. During this process, you opt to publish the document to an Excel Services location. You do this by selecting the Excel Services option from the Server tab of the Office Button menu.

From the Excel Service Options menu, you can select what information is published to the Reports Library. Because you are interested in sharing only the chart information from the server, you select only that sheet from the list of available sheets. All other sheets are still accessible for the Chart view, but those sheets are not accessible via Excel Services. You publish a major version to ensure that the document is available to other users of the site.

1. Open the file Project Performance.xls from the resources associated with this chapter (see www.wrox.com).

2. Click the Office Button to expose the contextual menu.

3. Click Publish Excel Services.

4. Click the Excel Services Options button. The Excel Services Options dialog box appears as shown in Figure 11-10.

   
   Figure 11-10

5. From the Show drop-down menu, select Sheets and select only the Budget Plan Chart sheet.

6. Click the OK button.

7. For the file name, and enter the URL of your performance site.

8. Select the Reports Library.

9. Save your report in the library, keeping the check box selected to Open in Excel Services.

10. For Document Type, ensure that Report is selected and click the OK button. The report is published to the document library and opens automatically, displaying the chart as shown in Figure 11-11.

    
    Figure 11-11

11. Return to the library and select Publish a Major Version from the document’s contextual menu.

Excel Web Access Web Part

Sometimes you don’t want to display an entire spreadsheet in a separate view, but a portion of it within a page that contains other information. You may do this to emphasize a certain aspect of the information, or you may need to show a larger picture of information and the spreadsheet data represents a single piece of a puzzle. To support those situations, you use Excel Services to expose the Excel Web Access Web Part.

Try It Out-Display a Workbook in a Web Part

Once you finish publishing your project’s performance to the Reports Library (see the last several Try It Outs), the chart becomes available for others to see whenever they click the report. However, you want to add a Web Part to the performance site’s main page so users can view the chart as soon as they visit the site. This helps your team follow its progress. In this example, you see how to display specific worksheets in an Excel Services Web Part. To do this, you must add an Excel Web Access Web Part to the main page of your performance website. You must then connect it to a workbook.

1. From the home page of your performance site, select Site Actions Edit Page.

2. From the top-left zone, select Add a Web Part.

3. Select the Excel Web Access Web Part from the Business Data group and click the Add button. The page refreshes to show an unconfigured Excel Web Access Web Part displayed in the top-left zone.

4. Click the link displayed in the Web Part to open the tool pane.

5. Click the icon to the right of the Workbook field to launch the File Selection box. Double-click the Reports Library and then double-click the Project Performance report.

6. From the Toolbar and Title Bar options, select None for the Type of Toolbar.

7. For the Toolbar Menu Command, unselect each of the four check boxes Display as Options.

8. Click Apply, and then OK to save the Web Part settings.

9. Publish the page to finalized changes and make the page available to all users.

How It Works

By selecting the icon next to the Workbook field, you can browse to the location where your workbook is stored. You can determine the level of interactivity that users can have with the workbook by selecting what toolbar displays and whether users have the ability to refresh collections or recalculate data. When you perform step 9, you are redirected to the published version of the page that looks similar to Figure 11-12.

Figure 11-12