Microsoft SQL Server 2005 SP2 Reporting Services integration with WSS 3.0 and MOSS 2007
SQL Server 2005 Service Pack 2 (SP2), which will be released any minute now :-), enables deep integration between Reporting Services and SharePoint technologies (Windows SharePoint Services 3.0 and Office SharePoint Server 2007). This integration enables an end-user to view and manage reports completely from within a SharePoint environment. The following is an excerpt from the upcoming 2007 Microsoft Office System Business Intelligence Integration with SQL Server 2005 whitepaper. [Update March 17: Added the hyperlink for the whitepaper, which is 1.5 MB in size and in Word 2007 .DOCX format.]
<Lawrence />
There are numerous benefits and advantages to integrating Reporting Services with Office SharePoint Server 2007. Prior to SP2, Reporting Services reports were published to a report server and were executed solely in that environment. The integration point between Reporting Services and SharePoint was two separate Web Parts: Report Explorer and Report Viewer. These Web Parts made it possible to view reports within SharePoint, but presented these challenges:
- The report content source was always separate from the SharePoint content source.
- Reports were hosted on a report server and were only displayed within the SharePoint environment.
- Security of reports was managed by the report server, so there was no unified security model.
- The user interface was not consistent between managing and viewing reports because each was performed in a separate environment.
- Filtering of report data could only be done by using report parameters that had to be defined in the reports themselves.
- Internet-facing SharePoint sites required deploying the report server outside the firewall.
With SQL Server 2005 SP2, deep integration with Office SharePoint Server 2007 offers numerous advantages, including:
- Single consistent user interface for managing and viewing reports.
- A much richer user experience.
- Storing reports in Office SharePoint Server 2007 document libraries. This allows a report to use standard Office SharePoint Server 2007 features, such as workflow, versioning, and collaboration.
- Reports and all associated resources are stored within the Office SharePoint Server 2007 database and are automatically synchronized with the same reports that are stored and executed from the report server. Scheduling, caching, and subscriptions are stored in the report server database only because this functionality is not available natively in a SharePoint document library.
- Single security model for reports because they are treated like any other document inside a SharePoint document library.
- Common report filtering paradigm that is used in SharePoint document libraries and filtering Web Parts.
While Reporting Services reports can be managed in any site or document library, Office SharePoint Server 2007 includes out of the box functionality for reporting, referred to as the Report Center. Report Center is delivered as a site template to enable you to easily create an Office SharePoint Server 2007 site for hosting reports. When you create a site using this template, navigation bar links are added to manage reporting resources, as shown in Figure 1. Figure 1 also shows a sample dashboard with defined Key Performance Indicators (KPIs).
Figure 1: Navigation Bar Links Highlighting New Resources Category.
The navigation bar links in the Report Center site template are:
- Documents – Document library to store a collection of documents.
- Reports – Document library that can be used for managing Reporting Services and Excel Services reports.
- Dashboards – Listing of all dashboards that you can configure for your users. For example, you can have one dashboard that shows executives the report KPIs that are useful to them. However, project managers need to see more detailed information about their projects, so they would need a different dashboard with different KPIs.
- Resources – Folder containing these types of resources that can be used with Reporting Services reports:
- Data Connections – Document library that contains shared data sources and/or report models that can be used among all reports.
- Report Calendar – Calendar list that can contain any date-related information for a report or report schedule. For example, you can indicate on which dates report data is refreshed or loaded. This item has no specific integration points with Reporting Services.
- Reference Library – Document library for you to place documents that can help end-users with the navigation and usage of the report center. This item has no specific integration points with Reporting Services.
Having resources stored in separate document libraries enables you to configure versioning, workflow, and security separately for each resource. For example, you can configure security to only allow members of the IT department to modify or upload data connections. Likewise, you can configure workflow in Office SharePoint Server 2007 to require approval for new reports.
SQL Server 2005 Reporting Services can operate in either Native Mode or SharePoint Integration Mode. As Reporting Services supports multiple instances, you also have the option of using both modes simultaneously on a single server. This section outlines some considerations you should think about to help determine which mode to use in your organization.
The term Native Mode in Reporting Services 2005 Service Pack 2 refers to the existing way that Reporting Services operates. The report server is responsible for management of all Reporting Services functionality, including report storage, rendering, scheduling, subscriptions, security, and administration. Reports, data sources, and report models are all stored in the report server database.
Native Mode is appropriate for any organization that does not implement Windows SharePoint Services 3.0 or Office SharePoint Server 2007, or does not need the added functionality of Integration Mode (see next section). If your organization uses Windows SharePoint Services 2.0 or SharePoint Portal Server 2003, you will not be able to leverage the additional productivity and security features of Integration Mode until you upgrade to Windows SharePoint Services 3.0 or SharePoint Portal Server 2007.
Using Reporting Services 2005 in SharePoint Integration Mode enables some significant benefits to the end-user and the organization as a whole, as follows:
- Uses SharePoint document libraries to store reports, data sources, and report models.
- Enables publishing, viewing, management, and delivery of Reporting Services reports from the same user interface used for managing Office documents.
- Enables organizations to construct BI dashboards with rich reports.
- Leverages workflow and collaboration capabilities that are already available in Office SharePoint Server 2007.
When using SharePoint Integration Mode, some features that are available in Native Mode are replaced with comparable functionality in Office SharePoint Server 2007 or are no longer available. The Reporting Services features that are not available in Integration Mode are:
- Report Manager
- Management Studio
- My Reports
- Linked Reports
- Job Management
Note: There is no support for migrating native mode reports to SharePoint integration mode. You can use those reports, but you must manually republish them to the Office SharePoint Server 2007.
Integrating Reporting Services with Office SharePoint Server 2007 requires these components to be installed:
- SQL Server 2005 Reporting Services – hosts Reporting Services reports and communicates with Office SharePoint Server 2007 using a Web Services interface. The Reporting Services server also manages subscriptions and report caching. This server can be separate from the Office SharePoint Server 2007, but if it is, you must also do a basic install of Office SharePoint Server 2007 on the Reporting Services server to ensure that the Windows SharePoint Services (WSS) 3.0 object model is installed.
- SQL Server 2005 Service Pack 2 – enables Office SharePoint Server 2007 and Reporting Services integration on the report server.
- Office SharePoint Server 2007 –Serves as the central access point for all reports and business data. Alternately, you can use Windows SharePoint Services 3.0.
- Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies - This feature includes the Report Viewer Web Part and the new report management user interface.
After installing the required components to integrate Reporting Services with Office SharePoint Server 2007, both the report server and SharePoint server need to be configured. The following sections outline the configurations necessary to achieve integration.
After installing SQL Server 2005 Service Pack 2 on the Reporting Services server, a new configuration option becomes available, called SharePoint Integration, as shown in Figure 2.
Figure 2: Configuring Report Server SharePoint Integration.
After installing the Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies feature of the Feature Pack for SQL Server 2005 Service Pack 2, a new application becomes available under the Application Management tab. This new application is shown in Figure 3.
Figure 3: Configuring the Reporting Services Application in Office SharePoint Server 2007.
To configure Reporting Services, you must set each of the options shown in Figure 4, as follows:
- Manage integration settings – To configure the Report Server Web Service URL and Authentication Mode (either Windows or other trusted account).
- Grant database access – To configure the name of the report server database server instance name.
- Set server defaults – To configure report server timeouts and other options.
Once Reporting Services and Office SharePoint Server 2007 integration is setup, reports can be created and deployed to the Office SharePoint Server 2007. Creation of reports can be done in one of two ways: one for developers and one for end-users. Each is discussed in the following sections.
Developers use Visual Studio®-based SQL Server Business Intelligence Development Studio to create reports. After installing the SQL Server 2005 workstation components, many business intelligence project templates become available, as shown in Figure 4.
Figure 4: Visual Studio 2005 Business Intelligence Project Types.
Note in Figure 5 that Report Model Project is selected. A Report Model is a way to define the data which can be used by end-users to create reports (see next section). Alternatively, developers can create a blank Report Server Project or use the Report Server Project Wizard to help guide the user through the process of creating reports and specifying data sources.
End-Users can create reports as well as developers, but a developer must first make the data available to those users. Making data available is done using a Report Model. A Report Model defines the data source of a report, the security parameters for the data source, and the tables, fields, and relationships used for the model. This model is then published to an Office SharePoint Server 2007.
Once the Report Model is published to the Office SharePoint Server 2007, end-users can create reports using Report Builder. This is a ClickOnce application that is downloaded automatically from the Office SharePoint Server 2007 and installed as a component on the user’s workstation. Report Builder enables the end-user to create and publish reports to the Office SharePoint Server 2007 using the Report Model that the developer created. Figure 5 shows the Report Builder tool.
Figure 5: Designing Reports in Report Builder.
Once reports are created and deployed to the Office SharePoint Server 2007, they are ready to be viewed. Report security is set the same way it is for any SharePoint document. You can set security permissions on libraries, folders, or individual files.
If a user navigates to a report in a report library on the Office SharePoint Server 2007, the reports can be viewed simply by clicking the desired file name. The integration components know how to connect to the report server Web Service to render the report from within the Office SharePoint Server 2007 environment. Figure 6 shows a rendered report in Office SharePoint Server 2007.
Figure 6: Rendered Report within the Office SharePoint Server 2007 Environment.
Reports in a report library are rendered using a Report Viewer Web Part. Any SharePoint page can also be modified to display reports by using the Report Viewer Web Part. Report Viewer Web Parts can also be connected to SharePoint Filter Web Parts to limit the data displayed in reports.
When Reporting Services is integrated with Office SharePoint Server 2007, report management is then performed using the same SharePoint document library as the one used to launch reports. Managing reports is as simple as navigating to the correct report in the SharePoint document library and clicking the down arrow. The user is presented with a host of options, as shown in Figure 7.
Figure 7: Managing Reports in Office SharePoint Server 2007.
Figure 7 shows these possible report management actions:
- View Properties – Standard Office SharePoint Server 2007 functionality to view document properties. Organizations can employ standardization for properties that will be used in reports, such as including categories, sub-categories, timestamps, approvals, and more.
- Edit Properties – Standard Office SharePoint Server 2007 functionality to edit document properties. SharePoint Office Server 2007 can be configured to make properties mandatory. This can enable organizations to provide valid metadata for each report in the report library.
- Manage Permissions – Standard Office SharePoint Server 2007 functionality to manage the permissions of the document. This is one of the main benefits of integrating Reporting Services with Office SharePoint Server 2007. A common security model is used to secure reports, folders, files, and libraries. System Administrators can easily assign security and troubleshoot report execution problems.
- Edit in Report Builder – Reporting Services functionality to launch the Report Builder tool to edit the definition of the report (see Figure 6). Report Builder enables end-users to easily modify or create reports and publish them to the Office SharePoint Server 2007.
- Delete – Standard Office SharePoint Server 2007 functionality to delete the document from the document library.
- Manage Subscriptions – Reporting Services functionality to add, edit, and delete report subscriptions. Users that subscribe to reports can automatically be notified when data changes in reports. For example, sales people can subscribe to a report to automatically receive their weekly sales figures via email.
- Manage Data Sources – Reporting Services functionality to select a shared or custom data source for the report. Shared data sources enable one data source definition to be used across multiple reports.
- Manage Parameters – Reporting Services functionality to manage the values and prompts for all parameters passed into the report. Parameters are a way to limit the data that is displayed in a report. For example, a report parameter named Year can be specified to limit the report data to the selected year when the report is run.
- Manage Processing Options – Reporting Services functionality to choose processing and snapshot options and timeouts. Snapshots and timeouts affect the performance of the Reporting Services server. It is important to consider these options to keep the server performing as well as possible.
- View Report History – Reporting Services functionality to view the history of report processing.
- Send To – Standard Office SharePoint Server 2007 functionality to send the document to another location.
- Check Out – Standard Office SharePoint Server 2007 functionality to check out the document in order to publish changes. The ability to check out, and then publish a report, data source, or data model is one of the major advantages to integrating Reporting Services with Office SharePoint Server 2007. A report, or any other document, can be checked out, modified, and have workflow optionally applied to the document before it is published back into the Office SharePoint Server 2007.
- Publish a Major Version – Standard Office SharePoint Server 2007 functionality to publish a version of a checked-out document. When significant changes to a document occur, it can be helpful to flag the document as a major version so that users have an indication as to the scope of changes in the document. A major version uses the format of version 1.x, 2.x, 3.x, etc.
- Version History – Standard Office SharePoint Server 2007 functionality to view the published versions of a document.
- Alert Me – Standard Office SharePoint Server 2007 functionality to alert a user to changes in the document. This option notifies a user when there is a change to the report definition itself, as opposed to the data being changed in a report.
