Programming report generation with SQL Server Reporting Services 2008
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
Dig Deeper
-
People who read this also read...
-
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in December 2009
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:
ReportServer2005:
Namespace: |
Microsoft.SqlServer.ReportingServices.ReportService2005 |
URL: |
http://servername/ReportServerName/ReportService2005.asmx?wsdl |
Declaration: |
ReportingService2005 rs = new ReportingService2005(); |
ReportExecution2005:
Namespace: |
Microsoft.SqlServer.ReportingServices.ReportExecutionService |
URL: |
http://servername/ReportServerName/ReportExecution2005.asmx?wsdl |
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";
rsExec.LoadReport(reportPath, historyID);
If your report contains parameters, you need to declare and populate an array of ParameterValue
objects:
ParameterValue[] executionParams;
executionParams = new ParameterValue[2];
executionParams[0] = new ParameterValue();
executionParams[0].Name = "Month";
executionParams[0].Value = "3"; new
ParameterValue();
executionParams[1].Name = "Year";
executionParams[1].Value = "2009";
Once the parameters are set up, you call the SetExecutionParameters() method and pass them
in:
rsExec.SetExecutionParameters(executionParams, "en-us");
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;
string extension;
string encoding;
string mimeType;
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)
- "PDF"
- "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);
stream.Close();
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
rs.LoadReport(reportPath, historyID)
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)
stream.Close()
End Sub
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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.