Reporting Services

Deliver SQL Server Reports To SharePoint To Enhance Team Collaboration

Ed Hild

Code download available at:ReportingServices.exe(204 KB)

Contents

Architecture
Building the Web Service
Building the Delivery Extension
Deployment
SQL Server 2000 and SQL Server 2005
Conclusion

Imagine you work for a company that has a critical database application that continually records new incoming information. This database could be storing order information, sales contacts, or just about any other kind of data. Now consider that a group of individuals within your company rely on reports about this data—perhaps trend analysis of monthly billing statements—for making key business decisions. These reports are created on a schedule: daily, weekly, or monthly. Sound familiar? Chances are your company has similar systems in place. Just about any type of company can relate to this.

Microsoft® SQL Server™ Reporting Services simplifies this scenario by allowing users to subscribe to a report and have it automatically delivered to them. But while this is a useful feature, it only addresses one aspect of the larger objective. Once users receive a report, they'll likely want to do more than just read through the data. Perhaps they'll need to create and assign tasks to employees, or maybe they will want to allow group members to have a constructive discussion about the data, or they may simply need to incorporate the data into a Microsoft PowerPoint® presentation.

The problem is that the system delivers a copy of the report to each individual by e-mail or sticking it on an internal company file share—the two out-of-the-box delivery options for subscriptions. Neither case supports collaboration regarding the report within the group. A more interesting solution would be to deliver the report to a Microsoft SharePoint® document library where discussion and collaboration features are readily available to be used with the report. This article details how to create a custom SQL Server Reporting Services delivery extension that will store the report in a Windows® SharePoint Services (WSS) document library.

Architecture

In the scenario I've described, the time of the report is critical. The report must be identical for all users regardless of when they view it. Therefore, I cannot rely on a model where each user runs the report on demand when they happen to visit the SharePoint site. (An on-demand solution could easily be accomplished using the new report viewer web part in SQL Server 2000 Reporting Services Service Pack 2, however the collaboration features of SharePoint would not be applicable.) Instead, the custom delivery extension must run the report at a scheduled time and deliver it in a completely automated fashion. Since SharePoint is capable of storing documents, my delivery extension will render the report as a PDF file and store it in a designated document library.

So how does the file, once it's been generated, make its way to the document library? The solution includes a custom SharePoint Web service that provides an interface to accept the report file and, through the WSS object model, stores the file in the appropriate library. Using a custom Web service adds flexibility, letting you include additional tasks, other than simply storing the file.

To facilitate the inclusion of a binary file in the request, the Web service will use the Web Service Enhancements (WSE) Direct Internet Message Encapsulation (DIME) with WS-Attachment functionality. I selected this method over base64 encoding of the binary data within the XML message since, in a Unicode character format, the base64 encoding method inflates the size of the request. Sending attachments with DIME moves the file outside of the SOAP envelope. Figure 1 shows this architecture at a high level.

Figure 1** Solution Architecture Overview **

The beauty of this architecture is that the end result is just a file in a document library. Users can, therefore, use all of the regular functionality that SharePoint has to offer, including collaboration and SharePoint alerts. If someone needs to confirm a report is valid and sign off on it before other users are able to view it, she can turn on content approval. If the content of the reports must be searchable, simply install a PDF IFilter.

Building the Web Service

The custom SharePoint Web service has just one method, UploadDocument. This method only accepts SOAP Web service requests that have a file attachment. To receive a set of parameters from the client, the Web service exposes a DocInfo class that has been specifically designed to provide flexibility as well as version resiliency (see Figure 2).

Figure 2 DocInfo Class

<XmlType(Namespace:="https://sample.microsoft.com/DocumentUtil")> _ Public Class DocInfo ' Fields Public FileName As String Public Folder As String Public Version As String<XmlAnyElement()> _ Public UnknownElements As XmlElement() <XmlAnyAttributeAttribute()> _ Public UnknownAttributes As XmlAttribute() End Class

At a minimum, this Web service must receive two strings: the names of the file and the library. However, the solution must remain flexible with the ability to support additional parameters in the future. In fact, this Web service is likely to add capabilities to your SharePoint implementation that go well beyond merely storing reports. For more information on building a class so that its serialization is open-ended, see Doug Purdy, a Program Manager at Microsoft, discuss this topic on the MSDN® TV episode "Loosely Coupled Web Services".

The element and attribute arrays in the DocInfo class allow me to add fields in the future without breaking backwards compatibility. Meanwhile, the version identifier enables the Web service method to discover what level of client is making the request so that it can act accordingly.

Once the UploadDocument Web service method has confirmed that the request has an attachment, it uses a BinaryReader to read the attachment into a byte array (see Figure 3). In the sample code (available in both C# and Visual Basic in the download), you will see the use of RequestSoapContext. This class provides access to the WSE-specific extensions of the Web service. Through its Attachments collection, you can access the actual PDF report file that the delivery extension is sending. With the attached report received, the UploadDocument method continues its processing using the SharePoint object model to determine which "web" the client is targeting:

Dim site As SPWeb = SPControl.GetContextWeb(Me.Context)

Figure 3 Accessing the Attached Report

Public Sub UploadDocument(ByVal info As DocInfo) 'Reject any requests which are not valid SOAP requests If (RequestSoapContext.Current Is Nothing) Then Throw NewApplicationException( _ "Only SOAP requests are permitted.") End If If (RequestSoapContext.Current.Attachments.Count = 0) Then Throw New ApplicationException( _ "Noattachments were sent with the message.") End If Dim reader As BinaryReader Try Dim fileData As Byte() = Nothing 'retrieve the attachment reader = New BinaryReader( _RequestSoapContext.Current.Attachments(0).Stream) fileData = reader.ReadBytes(CType(reader.BaseStream.Length, _ Integer)) ...

In this context, "web" is a generic SharePoint object model term. It can refer to a Windows SharePoint Services (WSS) site or a SharePoint Portal Server (SPS) area. The correct web is determined using the SharePoint context object, which is based on the URL used by the client to make the request. The SharePoint site's Web services are deployed in such a way that they can be referenced through the vti_bin folder of any web. To gain this functionality you must make sure to deploy your Web service in the same manner as the out-of-the-box services. I explore this topic in more detail in the deployment section of this article.

Once the UploadDocument method determines the web in which the report should be stored, the GetFolder method is used to retrieve the correct document library. The library then adds the byte array to its files collection using the given file name:

Dim folder As SPFolder = site.GetFolder(info.Folder) Dim file As SPFile = folder.Files.Add(info.FileName, fileData, True)

This process saves the report to the SharePoint document library. The True argument specified in this call specifies that the current file will overwrite any existing file with the same name. If the user has enabled versioning on the SharePoint library, the new file will actually update the existing file. As described later in this article, the delivery extension also has an option to append a timestamp into the file name. This is handy if a user does not want to enable versioning of the library, but would still like to keep all the report instances.

Building the Delivery Extension

The out-of-the-box delivery options I mentioned earlier, delivery via e-mail and delivering the file to a file share, each constitute a delivery extension. To get the report to a SharePoint library, I must build a custom delivery extension. This involves coding three classes: one that stores the subscription settings, one that renders the user interface, and one that actually delivers the report. When a user sets up a subscription to deliver a report to SharePoint, they need to provide four pieces of information: the URL of the WSS site or SPS area, the name of the document library, the file name to use for the report, and whether a timestamp should be added to the file name. Each of these values is stored in a SubscriptionData class.

The solution also includes an SPSLibraryDeliveryUIProvider class that generates the user interface used to capture this information from the user. This class renders the forms fields that are specific to the SharePoint library delivery extension. Figure 4 shows the subscription entry form for the custom delivery extension. The SPSLibraryDeliveryUIProvider is only responsible for the three textboxes and one dropdown list that match these settings.

Figure 4 Setting Up a Subscription

Most of the work of a delivery extension involves coding the class that actually delivers the report. The class I created is named SPSLibraryDeliveryProvider. It implements the IExtension and IDeliveryExtension interfaces. This class is automatically instantiated by the Reporting Services engine when a report is ready to be delivered and the user has selected my custom solution as the method of delivery.

In the class, the Deliver method is where the action begins. This method receives a notification parameter that is used to retrieve the user-specific settings for delivery as well as to communicate progress or failure to the host service. The Deliver method, in turn, calls SaveReport, which is where the delivery extension calls the custom SharePoint Web service. SaveReport begins by rendering the report into a PDF file (see Figure 5).

Figure 5 SaveReport Implementation

Private Sub SaveReport(ByVal notificationObj As Notification, _ ByVal data As SubscriptionData) Dim files() As _ Microsoft.ReportingServices.Interfaces.RenderedOutputFile = _notificationObj.Report.Render("PDF", "<DeviceInfo/>") If (files(0).Data.Length > 0) Then ' WEBSERVICEREF is a literal string constant ' whose value is "/_vti_bin/DocumentUtil.asmx"Dim serviceProxy As DocumentUtilService.DocumentUtilWse = _ New DocumentUtilService.DocumentUtilWse serviceProxy.Url = data.siteUrl + Me.WEBSERVICEREF ' establishendpoint for wse addressing Dim virtualServerUrl As String = CreateVSUrl(data.siteUrl) Dim dest As EndpointReference = New EndpointReference( _ New Uri(virtualServerUrl +Me.WEBSERVICEREF)) serviceProxy.RequestSoapContext.Addressing.Destination = dest 'credentials serviceProxy.Credentials = _ System.Net.CredentialCache.DefaultCredentials'build delivery Info Dim info As DocumentUtilService.DocInfo = _ New DocumentUtilService.DocInfo info.FileName = GenerateFileName(data) info.Folder = data.libraryName ' build theattachment Dim attachment As DimeAttachment = _ New DimeAttachment("application/pdf", _ TypeFormat.MediaType, files(0).Data)serviceProxy.RequestSoapContext.Attachments.Add(attachment) 'deliver serviceProxy.UploadDocument(info) End If End Sub

The next section of the SaveReport method configures the proxy class that Visual Studio® created when a Web reference was added to the project. In the code, the proxy class is DocumentUtilService.DocumentUtilWse. To initialize the proxy, I must set its Url property to the URL provided by the user, but with /_vti_bin/DocumentUtil.asmx appended. This will ensure that the SharePoint context is properly established for the code within the Web service method. Since SharePoint actually modifies the requests that come into a site's _vti_bin directories and routes them to the _vti_bin at the root of the virtual server, the proxy must be configured with a destination endpoint reference. By setting the destination endpoint reference, the resulting request will pass the validation enforced by the WS-Addressing logic within WSE. The URL of the endpoint reference is constructed using the CreateVSUrl function, which determines the URL of the virtual server from the URL of the site that the user provided.

Lastly, the proxy's security credentials are set. The Web service call will be made with the security context of the account that was assigned to the ReportServer service. However, this is done only for the example. Microsoft does not recommend running the service with elevated privileges, as it can open your network to elevation of privilege attacks. Rather, you should gather specific credentials in the delivery extension settings and use those to make the requests.

The last section of the SaveReport method actually populates the request with data. A DocInfo class is created and filled with the file name of the report and the name of the library in which it should be stored. The GenerateFileName function called here takes the user specified file name and adds the .pdf file extension and, optionally, the chosen timestamp type. The binary representation of the report is attached to the request using the RequestSoapContext.Attachments collection. Finally, the delivery extension sends its information to the custom SharePoint Web service.

Deployment

Key deployment items are described in more detail in the instructions that come packaged with the sample code, but are noted here to ensure that you will know to keep them in mind.

The ReportServer Windows service that is installed by SQL Server Reporting Services should be configured with an Active Directory® domain account. By assigning it a domain account, administrators will be able to assign the delivery extension rights to specific SharePoint libraries.

The custom SharePoint Web service must be deployed as described in the MSDN article, "Writing Custom Web Services for SharePoint Products and Technologies". This involves creating .aspx files from the .wsdl and .disco files and placing them in the C:\Program Files\Common Files\Microsoft Shared\Web server extensions\60\ISAPI folder.

The delivery extension assembly must be deployed to the ReportManager and ReportServer directories of SQL Server Reporting Services. The delivery extension must also be added to the RSWebApplication and RSReportServer configuration files. Lastly, there are code access security (CAS) configurations that need to be addressed in the Rssrvpolicy and Rsmgrpolicy files. For detailed information on this, see the MSDN article "Code Access Security in SQL Server 2000 Reporting Services".

SQL Server 2000 and SQL Server 2005

I originally wrote the solution described in this article using SQL Server 2000. The good news is that for SQL Server 2005, the interfaces on which I'm relying remain unchanged and, with the exception of an upgrade/recompile of the delivery extension project, everything went fine.

For SQL Server 2005, the Reporting Services directory is located at C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services (if you install SQL Server, Analysis Services, and Reporting Services together). Follow the same instructions for modifying the configuration and policy files while placing the upgraded SPSLibraryDelivery.dll in the bin folders.

For my test, I also installed Windows SharePoint Services Service Pack 1, extended a new IIS Web site, and created a top-level team site. The deployment instructions for the custom Web service remained unchanged.

Conclusion

There are many different methods of generating reports to provide users with critical information. The challenge for most organizations is not producing such reports, but rather delivering them in a timely and effective manner. With the custom delivery extension described here, reports can be captured and placed within SharePoint document libraries, allowing users to view and analyze the reports in a more robust manner that supports collaboration and other useful features.

Ed Hild is a technology architect at the Microsoft Technology Center in Virginia. He delivers architecture design sessions, proofs of concept, and strategy briefings to customers with emphasis on the Microsoft collaboration platform. He can be reached at edhild@microsoft.com or visit his blog at blogs.msdn.com/edhild.