SQL Server 2005 Integration with Microsoft SharePoint Products and Technologies

 

Anthony T. Mann
President, Mann Publishing Group

Technical Reviewers:

Brian Welcker
Microsoft Corp.

Prash Shirolkar
Microsoft Corp.

Published: December 2007

Applies To: SQL Server 2005 Reporting Services with SP2 and Microsoft SharePoint Products and Technologies

 

Summary:  Microsoft SQL Server 2005 SP2 introduced tight integration between SQL Server Reporting Services and Microsoft SharePoint Products and Technologies to provide a single-user environment with which to manage and execute reports. This paper shows how to configure the integration of Reporting Services and SharePoint Products and Technologies. It also shows how to manage reports and publish them to a SharePoint site.

You can also download a Microsoft Word version of this article.

Table of Contents

Introduction. 1

Microsoft Business Intelligence. 1

Terminology. 1

Integration Architecture. 2

Report Server 3

SharePoint Server 3

Server Configuration. 4

Report Server Configuration. 4

SharePoint Configuration. 4

Authentication Modes 5

Forms Authentication. 6

Firewalls 6

SharePoint Server Farm.. 7

Report Publishing. 8

Using SQL Server Business Intelligence Development Studio. 8

Using Report Builder 9

Report Management 10

Properties 10

Permissions 10

Subscriptions and Delivery. 12

Data Sources 13

Parameters 13

Processing Options 13

History. 15

Workflow/Approval 15

Dashboards and Web Parts. 15

Report Viewer Web Part 15

Filter Web Parts 17

Conclusion. 18

About the Author 18

Introduction

Microsoft® SQL Server™ 2005 and the 2007 Microsoft Office System are key parts of the Microsoft Business Intelligence (BI) offering. These technologies work together to put tools in the hands of business users to enable them to make important decisions about their businesses. To understand the key BI features built into the 2007 Microsoft Office System, see the 2007 Microsoft Office System Business Intelligence Integration white paper.

One of the key BI integration points between SQL Server 2005 and the 2007 Microsoft Office system is the tight integration between SQL Server 2005 Reporting Services and Microsoft Office SharePoint® Server 2007. This white paper outlines the integration of SQL Server 2005 Reporting Services with SharePoint Products and Technologies (Microsoft Office SharePoint Server 2007 and Microsoft Windows® SharePoint® Services 3.0).

Microsoft Business Intelligence

The Microsoft vision for Business Intelligence is to enable all employees in an organization to make better, faster, and more relevant business decisions. This requires a robust set of technologies that work together.

On the back end, the core BI platform is based on Microsoft SQL Server 2005 and its related technologies, such as SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS). On the front end, the 2007 Microsoft Office System, together with Microsoft Office Business Applications provides a familiar suite of tools and applications with which to access the back-end BI solution. By surfacing reporting and analytics through the tools that users are already familiar with, users can be productive immediately. They do not need go through lengthy training or learn new tools. This reduces the time required to implement a BI solution to weeks or months, instead of months or years, resulting in a faster return on the investment.

Terminology

Following is a brief introduction to the technologies and acronyms used in this paper:

·         WSS – Microsoft Windows SharePoint Services 3.0. WSS 3.0 provides a foundation for building Web-based business applications and is available as a free download for basic team-based SharePoint sites. WSS 3.0 is also used in Office SharePoint Server 2007, which provides additional collaboration and portal capabilities (covered later in this paper). For more information about WSS, see Microsoft Windows SharePoint Services 3.0 on Office Online.

·         MOSS - Microsoft Office SharePoint Server 2007. MOSS includes collaboration and workflow functionality. MOSS also enables forms and business processing, personal sites, enterprise searching, and much more. For more information about MOSS, see Microsoft Office SharePoint Server on Office Online.

·         SSRS – SQL Server 2005 Reporting Services. SSRS is the reporting technology that was first introduced in SQL Server 2000 as an add-in and is built into the core product with the release of SQL Server 2005. SQL Server 2005 Service Pack 2 (SP2) further enhanced SSRS by tightly integrating reporting into the SharePoint environment for a consistent experience to the end user. For more information about SSRS, see SQL Server 2005 Reporting Services.

The integration of Reporting Services with SharePoint products and technologies is referred to as SharePoint integrated mode. Hosting Reporting Services reports in the Reporting Services database and managing content through Report Manager is referred to as Native mode*.* This paper discusses SharePoint integrated mode.

Integration Architecture

SQL Server 2005 Reporting Services integration with SharePoint products and technologies was introduced with SQL Server 2005 Service Pack 2 as an additional ** option for delivering and managing Reporting Services content; the traditional Reporting Services architecture with delivery and management though Report Manager is still available. The delivery of reports through SharePoint technologies is an alternative option for organizations that are looking for one central location for accessing, managing, and sharing both structured and unstructured data for improving business insight.

To enable tight integration between SharePoint Products and Technologies and SQL Server Reporting Services, the Reporting Services architecture is enhanced to enable Report Server users to store reports in SharePoint document libraries and leverage the SharePoint security model. The overall architecture is shown in Figure 1.

 

Component architecture for SharePoint integration

Figure 1: Reporting Services / SharePoint Integration Architecture

 

A number of key functional enhancements were made to SQL Server 2005 SP2 to make Reporting Services work in SharePoint integrated mode:

·         Catalog Synchronization – When a user works with a report in the SharePoint environment, the report definition language (RDL) file is stored in the SharePoint content database. SharePoint technologies communicate with the Reporting Services Web service to perform various operations, including rendering the report and exposing the report through the Reporting Services API. Reporting Services ensures that it renders the latest published version of the report by synchronizing the Report Server database content with the appropriate SharePoint database content.

·         Report Security – A security extension in SP2 enables you to maintain report security in MOSS or WSS for operations on the report server. This security extension supports both Windows integrated and trusted account modes of authentication between SharePoint Server and Report Server.

·         Report Viewer Web Part – The Report Viewer Web Part has been updated to display rendered reports natively in a SharePoint Web application and to enhance the overall functionality of using Reporting Services in a SharePoint environment. The Report Viewer Web Part is discussed later in this paper in Dashboards and Web Parts.

·         Report Management UI – User interface elements have been built for SharePoint technologies to enable report management through the common SharePoint interface. This includes multiple content types, Web pages, and context-sensitive menus for reporting objects, such as reports, data sources, and data models. Because of these report management UI enhancements, managing reports in Report Manager is no longer supported when you use Reporting Services in SharePoint integrated mode.

Report Server

Report Server is still responsible for rendering reports, synchronizing reports, securing reports, managing subscriptions, and virtually all functionality required by Reporting Services. Report Server requires that the following software be installed:

·         SQL Server 2005 Service Pack 2 – Service Pack 2 contains the enhancements required to communicate with a SharePoint server and to manage integration with SharePoint technologies.

·         SharePoint Object Model – The SharePoint object model must be installed on the Reporting Services server. If you use the same server for SharePoint and Reporting Services, the object model is already installed. If you use a separate server for Reporting Services, you must install the object model on the report server as well. Regardless of whether the SharePoint farm is a WSS or MOSS farm, to install the object model, first install the WSS or MOSS Web front end on the report server. After that, use the SharePoint Products and Technologies Configuration Wizard to  configure the object model  to join the existing WSS or MOSS farm. For details, see How to: Install the Windows SharePoint Services Object Model on a Report Server Computer in SQL Server 2005 Books Online.

SharePoint Server

Either MOSS or WSS is used as a common mechanism for viewing and managing reports. They are also used as a common security mechanism for your reports and other documents. SharePoint Server requires that the following software be installed:

·         Microsoft SQL Server 2005 Reporting Services Add-in for SharePoint Technologies – Also known as the Reporting Services Add-in, this program is used to extend the functionality of SharePoint to implement the following functionality:

·         Context-sensitive menus – Any SharePoint document library that is enabled for Reporting Services shows the user a special menu that enables report, report model, and report data source management. See also Report Manageability later in this white paper.

·         Report Viewer Web Part – The Web Part communicates with the Report Server Web service to render and display reports within the SharePoint environment in Full Page view or from a SharePoint dashboard.

·         Report Management pages – Additional pages are installed on the SharePoint server and are made available through the common SharePoint management environment to support the tasks of managing reports. Management includes operations such as uploading and deleting reports, viewing history, and managing subscriptions.

·         Content Types – Additional SharePoint content types are installed for creating Report Data Sources, Report Builder Reports, and Report Models.

Note SharePoint technologies require that all of the Web servers in your server farm have the same SharePoint products and technologies installed. While SQL Server 2005 Reporting Services with SP2 can integrate with either MOSS or WSS, installing WSS on a Reporting Services computer and joining it to a MOSS farm (or vice versa) is not supported.

Server Configuration

To enable integration between MOSS or WSS and Reporting Services, you must configure each server. Each instance must be configured even if both run on the same server. The report server can be installed on the same server as SharePoint, or it can be installed on a different server. Install MOSS or WSS and Report Server on different servers when the workload needs to be spread over multiple servers. The software requirements and configurations for each server are described in the next two sections.

Report Server Configuration

After installing SQL Server 2005 Service Pack 2, use the Reporting Services Configuration Tool to configure whether the Reporting Services instance should run in SharePoint integrated mode or in Native mode. To run in integrated mode, when the Report Server database is created, select the Create the report server database in SharePoint Integrated mode option. The report server database stores all Reporting Services-related content.

After the database is configured for SharePoint integrated mode, Report Manager, which is a user interface for managing reports when Reporting Services is running in Native mode, is not supported. Report Manager is not needed in SharePoint integrated mode because all report management is done from within the SharePoint environment.

SharePoint Configuration

When you install the Reporting Services Add-in on the SharePoint server, the new Reporting Services feature is automatically installed. This feature and its configuration options in the SharePoint Central Administration site are shown in Figure 2.

 

Bb969100.SharePoint_SQL_IntegrationFig2(en-us,SQL.100).jpg

Figure 2: Configuring Reporting Services in Office SharePoint Server 2007

 

To configure Reporting Services in WSS or MOSS, set each of the following options (shown in Figure 2):

·         Manage integration settings – Used to configure the Report Server Web service URL and authentication mode (either Windows or other trusted account).

·         Grant database access – Used to provision the service accounts for the Report Server Web service and the Report Server Windows service for appropriate access to the SharePoint configuration and content databases.

·         Set server defaults – Used to configure multiple Report Server system properties.

Authentication Modes

When you configure Reporting Services to work in SharePoint integrated mode, you must configure SharePoint to securely connect to the report server. You can select from two options:

·         Windows Integrated – The SharePoint user’s Windows identity is used to perform operations on the Report Server. Integrated authentication works only when the report server and the SharePoint server are the same server or when Kerberos delegation is enabled between the servers.

·         Trusted Account – In this mode, the SharePoint application pool identity is used to access the report server. SharePoint user information is passed along to the report server as well, to ensure that operations are performed using the SharePoint user’s context and permissions. Trusted account authentication is required when forms or custom authentication is used over the Internet or in an extranet scenario.

Forms Authentication

If you want to expose your Reporting Services reports through your SharePoint site to a group of people who do not have Active Directory® or domain accounts on your network, you can use forms authentication. Forms authentication is useful in scenarios such as extranets where your partners need access to a SharePoint site, but you do not want to, or cannot, give them access to your network.

When you use forms authentication, you configure your SharePoint site to use an authentication mechanism called a membership provider. The membership provider takes authentication information on a Web form and validates it according to the functionality of the membership provider. For example, the AspNetSqlMembershipProvider uses ASP.NET to validate the input taken from the Web form against a SQL Server database. After authentication, the user is permitted to use the SharePoint site. More information about the steps to configure forms authentication can be found at Forms Authentication in ASP.NET 2.0.

Note Install the Reporting Services hotfix 939942 before you configure your SharePoint Web application to use forms authentication.

Firewalls

Viewing and managing reports in SharePoint is possible through a firewall over the Internet, but comes with a set of challenges. Figure 3 illustrates this network topology. In this scenario, a browser makes a request through the corporate firewall to MOSS or WSS across port 80 for http traffic and port 443 for SSL traffic. SharePoint Server, in turn, requests the Report Server Web service to render the selected report. This request is made based on the configuration of MOSS or WSS.

Bb969100.SharePoint_SQL_IntegrationFig3(en-us,SQL.100).gif

Figure 3: Using SharePoint across a Firewall

The scenario shown in Figure 3 is very common, but there is one caveat: the URL specified for communicating to the Internet-facing SharePoint server must be in the Default zone.

To enable a browser to work through a firewall when Reporting Services is running in SharePoint integrated mode, follow these steps:

1.    On the Application Management tab of the SharePoint Central Administration Web site, configure Reporting Services to use the URL of the report server.

2.    On the Operations tab of the SharePoint Central Administration Web site, under Alternate access mappings, ensure that public URL to access the Internet-facing SharePoint Web application is in the Default zone.

3.    Test to confirm that the SharePoint site correctly communicates with the report server over the Internet.

Caution   In SharePoint integrated mode, you cannot configure the report server URL to work both over the Internet and behind the firewall on your intranet at the same time. However, this functionality is planned for an upcoming release of SQL Server Reporting Services.

SharePoint Server Farm

Using SharePoint products and technologies in a SharePoint Server farm deployment enables you to spread the workload across multiple Web servers. Each site in the SharePoint Server farm must be configured as follows:

·         Each server in the SharePoint Server farm that runs the Windows SharePoint Services Web service must have the Reporting Services Add-in installed.

·         Each SharePoint site collection should have Reporting Services activated (see Figure 2).

·         Have users access the load-balanced URL to the Web farm. Do not have them access the URL for individual sites in the Web farm.

Note A SharePoint Server farm is different from a Reporting Services scale-out configuration. Reporting Services uses a single database instance to store and synchronize report metadata from all report servers in the farm. There is only one report server for a given SharePoint Server farm. This URL is specified on the Manage Integration Settings page in the SharePoint Central Administration Web site.

 Report Publishing

After Reporting Services is configured for SharePoint integration, publishing reports to a SharePoint server is very easy. Developers can publish reports by using the SQL Server Business Intelligence Development Studio (a Microsoft Visual Studio®-based development environment that is included with SQL Server 2005). End users and business analysts can use Report Builder to publish reports.

Using SQL Server Business Intelligence Development Studio

Before you can publish a report or data source from SQL Server Business Intelligence Development Studio, you must configure the deployment properties for the project that contains the reports. Figure 4 shows the properties for a Reporting Services project.

publish

Figure 4: Properties for a Reporting Services Project

Here is an example to help you understand how to configure URLs to work with a SharePoint document library. Assume that you have two SharePoint document libraries, ReportsLibrary and Data Connections, located in a SharePoint **** site named Reports on a server named localhost. You can select from these deployment options:

·         OverwriteDataSources – If True, overwrites data sources located in the TargetDataSourceFolder location.

·         TargetDataSourceFolder – Absolute URL to a SharePoint folder or document library that stores report data sources. If it is not specified, the absolute URL entered in the TargetReportFolder is used to store the report data source. The format for this URL is http<s>://<server>/<site>/<document library>. An example is https://localhost/Reports/Data Connections.

·         TargetReportFolder – Absolute URL to a SharePoint folder or document library that stores reports. The format for this URL is http<s>://<server>/<site>/<document library>/<folder>. An example is https://localhost/Reports/ReportsLibrary.

·         TargetServerURL –Absolute URL to the SharePoint site that contains the TargetReportFolder or TargetDataSourceFolder. The format for this URL is http<s>://<server>/<site>. An example is https://localhost/Reports.

Using Report Builder

Report Builder is a Reporting Services client tool that enables end users to create, modify, and share reports. To maintain security on the data that is exposed to end users, and to translate the often complex relational table structures into business-friendly terms, Report Builder uses a report model, which is basically a semantic layer. A report model is used to determine which tables and fields from an underlying data source are exposed to Report Builder.

Report Builder is a click-once application that can be automatically launched from a SharePoint document library via the browser in SharePoint integrated mode. Figure 5 shows a new Report Builder report that uses a report model.

Bb969100.SharePoint_SQL_IntegrationFig5(en-us,SQL.100).jpg

Figure 5: Creating a report with Report Builder

After a report is created or edited, it can be previewed, and then published to a SharePoint document library. Publishing a Report Builder report is as simple as clicking the Save button and navigating to the correct SharePoint document library.

Even though Report Builder is launched from SharePoint, it is actually a Windows-based application that is installed on the user’s computer. Therefore, it does not run under the same security context as the SharePoint Web session, but runs by using the user’s actual logged-in Windows credentials. This means that users are required to log on to SharePoint a second time if they are using forms authentication.

Report Management

When you are using Reporting Services in integrated mode, reports are managed completely within the SharePoint environment. If you navigate to a document library that contains reports, clicking a report brings up the context menu shown in Figure 6.

Bb969100.SharePoint_SQL_IntegrationFig6(en-us,SQL.100).jpg

Figure 6: Report Manageability Options for a Report

Properties

Click Edit Properties on the menu to change certain properties of the selected report, report data source, or report model. Click View Properties on the menu to view properties. You manage properties on Reporting Services items the same way as you manage any other item in a document library.

Permissions

Click Manage Permissions on the menu to access the permissions for the selected document library or item. User permissions for Reporting Services items (reports, report data sources, and report models) are managed the same way as you manage permissions for any other document in a document library. However, the permissions assigned to items in SharePoint have special meaning to the Report Server Web service. The following table describes this mapping:

 

Permission

Report Server Operation

Manage Lists

·    Create a folder in a SharePoint library during a publish operation from an authoring tool.

·    Manage report history.

Add Items

·    Add reports, report models, shared data sources, and resources (external image files) to SharePoint libraries.

·    Create shared data sources.

·    Generate report models from shared data sources.

·    Start Report Builder and create a new report or load a model into Report Builder.

Edit Items

·    View past versions of a document, including report history snapshots.

·    Edit item properties for reports and other documents.

·    Set report processing options.

·    Set parameters on a report.

·    Edit data source properties.

·    Create report history snapshots.

·    Open a report model or a model-based report in Report Builder and save changes to the file.

·    Assign clickthrough reports to entities in a model. Replace a report definition, shared data source, report model, or resource with a newer version (replace file, preserve metadata).

·    Manage dependent items that are referenced in a report or model.

·    Customize the Report Viewer Web Part relative to a specific report.

Delete Items

Delete reports, report models, shared data sources, and other documents from a library.

View Items

Open a report, report model, and other document and have it processed on the report server.

Open Items

·    View a list of shared data sources.

·    Download a copy of the source file for a report definition or report model.

·    View clickthrough reports that use a report model as a data source.

View Versions

View past versions of a document and report snapshots.

Delete Versions

Delete past versions of a document and report snapshots.

Create Alerts

Create, change, and delete subscriptions that use the Reporting Services delivery extensions to deliver reports to target locations. Only the subscription owner and users who have Manage Alerts permission can perform these actions.

 

Subscriptions and Delivery

Subscriptions enable users to automatically receive reports through a variety of delivery methods. While Reporting Services is designed with an extensible delivery mechanism for custom delivery types, the default delivery types are:

·         E-mail – Sends a report via e‑mail. Users can configure the To:, CC:, and BCC:, subject, and priority of the e‑mail.

·         Windows File Share – Sends reports to a Windows file share. You can specify the file name and security credentials required to write a file to the share.

·         Null Delivery Provider – Generates subscriptions that preload the report cache. This type of subscription can help increase report performance.

·         SharePoint Document Library – Sends reports to a specified SharePoint document library. The page where you configure this is shown in Figure 7.

 

publish

Figure 7: Report Delivery to SharePoint Document Library

 

To create a new subscription, click the Add Subscription button. Select the delivery type for the subscription and configure the properties for the selected type of subscription.

Note   Before you can create a subscription for a report, the data source that is associated with the report must have the credentials that are stored in the report or be associated with a shared data source that uses stored credentials.

Data Sources

Report data sources indicate where the source of the data for a report is located. Data sources specify the connection strings and security credentials that are required to locate the data on your network. Reporting Services supports two types of data sources:

·         Shared – Stores the data source definition (.RSDS) or report model (.SMDL) in a common location so that it can be used by multiple reports. Depending on the number of data sources you plan to have, you may want to create a SharePoint folder where you store all your data sources.

·         Custom – Stores a one-time definition used by the selected report.

To manage a data source for a single report, click Manage Data Sources on the menu. You can select a shared data source or create a new custom data source.

To add a shared data source, navigate to the document library that will contain the data source. Click the New button, and select Report Data Source. This brings up a page where you can configure the shared data source.

Note   The Report Data Source content type must be explicitly added in the document library settings before it will appear in the New drop-down menu.

Parameters

Parameters are used to pass values to a report. Parameters are defined when a report is created. To change parameter values or properties, select the report and click Manage Parameters on the menu. You are presented with a list of the parameters that are defined for the selected report. You can change existing parameters at this screen, but you cannot add or remove parameters. To add or remove parameters for a report, you must change the report definition.

To manage an existing parameter, click the parameter name. This brings up a screen that enables you to access the following properties of the parameter:

·         Parameter Name – The name of the parameter as it is defined in the report. This property cannot be changed at this screen. You can change it in the report definition.

·         Data Type – The type of data expected by the parameter. This property cannot be changed at this screen. You can change it in the report definition.

·         Default Value – Which value, if any, to use as the default. The values presented in this screen are defined in the report definition. To change the domain of values, edit the report definition.

·         Display – Controls what the user sees on the screen. Indicate how you would like the parameter to prompt the user for a value, or select the Hidden or Internal options.

Processing Options

Processing options are used to instruct Reporting Services how to run reports. Processing options are useful for creating historical views of reports for audit purposes. Reports can also be processed and cached to enhance performance. To set processing options, select the report, and then select Manage Processing Options from the menu to configure the following options:

·         Data Refresh Options – Specify from where the selected report get its data. Select from the following options:

·         Use live data – Always queries the data source. This ensures that report data is up to date, but can negatively impact performance.

·         Use cached data – Always uses cached data to run the report, if the cache exists. If it does not, live data is used. Reporting Services maintains a cache based on the parameter values selected by the user.

·         Use snapshot data – Always uses data that was explicitly taken as a snapshot. For example, a report can always run on last month’s data if a snapshot was taken at that time.

·         Processing Time-out – Configure when Reporting Services should stop trying to execute a report and time out. A time out helps to prevent excess server resources from being used on a single report. The following time-out options are available:

·         Use site default setting – Uses the time-out setting that is configured in the Set server defaults option in the Reporting Services application under the SharePoint Central Administration site (see Figure 2).

·         Do not time out report processing – Runs a report indefinitely. Use this option with caution, as it can cause poorly written data sources to consume too many server resources.

·         Limit report processing (in seconds) – Runs a report for a specified number of seconds, then times out.

·         History Snapshot Options – Select one or more options for how you would like historical report snapshots to be taken and stored. Select from the following options:

·         Allow report history snapshots to be created manually – Allows users to create snapshots on an ad-hoc basis for the selected report.

·         Store all report data snapshots in report history – Stores snapshot data in history.

·         Create report history snapshots on a schedule – Creates history snapshots at a predefined interval.

·         History Snapshot Limits – Use to limit the number of snapshots for the selected report. This helps to conserve disk space on the server. Choose from the following options:

·         Use site default setting – Uses the snapshot limit setting that is configured under the Set server defaults option in the SharePoint Reporting Services application (see Figure 2).

·         Do not limit the number of snapshots – Enables unlimited snapshots. Use this option with caution, because it can consume all available physical disk space.

·         Limit the number of snapshots to – Limits the number of snapshots to a specified value.

History

Report history shows a list of all snapshots that have been taken for the selected report. It also enables you to take an instant snapshot of the report. The View Report History menu item presents a screen that shows all snapshots taken for the selected report, including the date and time the snapshot was taken.

Workflow/Approval

An additional advantage of running Reporting Services in SharePoint integrated mode is that you can use SharePoint’s workflow and approval mechanisms. As long as the document library that stores a report has workflow configured, a report must be approved before it can be published and made available to the public. To configure who can approve a report, see Permissions earlier in this paper.

If workflow is enabled for a document library, an additional menu item is displayed as Approve/reject. Click this menu item to approve or reject the document. You can select from these options:

·         Approved – The document is public—it is available to anyone with view or read permissions.

·         Rejected – The item is not made public.

·         Pending – The item is not available to someone with view or read permissions, but is available to the person who created the document and to anyone with the Manage Lists permission.

Dashboards and Web Parts

Dashboards are a visual way to enable a user to get impressions of the state of their business “at a glance.” Dashboards can be used for displaying all types of data, including Key Performance Indicators (KPIs), scorecards, scoreboards, and even reports. It is a common scenario to have a SharePoint dashboard that serves as a starting place for users to generate business reports. Reports can be segmented by business function, role, department, or any other division that makes sense for your business. Building dashboards and scorecards is largely handled by Microsoft Office PerformancePoint™ Server 2007 and is out of the scope of this paper. However, you can use Microsoft SharePoint technologies to build dashboards using Web Parts.

Report Viewer Web Part

The Report Viewer Web Part is added to the SharePoint Web Parts gallery when the Reporting Services add-in is installed. The Report Viewer Web Part is used to render reports from any document library that contains Reporting Services reports in full page view.

You can embed Reporting Services functionality into any page or dashboard on a SharePoint site. For example, you can easily render a report of year-to-date sales on the home page of your SharePoint intranet site by using Reporting Services.

To add a Report Viewer Web Part to your site, simply edit the SharePoint page and add a new Web Part to a zone. Select SQL Server Reporting Services Report Viewer, located in the Miscellaneous section, as shown in Figure 8.

 

Bb969100.SharePoint_SQL_IntegrationFig8(en-us,SQL.100).jpg

Figure 8: Adding the Report Viewer Web Part

After the Report Viewer is added to the Web page, you can quickly render a report. Open the tool pane and browse to the report in a SharePoint document library. The selected report is rendered on the page, as shown in Figure 9.

Bb969100.SharePoint_SQL_IntegrationFig9(en-us,SQL.100).jpg

Figure 9: Viewing a Report in the Report Viewer Web Part

Filter Web Parts

Adding a Report Viewer to a Web page in a SharePoint site renders a Reporting Services report. But what if you want to limit (or filter) the data displayed in the Web Part, based on a selection in a different Web Part? There are many filter Web Parts that enable this.

For example, suppose you have two Report Viewer Web Parts on a page. Each displays a different view of company sales data. One displays a matrix report and one displays a graph. It is cumbersome to filter the date of each Web Part separately, so a Date Filter Web Part could be very helpful in assigning the date to each report at the same time.

To use a filter Web Part, after one or more Report Viewer Web Parts are added to the Web page, place a Date Filter Web Part on the page by using the same procedure described in Report Viewer Web Part earlier in this document. Now just connect the Date Filter Web Part to the Report Viewer Web Part(s). This is necessary so that the Report Viewer Web Part(s) know when a date is entered into the Date Filter Web Part. To assign the value of the Date Filter Web Part to the Report Viewer Web Part(s), simply modify the connections of the Report Viewer Web Part(s), as shown in Figure 10.

Fig_09.png

Figure 10: Specifying Report Viewer Connections

Once the Date Filter Web Part is connected to the Report Viewer Web Part, a date can be entered in the Date Filter Web Part and the report is updated accordingly.

Conclusion

Microsoft Business Intelligence puts decision making in the hands of every user within an organization. Combining familiar end-user tools with powerful back-end technologies is the key to rapid design, development, deployment, and acceptance of BI solutions.

The integration of Microsoft SharePoint product and technologies (Windows SharePoint Services 3.0 and Office SharePoint Server 2007) with SQL Server 2005 Reporting Services provides a single environment for all information needed by a business user, available through a common Web-based user interface.

With the BI platform in SQL Server 2005 and the 2007 Microsoft Office system, users have all the tools they need to start making better business decisions.

About the Author

Anthony T. Mann is the President of Mann Publishing Group, which publishes business and technical books under its Rational Press and Agility Press imprints. He is a veteran computer book author, writing more than 15 titles and regularly produces technical whitepapers and other content for Microsoft. He can be reached by e‑mail at tmann@mannpublishing.com.

 

For more information:

https://www.microsoft.com/sql/

https://www.microsoft.com/BI