Using OfficeWriter‘s integration with SSRS in conjunction with the Designer is typically a straightforward process with no programmatic manipulation of the reports. A developer designs the report in Visual Studio BIDS, opens the .rdl using the Designer, designs the template in Word/Excel, and publishes the report. The report is then rendered inside the Report Manager using the custom OfficeWriter export option. However, there are times that situations call for post-processing the report programmatically and that’s where the ExcelApplication and WordApplication objects come in. Accessing and rendering the reports through the SSRS API is straightforward and the resulting byte array can be turned into a MemoryStream and passed to OfficeWriter.

Adding the SSRS Web Service

The first step necessary to tapping into the SSRS API is to add the Report Execution Service to your web references inside of Visual Studio. The URL for the web service is likely along the lines of *http://localhost/reportserver/reportexecution2005.asmx*, where localhost/reportserver is the hostname and virtual directory of the SSRS server. Note that this is for SQL Server 2008, despite the 2005. This web service is located in the directory C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer in the example instance I am using.

Instantiating the web service and loading the report

This segment is straightfoward. First instantiate the ReportExecutionService object and set the credentials that will be used to authenticate against the Report Server. In this case, I just passed my current profile’s Windows credentials. LoadReport is then called with the following parameters:

• historyID – ID of a report history snapshot, ie. 2011-11-15T12:23:18 (more)

• reportPath – path to the desired report as seen in the Report Manager. ‘/’ is necessary and represents the root

Set report parameters

Set render parameters

The first parameter is necessary to specify the renderer that SSRS will use to generate the report. Typically this is set to ‘Excel’ or ‘Word’ but to use the custom OfficeWriter renderer, we use ‘WordTemplate’ or ‘ExcelTemplate.’ The other parameters are completely optional but need to exist to be passed to the Render method. You can read more about them here.

Render the report and pass it to OfficeWriter for processing

The final step is calling Render() which will return a byte array containing the output report’s data. To be passed to OfficeWriter, the byte array needs to be turned into a MemoryStream by passing it to the MemoryStream’s constructor. The stream can then be passed to the Application object’s Open method so it can be manipulated. The final product is produced during Save where the output Word or Excel document is streamed to the user.

OR with ExcelWriter:

Follow

Related posts: