Excerpted from SharePoint 2007 and Office Development Expert Solutions
SharePoint Application Services for Reporting and Dashboards
by Randy Holloway
Microsoft's SharePoint technology architecture delivers a foundation of APIs and functionality that makes it easier for developers to build and deploy applications for a variety of scenarios. Workflow-enabled forms solutions, business intelligence and reporting aggregation tools, Excel-based publishing tools for Web reports, and records management tools are a few of the many functions that SharePoint supports out of the box with hooks for customization and extensibility built in. These products have been packaged into Microsoft Office SharePoint Server and are primarily delivered through SharePoint's Enterprise edition product.
One key set of functionality in Microsoft Office SharePoint Server 2007 is the support for delivery of reports, particularly Excel-based reports, through the Web browser. The Report Center in SharePoint 2007 is designed to provide a solution to integrate Excel-based reports, dashboards, Key Performance Indicators (KPI) lists based on SharePoint lists, and SQL Server Analysis Services and manually entered data to develop reporting and dashboard sites that can be deployed within SharePoint and managed like other SharePoint sites.
While the SharePoint platform has delivered integration with reporting tools in the past through Web Parts for Reporting Services and a variety of Web Parts published by other business intelligence tools vendors in previous versions, SharePoint Server 2007 builds the concept of business intelligence (BI) into the core of the product with a site template for the Reports Center, the introduction of Excel Services, and integrated support for KPI dashboards. To understand how the BI features in the Report Center work, the following presents an example of publishing reports and building a dashboard with Excel-based data with an integrated KPI dashboard.
Publishing a Report
The Report Center is a site template in SharePoint that serves as an aggregation point for various reports, dashboards, and Web Parts to construct reporting solutions. To get started with the Report Center, this example starts with an Excel-based report that you can publish to the reports library. The following steps deploy the report:
- First, create a sample Excel workbook for your report. An example of a simple worksheet with conditional formatting (heat map) is shown in Figure 1.
To understand how the publishing model in Report Center works, any basic spreadsheet should provide a good starting point for demonstrating the functionality.
- The Report Center site template includes a library for reports that will automatically render Excel workbooks through Excel Services. To upload the workbook to the Reports Library, click the Upload option to upload a single report document or multiple report documents in the Reports Library. This will launch a page that will prompt you for the location of the report file and for Version Comments. Click OK to initiate the upload.
- Once the report document has been uploaded to the report library, you'll be prompted to provide additional metadata about the report. Fill out the form for report data as shown in Figure 2, and click OK.
Note that you can select a content type that will determine what metadata will be collected for that report. Remember that in addition to the default content type, such as Report, you can create custom content types and deploy them to your report libraries to collect additional data that's unique to your organization or reporting site requirements.
- To extend a content type to include options that are relevant to your organization or site requirements, such as defaults for the Report Category element of the Report content type, click on the Document Library Settings option from the main page of the Report Library. From the resulting Customize Reports Library page, follow the Content Type link for the Report Content Type by clicking on its link.
- On the List Content Type: Report page, follow the link for Report Category and click the "Edit column in new window" link, as shown in Figure 3.
- Add or customize choices for report category, as shown in Figure 4. These categories can be used to provide descriptive metadata for search, list filters in the report library, and workflows that are associated with reports. When creating your own categories, be sure to provide clear and descriptive categories that are likely to be reused.
After these steps are completed, a new report is published and can be assigned to the appropriate business category, department, or other organizational element used to categorize reports. This level of categorization and the resulting ability to effectively search for, filter on, and assign workflow actions to reports based on these categories deliver a substantial portion of the benefit to developers building their reporting applications on SharePoint Server 2007.
Now that you have a basic report published you can take a look at the functionality for dashboards and further customization of reports.
Creating Dashboards for Reports
Microsoft Office SharePoint Server 2007 delivers dashboard support for BI applications within your portal and collaboration web sites. These dashboards are supported in the Report Center and are designed to help you create reporting dashboards featuring links to reports, text-based content related to the reporting data, lists of Key Performance Indicators (KPIs), and data from Excel-based reports using the Excel Services Web Parts. This enables you as a developer to create reporting applications targeted at a department or specific organizational function with all of the key reporting elements required for business managers and decision makers to use when running the business.
From the Reports Center site, you can select the Dashboards link to see a library of dashboard sites. The Dashboards link takes you to a specific view of the Reports Library that you worked with earlier when publishing reports. There are several default filters applied to this library to allow you to view only the dashboard pages, reports, or other combinations of the data within the library when building your applications.
To create a new dashboard site containing Excel-based reports and a KPI list follow these steps:
- Navigate to the dashboard view of the Reports Library within the Report Center site template. Click the New menu item and select New Dashboard Page. This launches a new page called New Dashboard where you can begin configuring the dashboard application.
- On the New Dashboard page, select a layout for the dashboard page. The default options include a multi-column horizontal layout or two different vertical column layouts. For this example, select the two-column vertical layout. There is also an option on this page to create a KPI list automatically. Select this option to create KPI list on the new page. When naming your dashboard application, think about how your reporting users will navigate to the application and whether or not they'll need to browse the dashboard library to find their reporting content. Use descriptive terms in the dashboard page name to help the users identify with the content of the dashboard reporting data. To create the new page after configuring the dashboard options, click OK.
- Now you should see the new dashboard page in its configuration mode. By default, you'll see a KPI Web Part and two Excel Services Web Parts where can you configure connections to Excel workbooks or data connections. To assign an existing Excel workbook in the reports library to both Web Parts, you can use the workbook published earlier in the first example on report publishing to get started. Select one of the Excel Services Web Parts on the dashboard page and click the "Click here to open the tool pane" link. This will launch the configuration menu on the right side of the screen for configuring the Excel Web Part. This configuration panel is shown in Figure 5.
There are several configuration options for connecting a workbook to the Web Part in Excel Services. To get started with this dashboard, you'll need to select the location of the workbook using the Workbook field and the number of rows and columns to display. Advanced options such as which toolbar menu commands to expose, what level of interactivity is supported — such PivotTable support, for example — filtering, and sorting can be configured for each workbook connection. In addition, the Audiences feature in SharePoint can be used to target this Excel reporting content to specific audiences based on group membership or organizational role. This enables you to create more dynamic dashboard applications that can be targeted to a large range of users with data delivery targeted towards specific organizational membership or role functions.
Another component of the new dashboard page that has been created is the KPI Web Part. The KPI Web Part is used to present manually entered data, list data, or data from other sources such as Analysis Services in a banded fashion based on rules and configuration settings. These settings can be used to identify which values are "better," which icons should be used as indicators, and what calculations might be displayed based on underlying KPI data. To configure the KPI Web Part on the dashboard page, go through the following steps:
- Select a new KPI indicator based on an existing SharePoint list. For this the Parts List we're using is from Chapter 3, " Programming SharePoint Lists and Libraries," of the book, SharePoint 2007 and Office Development Expert Solutions (Wrox, 2007, ISBN: 978-0-470-09740-3). That list contains a Part Name field, a Part Number field, and a Price field so you can recreate a similar list for this example. To get started with creating a KPI based on the list, click the New button on the Key Performance Indicators Web Part, as shown in Figure 6. Select the "Indicator using data in SharePoint list" option. This will take you to the Dashboard KPI Definitions: New Item page.
- On the page for defining the KPI items, you'll need to provide a name, description, and comments about your indicator. Note that there are multiple sections of this page, and each subsequent configuration step for the KPI will occur on this same configuration page. With a list containing parts with prices, we can establish a KPI to determine how many of the parts are being sold for $3 or more. If $3 is the minimum price a business would need to sell at in order to stay profitable, it is important to make sure that you can provide an indicator tied to the price list to a manager responsible for profitability.
- Still on the page for defining the KPI items, there is a section called Indicator Value. In this section, you need to enter the List URL for the SharePoint list.
- Since we want to set our KPI to monitor profitability of parts, we need to configure the indicator value to be based on the price of the items in the parts list. There are a number of ways to do this. We can use an average calculation or track this by the percentage of the parts that are on the list that are priced at $3 or better. For this example, we'll use a percentage calculation of the items in the list for the indicator. To configure this, select the "Percentage of items in the list where" option under Value Indicator. Under that section, there are two drop-down boxes to select the column of the list and the operator to use and one text box for a value. Select the Price column of the list, select the "is less than" operator from the drop-down list, and enter 3 in the value text box. This defines a value indicator based on the percentage of items that are less than $3 in the SharePoint list for tracking parts.
- Finally, you need to configure the status icon for the KPI. This is where the "banding" occurs. Under the Status Icon section of the page, we'll need to configure the icon associated with this KPI. Since we're tracking profitability and want to look for the percentage of items priced at less than $3, you'll need to use the drop-down list to set the rule that better values are lower. In our case, the lower the percentage of items sold for less than $3 the higher the profitability margin should be.
- Green, yellow, and red indicators are defined for each band. You need to define the threshold percentage for which the green, or good, indicator should be shown and the value for which the yellow icon should be shown. Anything greater than that percentage, and you'll see the red icon. In this example, we use 10 and 50, meaning that less than 10 percent of the parts priced at less than $3 is our goal, and less than 50 percent of the parts priced at less than $3 causes us to get a yellow warning. Anything more than 50 percent of the parts being priced at less than $3 will cause the red indicator to show for this KPI.
- At the end of the configuration page for this KPI there is a Details link where you can provide a link to a more detailed document explaining how this KPI is calculated within your web site. There is also an Update Rules setting that enables you to recalculate the KPI for each viewer, which is important if your KPI is based on a dynamic list, or to manually initiate the recalculation periodically. To save this KPI, click the OK button at the bottom of the page.
- Once configuration of the dashboard page is complete, select the highlighted Publish option, as shown in the Figure 7. Since the dashboard publishing page is integrated into the overall Web Content Management publishing model in SharePoint it requires that the page be put into an editor mode and subject to workflow and approval for all content updates if required.
- Once the completed dashboard is published, you'll note that the KPI list shows a percentage of 66.67 (or 2/3) of the products in the parts list are priced below $3. As a result, the red indicator is showing on this KPI. This result will be different for you based on the number of items in your list and the prices that you used. To get a sense for how the KPI calculation and banding works, you can play around with adding items to your list and adjusting the percentage thresholds as defined for the Status Icon. To go back and edit the properties for the KPI or change its configuration, you can click on the arrow next to the KPI name and select Edit Properties, as shown in Figure 8.
The completed dashboard application includes a KPI list and Excel-based reports based on Excel workbooks that were published to the Reports Library. Of course, report documents posted to other document libraries or Excel data connections to data sources outside of SharePoint are also supported as part of SharePoint's BI features. For more detail on KPIs, Excel Services, and configuring BI applications in SharePoint, take a look at Chapter 7, " Building Business Intelligence Applications with SharePoint and Office," of the book, SharePoint 2007 and Office Development Expert Solutions (Wrox, 2007, ISBN: 978-0-470-09740-3), which dives deeper into the BI capabilities of Excel 2007 and SharePoint.
Note that additional Web Parts that are connected to other data sources could be incorporated to capture business data exposed through the Business Data Catalog or other list data needed. For example, if your SAP portal application includes IViews, a type of Web Part supported by SAP that exposes key business data, you can use the IViews Web Part in SharePoint 2007 to connect the SAP functionality into a dashboard that's surrounded by other SharePoint BI features. In addition, the KPI lists can be based on existing data from Analysis Services or can be defined and updated manually as required.
This article is excerpted from Chapter 6, " SharePoint Application Services," of SharePoint 2007 and Office Development Expert Solutions (Wrox, 2007, ISBN: 978-0-470-09740-3). Randy Holloway works as a technical advisor for Microsoft's enterprise software customers in St. Louis. Before joining Microsoft, Randy served as an IT director focused on software engineering and ERP systems support in addition to his writing and speaking engagements for a variety of industry publications and conferences. Randy holds a B.S. from Mississippi State University. He lives in St. Louis, Missouri, with his wife and two children. He also writes a weblog focused on technology and industry trends, located at http://randyh.wordpress.com.