Archive for month: November, 2006

As noted before, a major SSRS enhancement in the SQL Server 2005 Service Pack 2 is integration with SharePoint v3.0 and Microsoft Office SharePoint Server. After the RTM release of SQL Server 2005, the SSRS team has been hard at work to implement this feature. Starting with the newly released CTP2 build of SQL Server 2005 Service Pack 2, the SharePoint integration mode is available for public review and testing.

Here are my initial high-level observations after testing the SSRS SharePoint integration in CTP2.

The Good

Integrating reports in dashboards and portals is an increasingly popular requirement. It is obvious that the major design goal that the RS team has set up from the get-go was a seamless integration between SSRS and SharePoint. Many changes were made to the SSRS architectural stack to materialize this goal. The final result speaks for itself (see attached screenshot ).

Once the Report Server is configured in a SharePoint mode, the report definitions are equal citizens to the other documents that are deployed to the SharePoint repository. You can carry all of the report management and delivery tasks right within SharePoint. For example, you can upload the report definitions, set up report security, parameters, subscriptions, and view reports. No, no web-based report authoring yet (not in this release). Instead, use the Report Designer or Report Builder to deploy reports to a SharePoint library.

The Report Server security polices get superseded with SharePoint security. In other words, in SharePoint mode, the Report Server honors the SharePoint security policies and groups, e.g. Home Owners. Behind the scenes, viewing reports is carried by a Report Viewer web part which wraps the ASP.NET ReportViewer that debuted in VS.NET 2005.

Overall, you will undoubtedly like the tight SharePoint integration especially if you have used the old SharePoint Report Explorer web part which was just a wrapper on top of the Report Manager UI. As the popular add goes on, everyone can see the difference. SSRS reports just blend in and "light up" SharePoint. Moreover, the new integration model enables new features (native to SharePoint) that the Report Server doesn't support, such as version control of report definitions and workflow support. For example, you can set up a workflow that requires a formal approval when a new report is uploaded.

The Bad

Many moving parts. New web services endpoints (2006) and security extension are installed on the Report Server to support the SharePoint integration model. A Reporting Services add-in needs to be installed on the SharePoint box so SharePoint can "talk" to the Report Server (only single-server deployment scenario is supported in CTP, the final release will support installing SharePoint and Report Server on separate machines) .

Without going into technical details, a synchronization process takes place at runtime to synch the report definitions between the SharePoint repository and the Report Server catalog. In other words, the SharePoint is the master, the Report Server is the slave. Each time you run a report, the Report Server checks if the report definition exists in its catalog and, if not, calls down to SharePoint to retrieve and upload the report definition. That's because some management tasks are carried by SharePoint (mainly storing the report definitions), while others (subscriptions, snapshot caching, rendering) are executed by the Report Server. Upgrading an existing report catalog to SharePoint is not supported. You need to create a new report catalog enabled in a SharePoint integration mode (use the RS Configuration Utility) and upload manually or programmatically the existing report definitions.

The Ugly

My biggest gripe is the lack of backward application compatibility. If you have an existing application(s) that call down to the Report Server APIs and your requirements call for surfacing your reports to SharePoint you may find that this is not that simple. Not only the web service end points have changed but also the report paths are different to reflect the fact that report definitions are now stored in SharePoint, e.g. #. Further, some features didn't make to SharePoint because they don't have equivalents, such as linked Reports and batching. These changes will necessitate either rewriting your RS code to bent to the SharePoint integration mode (assuming you want to keep everything in SharePoint) or maintaining two report catalogs (one in the "classic" RS mode and a second for SharePoint integration). Of course, the later scenario would require an additional effort to synchronize both catalogs.

Finally, despite that extensibility has been an RS hallmark since its first release, the SharePoint web parts are not extensible. For example, as with the Report Manager, developers won't be able to change the parameter prompts, e.g. to implement more advanced parameter validation or replace them with custom controls. In short, if your requirement go beyond the built-in feature set, you don't have another choice but to abandon the Microsoft SharePoint implementation altogether and do it yourself.

Since I'd prefer centralizing the report management into a single report catalog whenever possible (as opposed to having multiple catalogs for each integration scenario), I couldn't help it but wish for a "semi-tight" integration where the SharePoint web parts just act as a thin presentation layer to the Report Server similar to the Report Manager. But again, I am not an expert in SharePoint and don't know what compromises this approach would have required or how difficult it could have been to achieve the SharePoint look and feel if the report definitions are not stored in SharePoint.Not to mention that the backward compatability issue I listed above may not be a problem if your requirements call for having two separate sets of reports anyway – one for application consumption (operational reports) and second for a SharePoint dashboard (management reports). In this case, having two distinct report catalogs may be preferable.

Two very useful features of Reporting Services (version 2000 and above) are snapshot execution and report history. Snapshot execution allows you to cache the report execution. Optionally, you can keep the cached reports in history, e.g. if the user wants to keep a copy of the report for historical analysis.

Once a report is enabled for snapshot execution, you can generate the history snapshot by invoking the CreateReportHistorySnapshot API assuming you need to do so programmatically, e.g.:

historyID = managementProxy.CreateReportHistorySnapshot(reportPath, out warnings);

However, if your code just calls CreateReportHistorySnapshot and the end user requests the report by clicking on the report link in the Report Manager or SharePoint (if the server is configured for SPS integration with SQL Server 2005 SP2), the user will get:

The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available. (rsReportNotReady)

This error is generated despite the fact that the report has history runs generated already. What’s going on? Why doesn’t the report server returns the last history run automatically? It turns out that the report snapshot and history runs are kept in two different places in the report catalog and are treated differently. To avoid this error change your code as follows:

managementProxy.UpdateReportExecutionSnapshot(reportPath);

ReportHistorySnapshot[] history = managementProxy.ListReportHistory(reportPath);

historyID = history[history.Length – 1].HistoryID;

The first line of code generates the report execution snapshot so clicking on the report link in the Report Manager would show the last snapshot. Assuming the report history for the report is enabled and “Store all report execution snapshots in history” option is enabled, this will also copy the report snapshot to the report history. The second line retrieves the definitions of all report history runs. Finally, the third line gets the last history identifier to return to the client if this is what the client is expecting.