Monday, January 5, 2009

How to Send SSRS Report as Email Attachment.

 

SSRS -SQL Server Reporting Server 

SQL Server Reporting Services provides access to the full functionality via  Report Server Web service. The Web service uses Simple Object Access Protocol (SOAP) over HTTP and acts as a communications interface between client programs and the report server.

Here is the URL  to access Reporting Server Web Service.

http://<YourServerName>/reportserver/reportservice.asmx

 

when your browse the  Reporting Server Web Service you get the  List of Methods which exposed.

image

Using this Reporting Service WebMethod  i am going to call reports and Send as PDF format as Email Attachment.

 

Architecture Diagram - Accessing  SSRS Web Service.

image

Accessing Report with Parameters and Saving as PDF format.

public static  bool GetReportasPDF(string reportParameterName, string reportParameterValue,string reportPath)
       {

 

string showHideToggle = null;
string encoding;
string mimeType;

// Read Report server Credentials 


           string userId = ConfigurationManager.AppSettings.Get("ReportServerUser");
           string password = ConfigurationManager.AppSettings.Get("RportServerPassWord");
           string domain = ConfigurationManager.AppSettings.Get("ReportServerDomain");
           string reportFormat = ConfigurationManager.AppSettings.Get("ReportFormatType");

 

// Create Reporting Server Web Service Proxy class.

           ReportingService rs = new ReportingService();
           //NetworkCredential nwc = new NetworkCredential("Sreeni", "Gandhi", "INDIA");

 

// Set the Credentials

           NetworkCredential nwc = new NetworkCredential(userId.Trim (), password.Trim (), domain.Trim ());
           rs.Credentials = nwc;

          // Set the Report Render arguments.


           byte[] result = null;
           //string reportPath = reportPath ;
           string format = "PDF";
           string historyID = null;
           string devInfo = null;

           // Prepare report parameter.


           ParameterValue[] parameters = new ParameterValue[1];
           parameters[0] = new ParameterValue();
           parameters[0].Name = reportParameterName.Trim();
           parameters[0].Value = reportParameterValue.Trim();
           DataSourceCredentials[] credentials = null;
           Warning[] warnings = null;
           ParameterValue[] reportHistoryParameters = null;
           string[] streamIDs = null;
           SessionHeader sh = new SessionHeader();
           rs.SessionHeaderValue = sh;
           try
           {
               result = rs.Render(reportPath, format, historyID, "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>", parameters, credentials,
                   showHideToggle, out encoding, out mimeType, out reportHistoryParameters, out warnings,
                   out streamIDs);
           }
           catch (SoapException e)
           {
               Console.WriteLine(e.Detail.OuterXml);
               StreamWriter sw = new StreamWriter("C:\\ReportServerError.XML");
               sw.Write(e.Detail.OuterXml);
               sw.Flush();
               sw.Close();
               return false;
           }


           // Write the contents of the report to an PDF file.


           try
           {
               FileStream stream = File.Create(@"c:\Reports\"+reportParameterValue.Trim()+".PDF", result.Length);
                stream.Write(result, 0, result.Length);
                stream.Close();
               return true;
           }
           catch (Exception e)
           {
               return false;
           }

       }

 

Sending EMAIL  with Attachment

Load the saved PDF file and send as email attachment

image

 

Thanks

SreenivasaRagavan.

5 comments:

Alex said...

I have seen a lot of good programs which recover emails. But I was on the Inet couple days ago and sighted an unusual software - restoring outlook express folders. It was unusual because of had a lot of curious functions and was free as I kept in mind. Besides I advised it my friend and one of them thanked me a present.

Mark Archer said...

That's a very cool way to do it through the web services API but a way that I think is much simpler and easier is to do this:

Dim reportURL As String = "http:///ReportServer/Pages/ReportViewer.aspx?&&rs:Format=pdf&rs:Command=Render"
'Create a webrequest to the ReportURL
Dim request As System.Net.WebRequest = System.Net.HttpWebRequest.Create(reportURL)
'Add permissions if needed
request.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
'Make a new attachment with the the stream from the response object
Dim atch As New System.Net.Mail.Attachment(request.GetResponse().GetResponseStream(), "Details.PDF")
'Add this attachment to an email object

Sorry for the bad formatting.

Ephrem said...

Many Thanks Mark, I used your solution and it works...it is much simpler than calling the ReportServer service to send a report as attachment.
When I use your solution as it is, I was getting an error with message "This stream does not support seek operations" but I found a solution in the website http://codemaverick.blogspot.com/2007/01/stream-does-not-support-seek-operations.html and it start working
I really appreciate!
Ephrem

Kor said...

Is it possible to use this with a windows service and have it pull report generation information from a table and email the report? I cannot use SSRS as I have 1 report that is much like a receipt for instance that needs to be emailed to our client.

nilesh said...

hey sorry if its dumb question but im new to Windows.

How do i use your solution in visual studio? When i paste and build the code it shows me errors..

can u explain me how to use?