Share Excel 2007 worksheets and KPIs by using dashboards

You can use a dashboard page to show a snapshot of data from your Microsoft Office Excel 2007 worksheet by displaying the worksheet in a Microsoft Office Excel Web Access Web Part on a Microsoft Office SharePoint Server 2007 site. Your dashboard can also display Key Performance Indicators (KPIs) to measure the progress that your organization is making toward its goals. The Report Center site provides you with tools that help you to create, store, and manage your reports and dashboard pages.

What do you want to do?


Learn about dashboards and the Report Center

Dashboards are used to communicate status and to drive action. An Office SharePoint Server 2007 dashboard is a Web page template that allows you to assemble and display information from disparate sources such as reports, charts, and KPIs.

You can create your own dashboard page by using various Web Parts. However, the quickest way to create a dashboard is to use the dashboard page template that is provided in the Report Center. You can then add or delete items and change the appearance of the page after you create it.

Although dashboard pages can be created on any site, the Report Center site is optimized for managing and displaying reports and KPIs. It can serve as the central location for a team, department, or organization to store, retreive, and modify its reports.

With the appropriate permission, anyone can set up a Report Center site from any top-level site in an organization. In addition, if you are assigned permission to add a library to a site, you can create a report library where you can easily create, store, and share reports and dashboards. For example, you can create a report library from a team site.

By default, the home page of the Report Center has links to a sample dashboard page, sample data, and sample KPIs. You can review this information before you create your own dashboard. The Report Center is preconfigured with the following features:

  • Major and minor versions are tracked so that you can view changes or revert to previous versions of reports and pages.
  • Draft item security is turned on so that only users with permission to edit reports can see minor versions.
  • Basic KPI configuration is done. It simplifies displaying KPIs on a dashboard page by automatically creating the KPI list, linking the list to the dashboard page, and adding the KPI Web Part to the page.

To access the Report Center, from the top-level site with the default navigation, click the Reports tab. To view all of the dashboards in the Report Center, on the Quick Launch, click Dashboards. Then, on the View menu, select Dashboards.

Dashboards command on View menu

Anyone with permission to view a dashboard page can view it or link to it from another site.

Top of Page Top of Page

Prepare to add items to a dashboard

To use Microsoft Office Excel Web Access, you must be using the version of Excel 2007 that comes with Microsoft Office Professional Plus 2007 or Microsoft Office Enterprise 2007. In addition, the server that is running Office SharePoint Server 2007 must have Excel Services enabled.

To display an Office Excel 2007 workbook on a dashboard, the workbook must be saved in a document library on a SharePoint site and the library that contains the workbook must be defined as a trusted location for Excel Services. To save a workbook in a document library, you need permission to contribute to the site that the library is on.

You can also display a workbook that you, or someone else, have already saved in a library. To do so, you need permission to view the workbook.

To include a KPI on your dashboard, you must have permission either to create a new KPI list or to access an existing one. By default, members of the Site Members SharePoint group can edit pages, but not create KPI lists. To create a KPI list, you must have the Manage Lists permission. By default, members of the Site Owners SharePoint group have this permission.

Permissions and trusted locations are usually set by the site administrator.

When you create the dashboard, you choose how many reports and KPIs that you want on the dashboard and where they appear on the page. After the page is created, you can customize it by adding and removing Web Parts, changing the layout, and connecting and filtering Web Parts.

Top of Page Top of Page

Create a new dashboard page

You can create a dashboard page from any location on a SharePoint site by using any combination of Web Parts. The dashboard page template in the Report Center helps you to create a page by adding the appropriate Web Parts and linking the KPI lists for you. Although you can create dashboard pages in sites other than the Report Center, the following procedure explains how to create a page starting from the Report Center.

  1. In the Report Center, on the Quick Launch, click Dashboards.
  2. On the New menu, click Dashboard Page.

Dashboard Page command

  1. In the File Name box, type a file name for the dashboard. The file name becomes part of the URL of the page and appears in headings and links throughout the site.
  2. In the Page Title box, type the name of the page. The title appears in headings and navigation links throughout the site.
  3. In the Description box, you have the option to type a description of the page. The description appears in a column in the library list.
  4. In the Document Library box, select the library where you want the page to be stored.
  5. In the Folder box, select the folder in the library where you want the page to be stored.
  6. In the Add a link to current navigation bar? section, do one of the following:
    • If you do not want a link for the page to appear on the Quick Launch, select No. You can get to the page by going to the document library where it is stored or by using the URL of the page.
    • To add a link on the Quick Launch, select Yes, and then select an option from the list. The link will appear under that section in the Quick Launch.
  7. In the Layout box, select an option for the number of Microsoft Office Excel Web AccessWeb Parts and Key Performance Indicators that you want and how you want them to be arranged on the page.
  8. In the Key Performance Indicators section, choose one of the following options.
Option Description
Create a KPI list for me automatically Creates and links a KPI list to the KPI Web Part. This is the best option to choose when a KPI list is not already created.
Allow me to select an existing KPI list later Adds the KPI Web Part to the page, but does not associate a KPI list with the Web Part. Use this option when you have an existing KPI list to use in the Web Part.
Do not add a KPI list to this dashboard Creates the dashboard with no KPI list Web Part. Choose this option when you know that you do not want to display a KPI. If you change your mind, you can add one later by editing the page and adding a KPI Web Part.

Top of Page Top of Page

Share an Office Excel 2007 worksheet on a dashboard page

To share an Office Excel 2007 worksheet on a dashboard page, the workbook must be first saved in a SharePoint library and then added to the Microsoft Office Excel Web Access Web Part.

 Note   To use Microsoft Office Excel Web Access, you must be using the version of Excel 2007 that comes with Microsoft Office Professional Plus 2007 or Office Enterprise 2007. In addition, the server that is running Office SharePoint Server 2007 must have Excel Services enabled.

Save a workbook to a SharePoint site

If the workbook that you want to use is not already in a SharePoint library, you need to publish it by using Excel Services. Publishing the workbook by using Excel Services is a better option than uploading it by using the Upload menu on the SharePoint site. When you use Excel Services to publish the workbook, you can define the parts of the workbook (such as individual worksheets, named ranges, or charts) that you want Excel Services to display in Microsoft Office Excel Web Access. By displaying only specific parts of the workbook and by using Office SharePoint Server 2007 permissions to help protect the workbook from unauthorized access, you can keep data in the workbook confidential while allowing authorized users to refresh, recalculate, and interact with the viewable data.

You can also define parameters. A parameter is a single cell that you name before you publish the worksheet. You can expose the cell by using the Microsoft Office Excel Web Access Web Part. Users can then enter values in the cell and recalculate the workbook to analyze the data.

 Note   Not all Microsoft Office Excel features are supported by Excel Services. For information about unsupported features, see the links in the See Also section.

The example used in the following procedure publishes a workbook to the Reports library of the Report Center site. However, as long as you are assigned the Contribute permission level, you can publish to any SharePoint site.

  1. Open the workbook that you want to publish by using Excel Services. If you plan to define parameters, name each cell that you want users to be able to use for input.
  2. Click the Microsoft Office ButtonButton image, point to Publish, and then click Excel Services under Distribute the document to other people.
  3. In the File name box, enter the path to the server and the file name. For example, to save the file Test.xlsx to the Reports Library in the Reports Center site, in the Contoso top-level site, type: http://Contoso/Reports/ReportsLibrary/Test.xlsx.

 Important   If you copy and paste the URL for the destination SharePoint library directly from the browser into the file name box, you must remove the extra characters that prevent you from copying a file to the destination library. In this example, the extra characters in the URL are Forms/current.aspx.

  1. Click the Excel Services Options button, and then choose the options that you want to use to display the workbook, or part of the workbook, in Excel Services.
  2. To verify that the viewable areas of the workbook appear correctly in the browser, select the Open in Excel Services check box.
  3. Click OK, and then click Save.
  4. In the Choose Document Type dialog box, in the Document Type list, select Report.
  5. Click OK.
  6. Refresh the SharePoint site to view the newly uploaded document.

Display the worksheet on the dashboard

  1. On the dashboard page where you want to add the worksheet, click Site Actions Site Actions menu, and then click Edit Page.
  2. In the Microsoft Office Excel Web Access Web Part where you want to add the worksheet, click the Web Part edit menuWeb Part Edit Menu, and then choose Modify Shared Web Part.
  3. In the Microsoft Office Excel Web Access tool pane, under Workbook, click Select a Link Button image, and then use the Select a Link dialog box to locate the workbook.
  4. To display only part of a worksheet, do any of the following:
Option Description
Named Item Click Button image, and in the Text Entry box, type the name of a range of cells that you defined in the worksheet. The Web Part displays only this range.
Rows Type the number of rows of the worksheet or the named range that you want to display. For example, if you type 5, then the first five rows of the worksheet or named range appear.
Columns Type the number of columns of the worksheet or the named range that you want to display. For example, if you type 5, then the first five columns of the worksheet or the named range appear.

Find links to more information about using the Microsoft Office Excel Web Access Web Part in the See Also section.

Top of Page Top of Page

Add a KPI to a dashboard page

A KPI is a visual cue that communicates the amount of progress made toward a goal.

How you add a KPI to a dashboard page depends on whether you want the dashboard template to automatically create the KPI list for you or you want to select an existing KPI list.

Choose one of the following two procedures, depending on the option that you selected in the dashboard template.

Add a new KPI by using an automatically created KPI list

When you choose the option in the dashboard template to automatically create a KPI list, you save several steps that you would have to perform if you were to add the KPI Web Part to a blank Web Part page. By using the dashboard template, the KPI list is automatically created and associated with the page. In addition, the Web Part is added to the page for you.

The next step is to choose the type of KPI that you want and add it to the KPI list. You do this from the KPI Web Part on the dashboard page.

  1. On the KPI Web Part toolbar, click the arrow next to New.
  2. Select one of the following KPI types, depending on where the data for the KPI resides.
Select this option When the source data is
Indicator using data in SharePoint list A SharePoint list that contains items from which you want to create an aggregate value, such as a sum, minimum, or maximum. Before you set up the KPI, make sure that the SharePoint list already is in the view that you want to use. You must first display the appropriate columns in order for the KPI to work.
Indicator using data in Excel workbook An Excel workbook in which the KPI is calculated.
Indicator using data in SQL Server 2005 Analysis Services A SQL Server 2005 Analysis Services cube.
Indicator using manually entered information Information that is not in a system and therefore is entered manually.
  1. On the New Indicator page, do one of the following, depending on the type of KPI that you chose in step 2.
For this KPI Do this
Indicator using data in SharePoint list
  1. In the Name and Description boxes, type a name and optional description for the indicator.
  2. In the Comments box, type text to help people who are viewing the KPI to understand what it represents.
  3. Under SharePoint List and View, in the List URL box, enter the URL of the list or library.

 Notes 

  1. In View, select the view that contains the items that you want to use in the KPI.
  2. Under Value Calculation, select one of the following ways to calculate the goal of the KPI:
    • Number of list items     A count of the total number of items in the list.
    • Percentage of list items where     A calculation that compares the value of a content type within a column or up to five columns in the list.
    • Calculation using all list items in the view     A computation of Total, Average, Maximum, or Minimum of a numerical column in the list.

 Note   The Calculation using all list items in the view option is only available if your list includes a numerical field.

Indicator using data in Excel workbook
  1. In the Name and Description boxes, type a name and optional description for the indicator.
  2. In the Comments box, type text to help people who are viewing the KPI to understand what it represents.
  3. In the Workbook URL box, enter the location of the workbook.

 Notes 

  • If you don't know the URL of the workbook, click Browse Button image to open the Select a Link dialog box, and then navigate to the file.
  • The URL must be relative to the current site.
  1. In Cell Address for Indicator Value, type the workbook number followed by an exclamation point and then the cell address of the location in the worksheet for the KPI. For example, if the indicator is in cell D15 on worksheet 1, type Sheet1!D15. Otherwise, use a named range.
Indicator using data in SQL Server 2005 Analysis Services
  1. In the Data Connection box, enter the URL where the .odc file is located.

 Note   If you don't know the URL for the .odc file, click Browse to open the Select a Link dialog box, and then navigate to the .odc file.

  1. In the Only display KPIs from display folder box, select the display folder in the Analysis Services database that contains the KPI.
  2. In the KPI List box, select the KPI that you want, such as Total Revenue or Average Profit Margin.
  3. Select the Include child indicators check box to display all of the child indicators for the selected KPI.
  4. In the Name and Description boxes, type a name and optional description for the indicator.
  5. In the Comments box, type text to help people who are viewing the KPI to understand what it represents.
Indicator using manually entered information
  1. In the Name and Description boxes, type a name and optional description for the indicator.
  2. In the Comments box, type text to help people who are viewing the KPI to understand what it represents.
  3. Type the numerical value of your progress so far.
  1. In the Status Icon Rules section, in the Better values are list, select higher or lower to indicate which range of numbers will be green.
  2. Type the values for the status indicators in the boxes. For example, to track the minimum percentage complete for a set of tasks, you can set the green indicator at the goal value and the warning value to be one less than the goal value. In that case, if you want to see when the minimum percentage complete drops below 25 percent, you set the green indicator to 25 and the yellow indicator to 24.

 Note   The Status Icon Rules for an Indicator using data in Analysis Services KPI are preset by the database analyst.

Add an existing KPI to the dashboard page

When you create a dashboard by using the template, you can select an existing KPI later. This option places the KPI Web Part on the dashboard page and then allows you to use the Web Part tool pane to select the KPI list that you want to display. You must have permission to access the KPI list in order to use it in the Web Part.

  1. To add the KPI list to the Web Part, in the Web Part, click Open the tool pane.
  2. In the Indicator List box, click Browse Button image to locate the KPI list that you want to display.
  3. In the Select a Link dialog box, double-click the title of the KPI list that you want to use.
  4. Click OK to close the tool pane.

Find links to more information about working with KPIs in the See Also section.

Top of Page Top of Page

Publish the dashboard page

Like most Web pages in Office SharePoint Server 2007, dashboard pages use the check-in and check-out system to ensure that only one person edits the page at a time and a versioning feature that helps you to keep track of the changes made to the page. To make a dashboard viewable to others, you must publish it. In some cases, the page must be approved by another person in your organization before it can be published.

The following procedure assumes that you are assigned permission to publish the dashboard page and that the page does not need to be approved by reviewers. Find links to more information about publishing pages in the See Also section.

Do one of the following:

To Do this
Save the page, but do not allow others to edit it. This allows you to come back to the page and edit it later without sharing your draft with others. On the Page Editing toolbar, click Page, and then select Save and Stop Editing.
Save the page and allow others to edit it. This option is useful when you have team members with whom you collaborate and you want them to view or modify a draft of the page. On the Page Editing toolbar, click Check in to Share Draft.
Publish the page to allow anyone with permissions to view it. On the Page Editing toolbar, click Publish.

Top of Page Top of Page

 
 
Applies to:
SharePoint Server 2007