Using Excel Services in Dashboards

The concept of aggregating decision-support data into a single place where the information can be seen side-by-side and reviewed at a glance has been emerging as a significant objective for information portals. The term dashboard has come to identify the place where a set of data views, possibly from different data sources, are brought together to facilitate business monitoring, analysis, and decision making.

The Excel Web Access Web Part

The Excel Web Access Web Part allows you to display data and charts from an Excel workbook and embed them into a SharePoint Server 2007 site or dashboard page. The Web Access Web Part presents the same display style as opening the workbook in a Web browser, but it offers additional features to allow better targeting of data. Within each Web Part, you can specify that only a single object in the workbook, such as a range of cells or a chart, be displayed, as shown in Figure 20-6.

Figure 20-6: Web Access Web Part

To add the Web Part to the page, follow these steps:

1. Open the page in Edit mode.

2. Click Add A Web Part in the zone where you want to place it.

3. Scroll down the list to the Excel Web Access Web Part, select the check box next to it, and then click Add.

4. On the Site page, click the Click Here To Open The Tool Pane link.

5. In the Workbook To Display box, type the path to the workbook in its document library.

6. In the Named Range or Object To Display text box, type the name of the range of cells or chart objects to display in the Web Part. (If you leave this entry blank, the entire workbook is displayed.)

7. Click OK and the Web Part renders the Excel object.

Key Performance Indicators Web Parts

Key Performance Indicators (KPI) are scorecard values that provide visual information to enable users to communicate progress towards goals or standards. Although KPIs are not specifically an Excel Services feature, the Key Performance Indicator List Web Part is an important element for a business intelligence dashboard because it allows users to quickly get a sense of the status of key decision-making factors. KPI lists can retrieve data from Excel sheets or databases and can be configured to present the information as icons, check marks, traffic lights, or other graphical indicators.

To create a KPI display that is based on the data in an Excel workbook, you start by defining Indicators in a KPI List. To do this, follow these steps:

1. From the New menu in the KPI List, select Indicator Using Data in Excel Workbook.

2. Give the indicator a name, enter the workbook address, and enter the address of the cell that contains the indicator value.

3. Finally, identify the threshold values at which the status icon changes to a different color.

Once the list is populated with indicators, add a Key Performance Indicators Web Part to the site or dashboard page and associate it with the KPI List. The indicators then display it on the page and automatically update every time the page is refreshed, as shown in Figure 20-7.

Figure 20-7: Key Performance Indicators Web Part

Filter Web Parts

Most data sets provide more information than you need to answer a given analytical question. The Filter Web Parts are used to target the information on a dashboard to allow viewing of more specific data and to focus on specific patterns. The Filter Web Parts can be added to a dashboard to enable users to filter all the content in other Web Parts quickly and easily without any code. Filters can be configured to allow users to select from a list of values, to filter on a value from an external database, or to filter on a value entered manually by the user.