C H A P T E R  9

PowerPivot and SQL Server Reporting Services

Don't let what you cannot do interfere with what you can do.

—John Wooden

SQL Server Reporting Services is a set of features included with Microsoft's SQL Server product for the creation, management, and execution of predefined reports. PowerPivot for Excel and SharePoint can use SSRS reports as a data source for solutions developed in either mode of PowerPivot. Reversing the roles of PowerPivot and SQL Server Reporting Services is also possible—that is, SQL Server Reporting Services can utilize PowerPivot for SharePoint databases as a data source for SSRS reports. In this chapter, we will examine the case for using SQL Server Reporting Services and PowerPivot together in order to deliver analytics and predefined reporting from a common dataset.

Consider one of the potential uses of this architecture: organizations that already utilize SQL Server Reporting Services can reuse the datasets underlying existing reports as data tables for PowerPivot solutions. Logic and relationships established in the existing SQL Server Reporting Services report definition are available for ad hoc analytics using PowerPivot. When changes in data relations, business rules, or other logic are made to the SQL Server Reporting Services report, the changes “flow through” to the PowerPivot solution via the SSRS dataset.

Alternatively the flow of data from SQL Server Reporting Services to PowerPivot can be reversed. A PowerPivot for SharePoint solution can form the data source for a standard SQL Server Reporting Services report definition. A candidate scenario for this use of PowerPivot as a data source would be similar to the solutions outlined in Chapter 8 that show PerformancePoint services surfacing data from a PowerPivot pivot solution. The key difference to consider is the various rendering modes available in SQL Server Reporting Services to schedule reports as well as deliver the information in Adobe portable document format, word documents, HTML, and alternative file formats. From a data architecture perspective, connecting SQL Server Reporting Services to PowerPivot for SharePoint is very similar to the connection used with PerformancePoint services.

Certainly the purpose of this book is not to exhaustively cover SQL Server Reporting Services. Instead it is my intention to explain the tasks and operations necessary to create, store, and execute SQL Server Reporting Services definitions as they relate to PowerPivot as a data source and PowerPivot as a reporting front end. There are two example programs that form development environments you may use to create or alter SQL Server Reporting Services definitions files. The first is Report Designer, contained within both Visual Studio and Business Intelligence Developer Studio (BIDS). BIDS is available as part of SQL Server's installation. The second, Report Builder, is a click-once application available to SharePoint Server users. Report Designer provides report definition editing and management features without the complete software development environment of Visual Studio or BIDS.

Finally, there are two principal configuration modes for SQL Server Reporting Services. In native mode, SQL Server Reporting Services executes requests to save, edit, and execute report definitions without any special integration with SharePoint Server 2010. Configured with SharePoint integrated mode, SQL Server Reporting Services features for editing report definitions are enabled within SharePoint. These SSRS features enable users to edit SSRS reports just as any other document stored by SharePoint.