Monday, February 1, 2010

How To: Execute and Save SSRS Report using C#

It is often required to execute a SSRS report from within C# and save it in the required file format like PDF, XLS etc.

SQL Server Reporting Services (SSRS) exposes two web services using which we can execute a report and save it in the required file format. The two web services are:

  • Reporting Service 2005 : http://[SERVER]/reportserver/reportservice2005.asmx
  • Report Execution Service : http://[SERVER]/reportserver/reportexecution2005.asmx

Add the above two web services as "Web Reference" to your project with the following namespace RS2005 and RE2005:



Use the C# code below to execute and save the report:

      public static void SaveReport()
{

RS2005.ReportingService2005 rs;
RE2005.ReportExecutionService rsExec;

// Create a new proxy to the web service
rs = new RS2005.ReportingService2005();
rsExec = new RE2005.ReportExecutionService();

// Authenticate to the Web service using Windows credentials
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;

rs.Url = "http://<SERVER>/reportserver/reportservice2005.asmx";
rsExec.Url = "http://<SERVER>/reportserver/reportexecution2005.asmx";

string historyID = null;
string deviceInfo = null;
string format = "EXCEL";
Byte[] results;
string encoding = String.Empty;
string mimeType = String.Empty;
string extension = String.Empty;
RE2005.Warning[] warnings = null;
string[] streamIDs = null;

// Path of the Report - XLS, PDF etc.
string fileName = @"c:\samplereport.xls";
// Name of the report - Please note this is not the RDL file.
string _reportName = @"/Marketing_Report";
string _historyID = null;
bool _forRendering = false;
RS2005.ParameterValue[] _values = null;
RS2005.DataSourceCredentials[] _credentials = null;
RS2005.ReportParameter[] _parameters = null;

try
{
_parameters = rs.GetReportParameters(_reportName, _historyID, _forRendering, _values, _credentials);
RE2005.ExecutionInfo ei = rsExec.LoadReport(_reportName, historyID);
RE2005.ParameterValue[] parameters = new RE2005.ParameterValue[1];

if (_parameters.Length > 0)
{
//parameters[0] = new RE2005.ParameterValue();
//parameters[0].Label = "";
//parameters[0].Name = "";
//parameters[0].Value = "";
}
rsExec.SetExecutionParameters(parameters, "en-us");

results = rsExec.Render(format, deviceInfo,
out extension, out encoding,
out mimeType, out warnings, out streamIDs);

using (FileStream stream = File.OpenWrite(fileName))
{
stream.Write(results, 0, results.Length);
}
}
catch (Exception ex)
{
throw ex;
}

}


Following output file formats are available: XML, CSV, IMAGE, PDF, EXCEL, WORD, HTML 4.0 and MHTML

Hope this helps!

47 comments:

dalvir said...

hi..
how can i show a report, deployed at SSRS report server, in web application using C#.
please help
Lucky

Sandeep Aparajit said...

You can get the report using the above code in HTML format and then render the same using ASP.NET application.

Jignesh said...

Hi Sandeep,
Which report path should i provide. The report is different server and i am developing on a different server.

So how do i modify this line
string _reportName = @"/Marketing_Report";

dalvir said...

Thanks sandeep..
My report is getting 3 parameteres. How can i set these parameters...

And have you any example code for this.

Sandeep Aparajit said...

Hi Jignesh,
We using the above C# code to execute SSRS report we have to provide the exact report name that appears on the ReportManager UI. We CANNOT provide the rdl file name instead. In the above example the @"/Marketing_Report", here Marketing_Report is the name of the report. If your report is places in a subdirectory, then give the name as:
@"Subdirectory/ReportName"

Sandeep Aparajit said...

Hi Dalvir,

You should know the parameters that you want to set. You can set them using the following code:

if (_parameters.Length > 0)
{
//parameters[0] = new RE2005.ParameterValue();
//parameters[0].Label = "";
//parameters[0].Name = "";
//parameters[0].Value = "";
}

Jignesh. said...

Dood you are just wonderfull...Thanks!!

dalvir said...

hi Sandeep.
I am using the following code to execute my report and to show it in Html form. But there is an error on rsExec.Render(......



"You have attempted to use a rendering extension that is either not registered for this report server or it is not supported in this edition of Reporting Services. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: You have attempted to use a rendering extension that is either not registered for this report server or it is not supported in this edition of Reporting Services."

My Code is [Code]
ReportService2005.ReportingService2005 rs;
ReportExecution2005.ReportExecutionService rsExec;
rs = new ReportService2005.ReportingService2005();
rsExec = new ReportExecution2005.ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.Url = "http://174.129.89.91/reportserver/reportservice2005.asmx";
rsExec.Url = "http://174.129.89.91/reportserver/reportexecution2005.asmx";
string historyID = null;
string deviceInfo = null;
string format = "HTML";
Byte[] results;
string encoding = String.Empty;
string mimeType = String.Empty;
string extension = String.Empty;
ReportExecution2005.Warning[] warnings = null;
string[] streamIDs = null;
string fileName = @"D:\test.html";
string _reportName = @"//Mydemo";
string _historyID = null;
bool _forRendering = false;
ReportService2005.ParameterValue[] _values = null;
ReportService2005.DataSourceCredentials[] _credentials = null;
ReportService2005.ReportParameter[] _parameters = null;
try
{
_parameters = rs.GetReportParameters(_reportName, _historyID, _forRendering, _values, _credentials);
ReportExecution2005.ExecutionInfo ei = rsExec.LoadReport(_reportName, historyID);
ReportExecution2005.ParameterValue[] parameters = new ReportExecution2005.ParameterValue[3];

if (_parameters.Length > 0)
{
parameters[0] = new ReportExecution2005.ParameterValue();
parameters[0].Label = "";
parameters[0].Name = _parameters[0].Name;
parameters[0].Value = "1";
parameters[1] = new ReportExecution2005.ParameterValue();
parameters[1].Label = "";
parameters[1].Name = _parameters[1].Name;
parameters[1].Value = Convert.ToString(DateTime.Now.AddDays(-7));
parameters[2] = new ReportExecution2005.ParameterValue();
parameters[2].Label = "";
parameters[2].Name = _parameters[2].Name;
parameters[2].Value = Convert.ToString(DateTime.Now);
}
rsExec.SetExecutionParameters(parameters, "en-us");
results = rsExec.Render(format, deviceInfo,
out extension, out encoding,
out mimeType, out warnings, out streamIDs);
using (FileStream stream = File.OpenWrite(fileName))
{
stream.Write(results, 0, results.Length);
}
}
catch (Exception ex)
{
throw ex;
}
[/Code]

dalvir said...

hi sandeep ..
I suceed in getting reports in html form . but there is a problem while using reportserver. I am using
System.Net.CredentialCache.DefaultCredentials
to set credentials.
But when report shows in browser then it asks for credetials.

how can i set creadential so that there is no need to set credentials.

I have also used
rs.Credentials =new System.Net.NetworkCredential("username", "password", "Domain")
but nothing happnes.
So should i do..
Please help.

Ankit said...

Hi Sandeep,

I want to generate the excel file on run time and then export the SSRS Report to it.Is it possible ?

Ankit said...

Hi Sandeep,
My webpage is timin gout after 45 settings during RENDER..is there an way to set timeout for render operation.

Thanks,
Ankit

dalvir said...

hi Sandeep.
I have drillthrough reports. And i am using reportserver web service to show them. Now there is a problem that when drillthrough event fire then it navigate to the report server directly.
I wants to show the child report as on my own page. Is there any way to handle drilldown reports.
Please help

Bhaskar Rao said...

Hi Sandeep,

Its help me a lot. Can you please tell me how to send the reports as attachment from Report Server?

Thanks
Bhaskar Rao.Thalatam

Tune Style said...

Hi Sandeep,

i want to loop the parameter. such as date parameter instead of assigning specific date i want to generate multiple date simultaneously. can you give some idea to me.

Here's the code:
parameters[0] = new re2005.ParameterValue();
parameters[0].Label = null;
parameters[0].Name = "STRRPTDATE";
parameters[0].Value = "18/09/2009";

Thanks
Alut

ab said...

thanks brother .I have some weakness for that .

thanks again.

webmaster forum

Anonymous said...

U r a genius :)

Regards
~A

stafford said...

What benefit is there using soap to call the report instead of simply accessing http://machine/ReportServer/Pages/ReportViewer.aspx?/path/to/report&rs:Command=Render&param1=value&param2=value&rs:Format=Pdf?

Cheers

عبداله بهمنی said...

Hi ...
my name is Abdullah Bahmani!!!!


I'm trying to write an application that help the RUP user to save they works and ...

I need to desing a form that users can create their shape and diagrams such as class digram and ...


I find a solution by visio activeX ... but I need to work Rational rose ActiveX but I dont Know how to do that ...

please help me to do this ...


I have to say you I work in c#

Hitesh said...

Hi Sandeep,
Is there any way we can add checkbox/Drop down in ssrs so that the end user can select what report he needs based on the selection and also basic report.

Anonymous said...

Sandeep,

An enormously helpful post - very well written and concise. Many, many thanks.

Craig

Anonymous said...

hi sandeep,
can we read direct data of report through web service?
also if i am not passing userid and password to webservice but can i use tokan and get authenticated..?

JFR said...

I am also stuck with this Error. could someone tell how to fix this Issue?

You have attempted to use a rendering extension that is either not registered for this report server or it is not supported in this edition of Reporting Services. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: You have attempted to use a rendering extension that is either not registered for this report server or it is not supported in this edition of Reporting Services."

Thanks,
JFR

Julian said...

I have added both web references to my project.
# Reporting Service 2005 : http://[SERVER]/reportserver/reportservice2005.asmx
# Report Execution Service : http://[SERVER]/reportserver/reportexecution2005.asmx

However I am getting the error:
The type or namespace name 'ReportingService2005'
The type or namespace name 'ReportExecutionService'
for both of the following lines:
RS2005.ReportingService2005 rs;
RE2005.ReportExecutionService rsExec;

stafford said...

As a follow up to my previous comment, the url has changed to the following for SQL 2008;

http://machine/Reportserver?/path/to/report&rs:Command=Render&param1=paramValue&rs:Format=Pdf

Anonymous said...

This was a great post and very clear and concise. My only additional question would be setting parameters to pass in and how that would be done. I am going to play around with the solution a little and look at some other posts and hopefully I can just figure it out. SSRS and SSIS being used by other programs is like a black art I have found. There is not much documentation and little agreement on what to do. For that reason this post was a diamond in the rough to find.

Anonymous said...

Sanjay,This is a really great article.I created a parameted script to run 180 reports.It takes only 10 minutes to render and export all 180 reports.Thanks for sharing your knowledge.The only limitation is report won't show filter names and values.Is it a limition? Please let me know is there a way to include the info in the report.Thanks in advance.Uma

Anonymous said...

Hi Sandeep,

Basically i am trying to get report for particular snapshotID that i have created. For that i have used the above code. I am able to successfully create the report in PDF format. But it is generated when i pass null value in _historyid and historyid variable. if i pass snapshot id in the history id then it gives following exception

System.Web.Services.Protocols.SoapException: The parameter value provided for 'snapshotID' does not match the parameter type. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ParameterTypeMismatchException: The parameter value provided for 'snapshotID' does not match the parameter type. ---> System.FormatException: String was not recognized as a valid DateTime.
at Microsoft.ReportingServices.WebServer.ReportExecution2005Impl.LoadReport(String Report, String HistoryID, ExecutionInfo2& executionInfo)
at Microsoft.ReportingServices.WebServer.ReportExecutionService.LoadReport(String Report, String HistoryID, ExecutionInfo& executionInfo)

Another thing i want to show the report in reportviewer how could i load this report in reportviewer.

Thanks in Advance

prudhvi said...

Hi Sandeep,
This is a great article. My only question I had my report server on a different server how can give the report path @"/Marketing_Report". I had to render pdf reports for a report which had subscriptions such as name, id and number. I need to pass 3 parameters in the script. Can you please help me passing the parameters?

Anonymous said...

Hi friends,
System.Web.Services.Protocols.SoapException: Server did not recognize the value of HTTP Header SOAPAction: Please tell how to rectify this error

Anonymous said...

Hi Sandeep,

Thanks for this great stuff. I am able to call my report and set single value parameters but I am not able to set multi value parameter from above code. Can you please help me on this?

Thanks in Advance

Anonymous said...

Something must have changed in SQL 2008; the only options available are
RS2005.ReportingService2005SoapClient rs;
RS2005.ReportingService2005Soap rs2;
RS2005.ReportingService2005SoapChannel rs3;
And none of them have the methods refer to.
Any idea what needs to be done?

Anonymous said...

Hi, Is it possible to auto-generate PDF say QUOTE report from CRM 2011 Online? If so, what are the steps. Thanks

Anonymous said...

hi

please can you give me ur mail id..

Anonymous said...

my mail id is indiradevit@clearpractice.com

please give me ur mail id

i got some problem

Anonymous said...

hi

i implemented this link.

but i got error.

“This report requires a default or user-defined value for the report parameter 'mgrid'. To run or subscribe to this report, you must provide a parameter value. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ReportParameterValueNotSetException: This report requires a default or user-defined value for the report parameter 'mgrid'. To run or subscribe to this report, you must provide a parameter value.”


mgrid is one parameter value.

please tel me where can i did mistake.

Anonymous said...

You are the Best !! Thanks a lot for great article

Anon said...

Wow just what i was looking for. Thanks

Anonymous said...

Hi Sandeep
I’m recreating some crystal reports in SSRS 2008 We use a web service to convert Crystal reports to PDF files. The Crystal Reports in web service is using a dll named CRAXDRT. I want to modify the web service to convert SSRS Reports to PDF.
As well I want to attach to SSRS report excel file and after that convert to PDF
Any suggestions

Thanks a lot

Anonymous said...

Hi,
i have developed a website in php, which create reports on daily basis, now i want to create and save that reports automatically according to date, like yesterday's report must be created today morning. can anyone help me that how i can i do this with c sharp?

Thanks in advance.

Vivek Bansal said...

Hi Sandeep,

I am currently exporting my reports in excel and pdf format (using sql 2008 reporting web service) and it's working fine but as soon as i try to export to word, it throws me the following error. Would you have any clue why is it doing that? Please help.

You have attempted to use a rendering extension that is either not registered for this report server or it is not supported in this edition of Reporting Services. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: You have attempted to use a rendering extension that is either not registered for this report server or it is not supported in this edition of Reporting Services.

Anonymous said...

Thanks Sandeep , you saved my day ,
below line saved my hours of efforts
-----------------------------------
// Name of the report - Please note this is not the RDL file. string _reportName = @"/Marketing_Report";
-------------------------------
Gourav from Mumbai

Ravikanth said...

Hi,Same code i was used in my project am getting error as "System.Web.Services.Protocols.SoapException: The item '/Report' cannot be found" at _reportName.i was given the _reportName as Report. what is "_reportName" , what type of value i can pass to _reportName.

Anonymous said...

thanks for the posting. there's a little ssrs security tweaking necessary. otherwise, it works like a charm.

David Bishop said...

Sandeep,

I like your code, but I can not get the parameters to work.

I get a SoapException, saying I need to set a value or a defaultvalue for my parameter.


Any thoughts on what I may have missed the code is passed below

Many Thanks

Dave
RE2005.ParameterValue[] parameters = new RE2005.ParameterValue[1];

if (_parameters.Length > 0)
{
parameters[0] = new RE2005.ParameterValue();
parameters[0].Label = "Packhouse";
parameters[0].Name = "Packhouse";
parameters[0].Value = "Line1";

}
rsExec.SetExecutionParameters(parameters, "en-us")

haritha said...

I want to run my clr procedure that trigger the ssrs report..the query for that report should be given in Clr proc and the rdl file which is generated should be save as pdf in database...this all process is a scheduled event.
need help?

Johan van den Brink said...

Wow! Many thanks for this piece of code. I was able to help my client with SQL Standard Edition to send 200 reports with different parameters to all their sales personnel!

Anonymous said...

Great Information, Tested and works perfect, Thanks a lot for the publication

Post a Comment