While working with SQL Server Reporting Services (SSRS), it can sometimes be useful to generate a report programmatically rather than retrieving it interactively. This would allow you to make the report available to users or keep it for further processing.
Let's take a look at how to use the Simple Object Access Protocol (SOAP) API methods in SSRS to generate reports and save them into files.
SQL Server Reporting Services is essentially implemented as a set of Web services, commonly referred to as SOAP API. SQL Server 2000 had a single Web service, and starting with SQL Server 2005 there are two endpoints.
The first one is ReportServer2005 and it contains methods related to management functions of SSRS. Don't let the 2005 suffix confuse you; it is used in SQL Server 2008 as well. The second endpoint is ReportExecution2005 and it contains methods used for report rendering. In order to use the endpoints from Visual Studio, you need to add a Web reference to the endpoints.
Below is a reference sheet showing the namespace, Web reference URL and sample declaration for each endpoint:
|rs = new ReportingService2005();|
|Declaration:||ReportExecutionService rsExec = new ReportExecutionService();|
Rendering a report programmatically is relatively simple. You can render a report by calling several methods in the ReportExecution2005 endpoint. First you need to initialize the report by calling the LoadReport() method:
string historyID = null;
string reportPath = "/Report Folder/My Report";
If your report contains parameters, you need to declare and populate an array of ParameterValue objects:
executionParams = new ParameterValue;
executionParams = new ParameterValue();
executionParams.Name = "Month";
executionParams.Value = "3"; new ParameterValue();
executionParams.Name = "Year";
executionParams.Value = "2009";
Once the parameters are set up, you call the SetExecutionParameters() method and pass them in:
At this point you are ready to call the Render() method. This method renders the report and returns it as a byte array that you can save into a file. You need to pass in several parameters. Many of these are optional and needed only for very specific requirements (detailed explanations of each parameter is beyond the scope of this article).
Here are the parameters and possible values:
string historyID = null;
string deviceInfo = null;
Warning warnings = font color="blue">null;
string streamIDs = null;
string format = "EXCEL";
The above format parameter needs to contain a string mapping to the desired output of the rendered file. Specifying "EXCEL" renders the report as a Microsoft Excel file. The other possible values are:
- "MHTML" for a web archive format (mht file)
- "CSV" for a comma-delimited file
- "IMAGE", "XML"
- "WORD" for Microsoft Word format
Now that everything has been set up, you can declare a byte array object, call the Render() method and save the results to a file:
Byte results = rsExec.Render(format,
deviceInfo, out extension,
out mimeType, out encoding,
out warnings, out streamIDs);
FileStream stream = File.OpenWrite(fileName);
stream.Write(results, 0, results.Length);
Rendering reports programmatically can be useful in many business scenarios. While the Report Manager application in SSRS allows you to export reports, imagine having a report that has five parameters, each with several possible values, and you need to manually export all possible combinations and save them as Excel files on the network.
The code I showed you above, with some simple additions, would allow you to develop an automated solution that could be scheduled to execute whenever needed. Other potential uses are automated report archiving, or forcing reports to be loaded into execution cache as I described in my article on speeding up SSRS reports with caching.
Another possibility involves generating reports from the command prompt. SQL Server Reporting Services contains a scripting environment called RSS which allows you to call the SOAP API from a RSS text file. It also allows you to pass in parameters as needed.
While the main purpose of RSS scripting is to automate report management, it can also be used for report rendering. RSS only supports VB.NET, so that is what the following code is in. This shows you how to code the Main() method (a required entry point for RSS) to connect to the ReportExecution2005 endpoint, render a report into Excel, and save it to a local drive:
Public Sub Main()
Dim format as string = "EXCEL"
Dim fileName as String = "C:\temp\reports\Product Line Sales.xls"
Dim reportPath as String = "/AdventureWorks 2008 Sample Reports/Product Line Sales 2008"
' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim extension as string = Nothing
Dim encoding as string
Dim mimeType as string
Dim warnings() AS Warning = Nothing
Dim streamIDs() as string = Nothing
Dim results() as Byte
results = rs.Render(format, deviceInfo, extension, _
mimeType, encoding, warnings, streamIDs)
' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
After you save the above code into a file (let's call it "Render Report.rss" as as example), you can execute it from DOS by starting the rs executable that's supplied with SSRS:
rs -i "c:\temp\reports\Render Report.rss" -s "http://servername/ReportServerName" -e Exec2005
Please note that the "-e" parameter with "Exec2005" value is required, otherwise you will connect to the management endpoint and get an error because the Render() method is in the execution endpoint. Also note that unlike in the .NET code, the rs variable in RSS is not declared, the RSS scripting host automatically sets it up as an endpoint.
ABOUT THE AUTHOR
Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He
specializes in SQL Server development, database performance tuning, ADO.NET and writing database
tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach
conferences in Canada and is the president of the Vermont SQL Server User Group.
This was first published in December 2009