Problem

You want to modify your reports with the WordApplication or ExcelApplication object after they’ve been populated with data by SQL Server Reporting Services. The ExcelApplication or WordApplication object’s Open method can’t pull a report from a report server, so how do you open a SSRS report with OfficeWriter?

Solution

For simplicity and ease of reading the code examples and text in this article refer to the ExcelApplication object exclusively. However, the exact same techniques can be used with the WordApplication object. The only strict requirement for opening reports from SSRS is for the Open method to have an overload that takes a Stream argument, which both objects have.

SQL Server Reporting Services exposes a web service that lets programs access and work with the reports on the server. The web service has a number of functions for interacting with a report server and in fact is the same API that the Report Manager tool is built on top of. One of its functions will let us retrieve a fully rendered report that we can then pass to the ExcelApplication object. Once the ExcelApplication object has opened the report, it can be used as if it were any other file.

Getting a Reference to the Report Server

There are a number of different ways to access a report server to manage your reports. The MSDN documentation has detailed instructions for all the different techniques. The most powerful way, and the one that the Report Manager application uses, is to use the report server’s web service, or SOAP API. The easiest way to use the web service is to add the report server as a web reference in Visual Studio. This will allow us to make web service calls to the report server as though it were any other object in a .NET project. I’ll provide a brief overview of the steps to add a report server as a web reference; details can be found at MSDN.

1. After opening the project you want to open a report in, select ‘Add Web Reference’ from the Project menu (or by right-clicking on the project name in the Solution Explorer).
2. Enter the URL of the report server in the text box. In most cases, this should be “http:///reportserver/reportservice.asmx”. Click ‘go’.
3. Visual Studio should find a single reference named ‘reportservice’. Enter a friendly name for the service in the text box on the right. For this example, I’ll use the name “MyReportService”.
4. Click ‘Add Reference’.

Creating a ReportingService Object

Adding the web reference in the last section created a special namespace that contains the object we’ll use to render the report. This namespace is .. In the example I used above this would be ReportingServiceRender.MyReportService. This namespace contains a ReportingService object that we’ll use to do the actual rendering of the report. To create a ReportingService object, use its default (empty) constructor. You can either fully qualify the class name or add a using (imports in VB.NET) statement to your code like I do in the sample below.

Note that while the default behavior is to add the report service in the project’s namespace, this is not always guaranteed, especially if the project name changes. If you add a web reference and cannot find it in the project namespace, right click on the web service in the Solution Explorer and select ‘View in Object Browser’. This will open the object browser and highlight the web service with its full namespace.

After creating the ReportingService object, we have to tell it how to authenticate itself to the report server. As long as the account your program is running as has permissions to render reports, you can use the default credentials here. If you need the code to run under a different set of credentials, you’ll need to create those at this step using the CredentialCache object.

 // Replace this name with whatever you named your reporting service instance using ReportingServiceRender.MyReportService; // ... Namespace and class declarations, etc ... ReportingService rs = new ReportingService(); rs.Credentials = System.Net.CredentialCache.DefaultCredentials; 

Calling the Render Function

The function we’ll use to get a copy of the populated report is ReportingService.Render. For our purposes, all we need are the first two arguments: the path of the report and the name of the rendering extension.

The path to a report always begins with a ‘/’, and does not include the ‘.rdl’ extension. For example, a report in the Home folder named “MyFancyReport” will have a path of “/MyFancyReport”. If we moved that same report to the “ExtraFancy” folder, the path would be “/ExtraFancy/MyFancyReport”. Note that all the slashes are forwards, as in a URL, instead of backwards, as in a Windows path.

The rendering extension is a string that tells Reporting Services what format to return the report in. By default, SSRS can render reports to a few simple formats including HTML, XML, PDF and basic, limited Word and Excel documents. To render a report as an Excel workbook with the SoftArtisans OfficeWriter renderer, use the “XLTemplate” extension. For Word documents, use the “WordTemplate” extension.

Because the last five arguments are output arguments in C#, we need to create variables to pass to the function instead of simply using null. Even though we don’t use the values in those variables, the variables must exist for the code to compile.

 // These variables are all the output variables the Render function takes. // We don't use them, but we need to create them to pass to the function. // Note that the ParameterValue array and the Warning array are members of // the ReportingServiceRender.MyReportService namespace we created earlier string optionalEncoding = null; string optionalMimeType = null; ParameterValue[] optionalParams = null; Warning[] optionalWarnings = null; string[] streamIDs; // The first argument tells Render where to find the report, the second // argument tells it what extension to use to render the report. string reportPath = "/ExtraFancy/MyFancyReport"; string reportFormat = "XLTemplate"; // The actual call to Render. The only important parameters are the first // two; the rest are either null or unused. byte[] result = rs.Render(reportPath, reportFormat, null, null, null, null, null, out optionalEncoding, out optionalMimeType, out optionalParams, out optionalWarnings, out streamIDs); 

Opening the Report in the Application Object

As you can see in the code snippet above, the Render function returns a byte array. The ExcelApplication object can’t open a byte array directly, but it can open a MemoryStream object (ExcelApplication, WordApplication. We can create a MemoryStream object by passing it a byte array in the constructor.

Once the Application object has opened the stream we can use it just like any other file, including streaming the file to users with one of the overloads of Save (ExcelApplication, WordApplication.

 // Turn the byte[] to a stream object that Open can read. MemoryStream reportStream = new MemoryStream(result); ExcelApplication xla = new ExcelApplication(); Workbook wb = xla.Open(reportStream); reportStream.Close(); // ... Modify the report with the Application object ... // Stream the rendered and modified report back to the user. xla.Save(wb, Page.Response, "RenderedReport.xls", false); 

Note that this code should be executed as part of a postback in a web form. By invoking this code through a web form you can create an interface that lets users request a report by clicking on a button or link, then stream that report back to them as though it were any other downloaded file.

Attached to this article is a zip file containing a Visual Studio 2008 project with this code in it. The project also contains a web form with a button that fires the code. Note that the web reference in the project assumes that the report server is hosted on the same machine that the code runs on, so it may need to be updated before the code will run. Also keep in mind that the project looks for a report named MyFancyReport in the /ExtraFancy folder. If you don’t have a report there, you’ll either need to create one or change the path in Default.aspx.cs.

Attachments

• SSRSPostProcessing.zip

Follow

Related posts: