In this chapter, we’ll expand on the core Microsoft Office SharePoint business intelligence (BI) features discussed in Chapter 18, to include discussion and best practices for enhanced functionality offered through Microsoft SQL Server 2005 Reporting Services and Microsoft Office PerformancePoint Server 2007. You’ll realize additional BI capabilities such as the ability to export Web-based reports to Microsoft Office applications as well as additional formatting and drill-down features for reports and KPIs, and central management for distributed Microsoft Office Excel authoring environments.
First, let’s review the BI functionality within Office SharePoint sites offered through integration with SQL Server 2005 Reporting Services.
Reporting Services was initially introduced with Microsoft SQL Server 2000 as a tool to centrally create and publish reports against database data. It leveraged Microsoft Visual Studio 2003 as the report designer and authoring environment. SQL Server 2005 has seen Reporting Services reporting capabilities extend to include reporting against multiple data sources, including SQL Server, Object Linking and Embedding Database (OLE DB), SQL Server Analysis Services, Oracle, Open Database Connectivity (ODBC), XML, Report Server Model, SAP NetWeaver BI, and—included with Microsoft SQL Server 2005 Service Pack 2—Hyperion Essbase. SQL Server 2005 Business Intelligence Development Studio, an integrated development environment (IDE) that is hosted within Microsoft Visual Studio 2005 and installed as part of SQL Server 2005, includes Business Intelligence Project templates that developers can use to create custom reports and model data for back-end databases. A Report Designer enables developers to design and deploy reports to either Reporting Services or SharePoint document libraries.
Reporting Services has evolved to include extended functionality within SharePoint sites and direct integration with SharePoint permission sets. Information workers are now able to work directly with Reporting Services from within SharePoint document libraries. Configuration is dependent upon two main server components: SQL Server 2005 Service Pack 2 Reporting Services and SharePoint Server 2007 (or Microsoft Windows SharePoint Services 3.0). We will evaluate the numerous options in the following sections.
There are two modes in which Reporting Services can be configured and integrated with SharePoint Server 2007 sites. One is the default mode, namely, native mode. The other, SharePoint Integrated mode, was introduced in SQL Server 2005 Service Pack 2 and enables Reporting Services reports to be deployed directly to SharePoint document libraries. SharePoint Integrated mode also provides information workers with the ability to directly create and author Reporting Services reports from within document libraries.
In this chapter, we predominantly focus on using Reporting Services configured in SharePoint Integrated mode. However, we will show you the alternatives for including Reporting Services reports in SharePoint sites where the SQL Server 2005 Reporting Services can remain in native mode.
Two main components support Reporting Services running in SharePoint Integrated mode: SQL Server 2005 Service Pack 2 and Microsoft Reporting Services add-in for Microsoft SharePoint Technologies.
When you install Service Pack 2 for SQL Server 2005, a Reporting Services Configuration Manager is installed, which includes an additional configuration toolset (SharePoint Integrated) specifically for integrating Reporting Services with SharePoint. When a Reporting Services server is changed from native mode to SharePoint Integrated mode, additional functionality is added to the Reporting Services server, including security extensions to work directly with SharePoint permissions to control access to Reporting Services reports. Figure 19-1 shows the Reporting Services Configuration Manager dialog box where Reporting Services has been changed from its default native mode to run in SharePoint Integrated mode.
Note
Some screens within the Reporting Services Configuration Manager specifically reference Windows SharePoint Services, such as the screen shown in Figure 19-1, as opposed to SharePoint Server 2007, which we’re discussing here. However, be aware that the configuration applies to both Windows SharePoint Services 3.0 and SharePoint Server 2007.
Note
Any single instance of Reporting Services can run only in either native mode or SharePoint Integrated mode. Consider the impact of changing your SQL Server 2005 Reporting Services instance from native mode to SharePoint Integrated mode. For instance, you may already have existing applications that use the default native mode, or you may eventually roll out other applications that will work only with an instance of Reporting Services in native mode. An example is the PerformancePoint 2007 server reporting storage of operational and business reports, which is supported only on a Reporting Services instance running in native mode. One way to accomplish both native and SharePoint Integrated mode while minimizing licensing costs might be to create an additional instance of SQL / Reporting Services on the same SQL server. See the following blog post: http://mindsharpblogs.com/kathy/archive/2007/12/10/3788.aspx.
Note
You can download Service Pack 2 for SQL Server 2005 at http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&displaylang=en.
By switching Reporting Services from native mode to SharePoint Integrated mode, you can combine with the Reporting Services add-in for SharePoint Technologies, which is an add-in specifically architected to work with Reporting Services running in SharePoint Integrated mode to add additional reporting functionality in both SharePoint Server 2007 and Windows SharePoint Services 3.0.
To coincide with the earlier release of Service Pack 2 for SQL Server 2005, Microsoft released a Reporting Services add-in for SharePoint Server 2007 and Windows SharePoint Services 3.0 in early 2007 to take advantage of the additional functionality and Reporting Services integration throughout SharePoint sites and document libraries. The add-in, combined with Reporting Services running in SharePoint Integrated mode, provides powerful reporting features within SharePoint document libraries and gives information workers the ability to create and author Reporting Services reports using a special Report Builder application. This removes the reliance on developers to create and maintain Reporting Services reports. You can download the add-in at http://www.microsoft.com/downloads/details.aspx?FamilyID=1E53F882-0C16-4847-B331-132274AE8C84&display=en.