Tag Archive for: Reporting Services

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.

One of the most useful features of the SSRS 2005 Report Builder is the ability to generate reports on the fly. As long as there is a navigational path in the model, the end user can click on a drillthrough-enabled attribute to see more details. This feature is officially called infinite drillthrough.

Sometimes, the automatic drillthrough reports may get in the way. For example, there are performance implications when this feature is used with UDM as a data source. Or, you may need the drillthrough report to bring data from a different data source than the one the Report Builder model is built upon. To meet this requirement, you can wire up a custom drillthrough report. For example, let’s assume that your model has Customers and Accounts entities where a customer can have one or more accounts. You need to define your own drillthrough report that shows the customer’s account details when the end user clicks on the customer entity.

As a first step, you need to create the drillthrough report. If the report will fetch data from the model data source, use the Report Builder to author the report. Once the report is ready, go to the Report->Report Properties menu and select the “Allow the users to drill to this report from other reports” checkbox. This causes the Report Builder to add two input parameters (DrillthroughSourceQuery and DrilthroughContext) to the report. The Report Server will pass the semantic query and execution context to these parameters at runtime (more on this in a moment). Next, save the report to the Report Server. Start SQL Server Management Studio and open up the model properties. Select the Drillthrogh reports page (see attached screenshot) and set the Single Instance or Multiple Instance properties to point to the custom drillthrough report(s) you’ve created.

But what f you want your custom drillthrough report to fetch data from a different data source? Perhaps, your model is based on UDM but you need to display data from the relational database? Fear not, you have a complete control over the drillthrough report as long as it has DrillthroughSourceQuery (you don’t really need this one but it must be present) and DrillthroughContext string parameters. You will most likely need the identifier of the clicked entity in the Report Builder so you can filter data in the drillthrough report. The identifier is passed in XML format under the DrillthroughContext parameter, e.g.:

<DrillthroughContext xmlns=”http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling”>

…

<GroupingValues>

<GroupingValue Name=”Customer”>ABtbQ3VzdG9tZXJdLltDdXN0b21lcl0uJlsyNV0=</GroupingValue>

</GroupingValues>

</DrillthroughContext>

As you can see, the customer identifier is Base64-encoded, so it will take some extra effort to extract and decode it. You can write a simple custom function to load the DrillthroughContext XML fragment in XML DOM and navigate to //GroupingValue[@Name=’Customer’] (watch out for the default namespace!). Then, you can use System.Text.UTF8.GetString(System.Convert.FromBase64String()) to decode the value. For example, in our case, the identifier may look like [Customer].[Customer].[Customer].&[134] (recall that we target UDM as a data source). Finally, you need to parse this string to get to the 134 nugget of truth. Once it is yours, the rest is easy.

You will probably never run into this issue unless you have very wide tables but here it is. If use the RS table region and the table has many columns and groups, the number of textboxes in the table cell can increase dramatically and exceed the maximum limit. This situation manifests itself with a rather cryptic error message upon uploading the report definition:

[BC30494] Line is too long

What's going on? SSRS compiles the report definition to prepare the report intermediate language which gets saved in the report catalog. If the N*M (where N is the number of textboxes in a table and M is the size of the textbox name) exceed 64K, the VB.NET compiler throws this error because a VB.NET source file cannot exceed 64K. The workaround is trivial:

1. Reduce the number of textboxes in a table by removing columns and/or groups.
2. Shorten the textbox names, e.g. instead of textbox1 rename to t1.

No one likes long lines…