I attended TechReady3 last week, and one of the most interesting sessions focused on the work the SSRS team has done around Reporting Services / SharePoint integration in SQL Server 2005 Service Pack 2 (due next year).
As you know, we already have SharePoint Web Parts, and they work well. However, Microsoft will include much deeper integration with MOSS (Microsoft Office SharePoint Server 2007) in SP2.
After installing SP2, you can run Reporting Services in “native mode” (what you do now), or “SharePoint integration mode”. You get some pretty cool functionality in SharePoint integration mode, but it does come at a cost.
Anyway, you’ll configure SharePoint integration mode using the SQL Server Reporting Services configuration tool — In SP2, the tool has been modified to include some additional UI to do this work. The SSRS WMI interfaces have also been extended, which goes without saying.
Once in SharePoint mode, most of the objects you’re used to seeing in reportserver db are instead stored in SharePoint. Reports, data sources, models and resources all “live” in SharePoint. Metadata about subscriptions and a few other pieces of functionality (schedules, caching) stay in the reportserver database.
In SharePoint Integration mode, all management functions go through SharePoint. This is good and bad, in my opinion. For example, since your reports are now stored in MOSS, you get document versioning (hooray!) and other MOSS features such as workflow and collaboration.
However (The bad news), in SharePoint integration mode, the tools you’ve grown accustomed to using to manage SSRS will no longer function: Report Manager and SQL Management Studio won’t work anymore…ALL administration (uploading, rendering, and delivering reports) happens in MOSS. You cannot run your SSRS instance in “dual mode”, by the way – you must choose either native of integrated mode. There also isn’t an automated upgrade path between native mode and integrated. If your reports are already sitting in the reportserver database, you’ll basically need to export them (or write code to do so) and then import them into an instance of SSRS running in SharePoint mode. VS 2005 and BIDS will be able to publish directly to MOSS, which is a good thing.
From a management perspective, another major change while in integration mode is around security: If you run in SharePoint integration mode, MOSS handles Reporting Services security. Again, this is good and bad: you get a single, consolidated place to manage security, but you’ll lose any investment you’ve made in SSRS Web Service security and deployment code you’ve written. Since MOSS does security differently than SSRS does, the SSRS Web Service APIs for security are no longer leveraged. Bummer! Finally, you won’t get complete feature parity while in SharePoint integration mode — Linked reports, data driven subscriptions, and job management (does this mean no creating schedules? I don’t know) are not available in SharePoint mode. I find the loss of data driven subs particularly painful as it is a really powerful feature. As a consolation, the Microsoft team has written a new SharePoint delivery extension to allow you to deliver reports right to the SharePoint library. You also can launch Report Builder from inside MOSS.
Viewing reports while in integrated mode is really nice, and will be second nature to anyone already using the existing web parts. You’ll be able to view reports in either a “full page” view or inside Web Part pages. (This next part is really cool). Our Web Part controls will support Row and Filter consumers for specifying parameter values via filter Web Parts. What this means to you is you could drop two (or more, I guess) SSRS report Web Parts on a page which each display a different report filtered by date, and then filter them both using the same filter dialog. Very cool! You could even display / filter an SSRS report and Excel workbook using the same filter. Nice.
For displaying controls outside of MOSS, the Report Viewer controls and URL Access will continue to function, except that you’ll point to your reports inside the MOSS document library instead of out on http://somebox/reportserver?/path/to/report. When using URL Access you’ll still be able to specify parameter and deviceinfo settings.
Microsoft adds a new SharePoint SOAP endpoint for use while in integration mode. This endpoint will REPLACE the two you’re used to, so you’ll need to refactor your code a tiny bit. From what I saw, the methods you’ve grown to know and love like Render() and RenderStream() work just like they used to, but you’ll call them from the SharePoint endpoint rather than from where you’re used to. This will require you to update a few project references, but no big deal, really.
You know all that I know now. The team said they are working on getting us complete feature parity between running in native and integrated mode, but it won’t happen in SP2. Once we’re there, integrated mode is a no-brainer. I think that integration mode will be very popular for new deployments of SSRS and for existing (less complex) workgroup deployments. I’m not so sure about large existing enterprise deployments – there seems to be a high cost to pay around administration.