I had a requirement to automate the generation of PDF files of SSRS reports.

Figured out a way to use 2 webservices exposed by SQL Server Reporting Services 2005:
a. ReportService2005
b. ReportExecution2005

Implementation:
a. Create SSRS reports and deploy it on the report server.
b. In the windows application add reference to the 2 SSRS webservices: ReportService2005, ReportExecution2005
c. Set default report credentials.
d. Set 2 webservies url
e. Load the SSRS report using ReportExecution2005
f. Get SSRS reportparameters using ReportService2005, to get the total parameters in the report and set the appropriate parameters value to render the report using ReportExecution2005. (confused !!!. Check the code below)
g. Set report parameters using
SetExecutionParameters() method of ReportExecution2005.
h. Render the report to get the PDF in byteArray format.
i. Write the pdf byteArray[] to the given pdf file using FileStream Class.



Source Code:

static private void CreatePDFReports()

{
//create proxy to the webservice
ReportService2005.ReportingService2005 rs2005 = new ReportService2005.ReportingService2005();
ReportExecution2005.ReportExecutionService re2005 = new ReportExecution2005.ReportExecutionService();

try
{
//authenticate to the web service using windows credentials
rs2005.Credentials = System.Net.CredentialCache.DefaultCredentials;
re2005.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs2005.Url = ConfigurationSettings.AppSettings["ReportService2005.ReportService2005"];
re2005.Url = ConfigurationSettings.AppSettings["ReportExecution2005.ReportExecution2005"];

//prepare render arguments
string historyID = null;
string deviceinfo = null;
string format = "PDF";
byte[] bytPDF;
string encoding = string.Empty;
string mimeType = string.Empty;
string extension = string.Empty;
ReportExecution2005.Warning[] warnings = null;
string[] streamIDs = null;

//define variables needed for GetParameters() method
//get the report name
string _reportname = ConfigurationSettings.AppSettings["reportName"];
string _historyID = null;
bool _forRendering = false;
ReportService2005.ParameterValue[] _values = null;
ReportService2005.DataSourceCredentials[] _credentials = null;
ReportService2005.ReportParameter[] _parameters = null;

//load the selected report.
ReportExecution2005.ExecutionInfo ei = re2005.LoadReport(_reportname, historyID);

//Get the no. of parameters in the report.
_parameters = rs2005.GetReportParameters(_reportname, _historyID, _forRendering, _values, _credentials);
int totalParams = _parameters.Length;

ReportExecution2005.ParameterValue[] parameters = null;

//prepare report parameters
parameters = new ReportExecution2005.ParameterValue[totalParams];

foreach (ReportService2005.ReportParameter rp in _parameters)
{
switch (rp.Name)
{
case "param1_name":
{
parameters[_parameters.Length - totalParams] = new scheduleReports.ReportExecution2005.ParameterValue();
parameters[_parameters.Length - totalParams].Name = rp.Name;
parameters[_parameters.Length - totalParams].Value = 1;
}
break;

case "param2_name":
{
parameters[_parameters.Length - totalParams] = new scheduleReports.ReportExecution2005.ParameterValue();
parameters[_parameters.Length - totalParams].Name = rp.Name;
parameters[_parameters.Length - totalParams].Value = 100;
}
break;
}
totalParams--;
}

re2005.SetExecutionParameters(parameters, "en-us");
bytPDF = re2005.Render(format, deviceinfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

FileStream fs = new FileStream("report.pdf", FileMode.OpenOrCreate, FileAccess.Write);
fs.Write(bytPDF, 0, bytPDF.Length);

fs.Close();
}

catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.Read();
}
}

3 comments

  1. sfinks_29 // February 8, 2011 at 7:36 PM  

    Any idea how to combine two reports into the one PDF?? I've been trying to do this, but not having any luck

  2. Anonymous // July 20, 2012 at 12:25 AM  

    Use pdf bundler assembly to combine pdf streams or pdf files.

    Thanks,
    Murali

  3. David Richardson // July 30, 2013 at 10:49 AM  

    I looks like you are running this in SQL Server as a CLR procedure?

    Is it possible to pass back the number of pages generated?

    Do you have the program for download?

    I don't know anything about Visual Studio, but need something like this...

    Thanks,

    David