Understanding the Report Center and Dashboards in SharePoint Server 2007

Summary: Examine new capabilities built into Microsoft Office SharePoint Server 2007 Enterprise Edition, which enable and simplify business process automation. (13 printed pages)

Microsoft Corporation

May 2008

Applies to: Microsoft Office SharePoint Server 2007 Enterprise Edition

Contents

• Overview of the Report Center and Dashboards in SharePoint Server 2007

• Using the Report Center and Dashboards

• What Is a Dashboard?

• Understanding Web Parts–The Dashboard Building Blocks

• Including Key Performance Indicators and KPI Lists

• Understanding Excel Services and the Excel Web Access Web Part

• Integrating Office SharePoint Server 2007 and SQL Server 2005 Reporting Services

• Personalizing by Using Filter Web Parts

• Microsoft Office PerformancePoint Server 2007

• Conclusion

• Additional Resources

Overview of the Report Center and Dashboards in SharePoint Server 2007

Microsoft Office SharePoint Server 2007 helps organizations provide business intelligence (BI) capabilities to every employee. It enables sharing, control, and reuse of important business information that is used frequently in the organization.

The integrated BI features of Office SharePoint Server 2007 enable the widespread delivery of information and data through the Microsoft Office system. This shared distribution improves communication, and facilitates the sharing and repurposing of existing documents, reports, and worksheets.

For information workers, BI also provides access to information from a variety of sources in the familiar software applications that they are already using daily. The BI features in Office SharePoint Server 2007 provide both Web access and programmatic access to collaborative content and materials in a SharePoint repository.

Addressing Role-Based Individual Needs

One of the challenges facing today's information technology administrators is how to address the ever-growing amount of information available. To be effective, information workers must be able to easily identify and access the data that is relevant to their roles in the organization. That critical information must be easy to access, and it must be available in a consumable format that lets workers focus on their tasks.

The fundamental concept of a role-based solution is built around the realization that each worker has unique needs. The sales representative who is managing individual accounts must see data, records, and documents that relate directly to the daily realities of tracking and contacting that customer base. The financial officer who is preparing a report that outlines projected growth for the corporation needs a unique data view that provides a different subset of the collective data store. The person who is managing inventory levels in the centralized warehouse or shipping facility requires access to a specific set of data to perform daily tasks.

Any solution that is intended to address the range of information needs in the organization must also be easy to manage, and flexible enough to be effectively implemented by the IT department.

Accessing the Right Information, at the Right Time, in the Right Format

Relevant business information must be available to decision makers at all levels of the organization. By using the new Office SharePoint Server 2007 Report Center site, you can transform this ever-growing collection of information into easy-to-use and insightful tools for making informed and effective decisions at all levels of the company.

Adventure Works—a Fictional Scenario

The Adventure Works sample databases are samples based on a fictitious multinational company named Adventure Works Cycles. Adventure Works is used in this article to show how to use the Report Center site to solve information distribution problems and provide role-based information access.

Fictional employees of Adventure Works Cycles are featured in this article, showing how the unique information needs of each employee can be solved with the customized dashboards that are available as part of the BI features of Office SharePoint Server 2007.

Our Approach

Office SharePoint Server 2007 helps organizations provide BI capabilities to every employee, so that employees can share, control, and reuse business information. The two most important components to the solution are the Report Center site and the dashboard capabilities.

The Report Center site includes Office SharePoint Server templates that are optimized for report access, administration, and performance tracking. These site templates provide consistent administration for reports, worksheets, key performance indicators (KPIs), and data connections.

Figure 1. Adventure Works profile

Dashboards feature dynamic and customizable views of business information that directly relate to an individual worker's job-related tasks and needs.

The Solution: Office SharePoint Server 2007

Microsoft Office SharePoint Server 2007 provides a comprehensive array of collaboration and content-sharing features. Its BI aspects help address the needs of individual workers and the growing need for content and data filtering.

Figure 2. Office SharePoint Server 2007 feature areas

Using the Report Center and Dashboards

The Report Center site includes a BI portal, and it highlights the dashboard capabilities of SharePoint Server 2007. Predefined site templates, optimized for report access and administration, are included. They provide consistent management of reports, worksheets, KPIs, and data connections.

The Report Center serves as the central hub for information delivery by using the Business Intelligence Web Parts, the basic building block components used to design dashboards. A dashboard provides a Web-based display that integrates information from a variety of sources, including data from other applications, Internet and intranet content, and personal files.

Figure 3. Reports Library

To create a dashboard, use the dashboard page template provided in the Report Center. After creating a dashboard, you can add or delete items and change the appearance of the page.

Although you can create dashboard pages on any site, the Report Center site is optimized for managing and displaying reports and KPIs. The Report Center serves as the central location for a team, department, or organization to store, retrieve, and modify reports.

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

• By default, draft item security is enabled so that only users who have permission to edit reports can see minor versions.

• Basic KPI configuration is automated, streamlining the process of 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.

Figure 4. Sales dashboard

When creating a dashboard, you start with an existing template and choose how many reports and KPIs to include and where they appear on the page. After the dashboard is created, you can customize it by adding and removing Web Parts, changing the layout, and connecting and filtering Web Parts.

Figure 5 shows a Report Center dashboard with a library of reports, a chart, and KPIs already set up.

Figure 5. Regional sales dashboard

In the Report Center, you have an individual Report Library that gives you instant access to the reports and data views that you created, customized, or use frequently.

What Is a Dashboard?

A dashboard is a personalized solution for information workers that consolidates personal, team, corporate, and external information, and provides single-click access to analytical and collaborative tools. It brings an integrated view of a company's data resources to an individual's desktop, providing immediate access to key business information.

A dashboard is the key delivery vehicle for the BI functionality of Office SharePoint Server 2007. It is an easy-to-use Web Part page that enables information workers to make use of one another's knowledge and to more easily access and share information, worksheets, documents, and data.

A dashboard can draw information from multiple data sources and formats, combining complex information into one simple interface. A critical component of delivering the right information in the right format is the consolidation of personal, team, corporate, and external information into a single-click viewing portal.

A dashboard also provides the ability for information workers to share and access important information that they need to make intelligent business decisions, regardless of where the workers are located.

Figure 6. John Smith inventory control profile

With so much information coming from so many sources, information workers can spend valuable time sorting through various materials to find one important point or relevant piece of data. A dashboard helps solve information overload by delivering focused business data by using filters, user-specific categories, and summaries. Users can access high-level information in relevant business reports directly from their dashboards.

Figure 7. Chris Sells sales representative profile

Unlike some browser-only portal solutions that deliver information only from the Web or an intranet, a dashboard integrates information from a variety of sources. You can organize and view key business data from corporate applications, Internet and intranet sites, and personal files in a dashboard.

Dashboards also help workers to make efficient and effective business decisions regardless of their location. Information workers are often away from their desks—and offices—but they still must access their company's networks and the Web and communicate with their teams and their customers. The Web-based dashboard provides anywhere access to needed information.

Understanding Web Parts–The Dashboard Building Blocks

Office SharePoint Server 2007 includes a set of building blocks called Web Parts that enable you to quickly develop and maintain dashboards that include dynamic, interactive data capabilities, such as drill-down, filter, and sort. Dashboards that are built on Office SharePoint Server take advantage of the user's familiarity with Microsoft Office Excel, making use of PivotTables, Excel charts, and formulas. Dashboards also conform to the Office SharePoint Server environment, enabling users to automatically correspond to a corporate look and feel. Dashboards can also use SharePoint Web Parts external to Excel, such as KPIs, or any SharePoint Web Part or custom Web Part.

The secret to the personalization of dashboards is the flexibility and simplicity of the design. The key building block of the dashboard is the ASP.NET 2.0 Web Part. A Web Part can be a simple component that displays a user's favorite Web site, or it can be a sophisticated component that integrates existing systems with the analytical and collaborative tools in products, such as Microsoft Office, Microsoft Exchange Server, and Microsoft SQL Server.

By creating customized Web Parts, IT staff can customize dashboards to meet the specific needs of individuals in the organization. Although users can customize their own dashboards with the easy-to-use interface by selecting from lists, KPIs, reports, worksheets, and other resources to build their own individual data view, Office SharePoint Server also includes a wide variety of ready-to-use Web Parts. Users can assemble these predefined Web Parts to quickly build new pages.

A SharePoint Web Part creates HTML renderings from the results returned by the server, enabling users to view and interact with the resulting dashboard in a Web browser. Customizable browser-based access that can be personalized to meet the needs of the individual user is an important component to the anytime, anywhere delivery model of the dashboard.

Web Parts provide a simple way to use query filters to deliver specific views of data, enabling the user to develop personalized role-based dashboards that meet individual, specific decision-making needs.

Including Key Performance Indicators and KPI Lists

A Key Performance Indicator (KPI) is a quantifiable measurement for gauging business success that is frequently evaluated over time. KPIs compare an actual value to a target value. For example, the sales department of an organization might use monthly gross profit as a KPI, but the human resources department of the same organization might use quarterly employee turnover. Each is an example of a KPI. Business executives frequently consume KPIs that are grouped together into a business scorecard to obtain a quick and accurate historical summary of business success.

The KPI is a fundamental example of business intelligence, where easily providing the right information displayed in the right format increases the user's productivity.

A KPI list is a key component of the unique personalization features of the dashboard, and it adapts to meet the needs of the individual information worker. A KPI Web Part is a navigation component that can be added to a dashboard, enabling an individual to build an easy-to-use selection system for the KPIs needed most often. When defining a personal KPI list, the user can draw from KPIs stored in Microsoft SQL Server Analysis Services, Excel, a SharePoint list or Business Data Catalog data sorted in a SharePoint list, or data that the user entered manually.

Figure 8. Sven Mortensen CFO profile

The KPI Web Part provides a set of visual indicators to help the user be more aware of current performance status. The user can also build multiple lists, organizing KPIs based on personal criteria and requirements specific to a role-based view of the organization.

As with most reports, the "one size does not fit all" axiom applies. Managers in different divisions in the same organization make their decisions based on their responsibilities and the unique needs of their roles. KPIs can be customized and personalized, providing each decision maker with the right data view.

Figure 9. KPI list

With Office SharePoint Server 2007, KPIs are easy to create, they can be shared throughout the organization with others who need the same collection of reference material, and they draw from various formats, applications, and data sources.

Understanding Excel Services and the Excel Web Access Web Part

Office Excel 2007 and Excel Services enable data to be accessed, manipulated, and displayed in compelling ways. When data is ready for publication, information workers can combine their data with other data by using dashboards.

Excel Services enables users to rapidly create server-side solutions that complement the use of client-side worksheets. Excel Services also makes Excel data available to use in interactive BI dashboards and portals.

Excel Web Access is an Excel Services Web Part in Office SharePoint Server 2007 that renders (in other words, creates the HTML for) live Excel workbooks on a Web page, and enables the user to interact with those workbooks and explore them. Excel Web Access is the visible Excel Services component for the user. You can use Excel Web Access like any other Web Part in Office SharePoint Server 2007.

Excel Web Access does not require anything to be installed on the user's client computer. The Excel Web Access Web Part provides browser-based access to worksheets that are centrally stored and managed. Excel Services is a server-side set of technologies that is fully integrated with the Excel 2007 client, extending the client to be a natively multiuser environment. By using Excel Services, organizations can efficiently share worksheets widely and securely, enabling workers to use worksheet analysis as part of BI solutions in dashboards, portals, reports, and custom Web pages. This enables developers to easily access business logic directly from published worksheets for reuse by, and integration with, business applications. This solution is a server-side companion to the Excel rich client, providing high-performance calculations and highly reliable and available implementations. This solution enables businesses to address current and emerging needs for data modeling, analysis, reporting, and sharing.

Figure 10. Business score card

Integrating Office SharePoint Server 2007 and SQL Server 2005 Reporting Services

Microsoft SQL Server 2005 Reporting Services Service Pack 2 (SP2) can be integrated with Office SharePoint Server 2007 to enable publishing, viewing, and managing reports on all data in the SQL Server databases in the organization. By using Web Parts and customized dashboards, IT professionals can use the role-based model to provide easy access to that data for everyone in the organization.

There is an important difference in this integrated path to viewing SQL Server data by using Office SharePoint Server 2007 instead of using SQL Server Reporting Services. When displaying SQL Server data by using Report Center, a user can create a dashboard that pulls information from databases and can also use any other shared content in the Office SharePoint Server world of collaborative content.

For more information about the features that are available on a report server that is configured for integration with Windows SharePoint Services 3.0 or Office SharePoint Server 2007, see Features Supported by Reporting Services in SharePoint Integration Mode.

Personalizing by Using Filter Web Parts

Filters enable dashboards to be personalized by using shared parameters among Web Parts on a dashboard. The parameters passed can be automatically applied based on user profiles, SharePoint lists, the Business Data Catalog (BDC), or manually-entered information. This versatile filtering capability is the key to the power of personalization. It gives individual information workers unique data views.

Figure 11. Add Web Parts to Filter Zone

An API is also provided that enables developers to create their own custom-filtered Web Parts.

Microsoft Office PerformancePoint Server 2007

For enterprises that need additional in-depth analysis tools, Microsoft Office PerformancePoint Server 2007 can provide complementary BI resources.

Office PerformancePoint Server 2007 is the Microsoft solution for corporate performance analysis. It enables corporations to build, use, and manage their performance scorecards. You can widely distribute Office PerformancePoint Server 2007 scorecards through a diversity of clients, and they enable employees to remain connected, proactive, and aligned to the corporate strategy.

Both Office SharePoint Server 2007 and Office PerformancePoint Server 2007 offer the concept of a KPI, but it is important to understand how scorecards and dashboards provide vital, but different, views of information in an organization. A scorecard is used to align corporate strategy and transform it into employee actions; a dashboard is a platform for integrating information, reporting, analysis, and other personalized needs. Dashboards provide a visualization of departmental or individual initiatives, whereas scorecards are an expression of corporate strategy.

Business scorecards help companies make sense of their business data by organizing it in the context of KPIs. Office PerformancePoint Server 2007 provides advanced scorecarding functionality with reports, charts, graphs, and analysis tools, and with unstructured data, such as documents, worksheets, and Web sites. You can manage KPIs from Microsoft SQL Server 2005 Analysis Services. Maintaining KPIs from SQL Server allows for the scenarios where common KPIs are shared across Office SharePoint Server 2007 and Office PerformancePoint Server 2007.

Business scorecards give companies and information workers a broad view of business opportunities, through which employees can better understand business challenges, effectively shape solutions, and quickly execute their objectives.

Conclusion

Office SharePoint Server 2007 provides a powerful and versatile way to deliver business intelligence information to all information workers in an organization. Providing the right information, in the right format, and at the right time enables users to make better-informed decisions by presenting business-critical information in one central location.

You can assemble live, interactive BI portals to display vital role-based information from disparate sources. By combining Web Parts, scorecards, KPIs, and other collaborative information, dashboards deliver the ideal combination of data, formatted and displayed based on the unique requirements of the individual.

As organizations grow more dependent upon digital information, collaboration, and integration of multiple data sources, today's information worker faces an escalating challenge to manage this influx of facts, figures, and documents. In a digital work environment, Office SharePoint Server 2007 provides the tools to effectively organize, manage, share, and deliver that content to users.

Additional Resources

For more information, see the following resources.

• SharePoint Server 2007 Developer Portal

• TechNet Webcast Express: Exploring the Report Center in SharePoint Server 2007 (Level 100)

• Excel Services Overview

• SharePoint Products and Technologies Community