SQL Server 2005 Service Pack 2 (Integration between Reporting Services and SharePoint 2007)
Yesterday I was rewriting my two-day Reporting Services 2005 course to a three-day course. The previous times I gave this course I ran out of time and couldn’t explain every module in depth. So I decided to extend the course and add an extra module about Implementing Business Logic with MDX. This new module contains information about using Analysis Services Cubes to create reports. I’ll focus on matrix and graphics data regions as well. Besides adding a new module I was adding some content to the other ones. When I was extending the Programming Reports module I found out about SQL Server 2005 SP2.
As you know, we already have SharePoint Web Parts, and they work well. However, Microsoft will include much deeper integration with WSS/OS in SP2. Some limitations of the current WebParts are:
- Separate content stores;
- Different security models;
- Different management UI;
- No filter web parts.
The reporting team did a great job to fully integrate Reporting Services into WSS/OS. After installing SP2, you can run Reporting Services in "native mode" (like it’s today), or "SharePoint integration mode". The integration mode will enable publishing, viewing, and management of rich reports.
Integration Architecture
Management
SQL Server 2005 SP2 is installed on a report server along with the SharePoint Object Model (farm install). The Reporting Services feature pack is installed in WSS “v3” and the Report Server web service URL is registered with WSS farm. To create a new Report Server database in “SharePoint Integration mode” you can use the Reporting Services Configuration Tool. In SP2, the tool has been modified to include some additional UI to do this work.
Note: Unfortunately it’s not possible to migrate from existing SQL Server 2005 Reporting Services (SSRS) installations.
Using “SharePoint Integration mode”, most of the objects you're used to store in reportserver database are instead stored in SharePoint database. Some functionality like scheduling, subscriptions, snapshots and caching are still stored in the reportserver database. Lets say they implemented every feature of Reporting Services in SQL Server 2005 Express in WSS.
All other objects like reports, data sources, and report models are published to SharePoint document libraries. When a report is selected in WSS, the report viewer Web Part calls the report server API to process and render the report. Users can manage properties and subscribe to reports through WSS UI which calls the RS SOAP API. New report server delivery extension allows for rendered reports to be delivered to WSS document libraries (including Report Center). Another nice thing is all cool features of SharePoint like versioning, collaboration and workflows are now available to all objects.
Note: Report Manager (Web UI / Management Studio) is not supported in “SharePoint Integration Mode” and it's not possible to run SSRS in both modes at the same time.
Another major change is security. All permissions are set within WSS and will give you a consolidated place to manage security. For Example when using Team Foundation Server you now have to set rights in TFS, SharePoint and Reporting Services. I hope tools like this will fully integrate with SharePoint as well. The design tools (Report Designer, Report Builder, Model Designer) are also updated to work with WSS.
Note: It looks like data-driven subscriptions is cut out. Normally this is really powerful feature. So I hope the Microsoft team is aware of this and will soon add it to the “SharePoint Integration mode”.
SharePoint User Interface
Viewing reports while in integrated mode is like any normal webpage in SharePoint. You can use the Report Viewer WebPart to show a report in full page view or on Web Part Pages. This WebPart wraps the ReportViewer ASP.NET Viewer Control and handles report rendering calls to report server. Besides handling it will give you all the properties by using its own EditorPart and verbs. The properties can be devided by:
- Report: ReportPath, HyperlinkTarget
- View: AutoGenerateTitle, AutoGenerateDetailLink, ToolBarMode, ParametersMode, ParametersAreaWidth, DocumentMapMode, DocumentMapAreaWidth
- Parameter: Default Values
At last is will also support Filter Consumer and Row Consumer interfaces for specifying report parameter values via filter Web Parts. This way you can slice Excel Workbooks and Reports on a single Web Part page. Superb.
Document Library UI
Viewing Reports (Full Screen)
Report Properties (Parameters)
Report Viewer Web Part
Other important changes
Integration will give you new report server SOAP and WMI interfaces endpoints.
The SOAP Proxy is now installed in WSS to support firewall deployment.
Most API’s are mapped to SharePoint object model calls. For example, ListChildren returns items in the content database.
File Extensions are mapped into Report Server types (Report / Data Source / Model / Resource)
Reporting Services security role definitions are replaced with SharePoint principles. CreateRole, DeleteRole, GetRoleProperties, SetRoleProperties are all removed
Summary
I think the the team did a great job. They have said they will deliver a version with no differences between the native and the integrated mode, but it won’t happen in SP2. The integrated mode will be very populair with customers deciding to use SharePoint and are using less complex solutions. More complex solutions using subscriptions and lots of administration will still use native mode.