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.
Anyone with permission to view a dashboard page can view it or link to it from another site.
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.
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.
-
In the Report Center, on the Quick Launch, click Dashboards.
-
On the New menu, click Dashboard Page.
-
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.
-
In the Page Title box, type the name of the page. The title appears in headings and navigation links throughout the site.
-
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.
-
In the Document Library box, select the library where you want the page to be stored.
-
In the Folder box, select the folder in the library where you want the page to be stored.
-
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.
-
-
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.
-
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. |
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.
-
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.
-
Click the Microsoft Office Button
, point to Publish, and then click Excel Services under Distribute the document to other people. -
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.
-
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.
-
To verify that the viewable areas of the workbook appear correctly in the browser, select the Open in Excel Services check box.
-
Click OK, and then click Save.
-
In the Choose Document Type dialog box, in the Document Type list, select Report.
-
Click OK.
-
Refresh the SharePoint site to view the newly uploaded document.
Display the worksheet on the dashboard
-
On the dashboard page where you want to add the worksheet, click Site Actions
, and then click Edit Page. -
In the Microsoft Office Excel Web Access Web Part where you want to add the worksheet, click the Web Part edit menu
, and then choose Modify Shared Web Part. -
In the Microsoft Office Excel Web Access tool pane, under Workbook, click Select a Link
, and then use the Select a Link dialog box to locate the workbook. -
To display only part of a worksheet, do any of the following:
| Option | Description |
| Named Item | Click |
| 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.
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.
-
On the KPI Web Part toolbar, click the arrow next to New.
-
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. |
-
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 |
|
| Indicator using data in Excel workbook |
|
| Indicator using data in SQL Server 2005 Analysis Services |
|
| Indicator using manually entered information |
|
-
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.
-
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.
-
To add the KPI list to the Web Part, in the Web Part, click Open the tool pane.
-
In the Indicator List box, click Browse
to locate the KPI list that you want to display. -
In the Select a Link dialog box, double-click the title of the KPI list that you want to use.
-
Click OK to close the tool pane.
Find links to more information about working with KPIs in the See Also section.
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. |
No comments:
Post a Comment