SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Tag Archives: Email

Render and Email a Reporting Services Report using .Net

In an earlier post, I had described a customer scenario, where they would want to execute and render a report from a .Net application, and then send the rendered report as an email attachment.

In the previous post, I had talked about how to generate a Tree View of the Report Server Items. In this post, I would be talking mainly about the how to execute a report and render it in a required format. And then send the Report as an email attachment.

The Post, assumes the following

  1. The report which needs to be executed is selected on the Tree View and the report path is passed to the function.
  2. The Report runs with default parameters.
  3. The SMTP mail server is preconfigured.

In order to execute a Report, we need to use the ReportExecutionService() Class, defined in the ReportExecution2005 endpoint for Reporting Services.

I created a new class ExecuteAndRender for this functionality.

The ExecuteAndRender Class, defines and object for the ReportExecutionService() class, in order to execute the methods exposed by Reporting Services.

   1: public ExecuteAndRender()

   2:         {

   3:              rs = new ReportExecutionService();

   4:              rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

   5:              rs.Url = "http://localhost/reportserver/ReportExecution2005.asmx";

   6:         }

Next I created a function RenderReport(string ReportName), this function performance the following tasks

  1. Define the Report Rendering and execution settings.
  2. Calls the ReportExecutionService.LoadReport() to load the report definition.
  3. Defines the report parameters. In this case, since all default values would be used, we do not explicitly define the parameters.
  4. Calls the ReportExecutionService.Render() method to render the report in the desired format.
  5. One the report have been rendered, we create a PDF file for the report.
  6. Then call the AttachAndSendEmail() to send the email.
   1: public void RenderReport (string ReportName)

   2:         {

   3:             byte[] result = null;

   4:             //string reportPath = "/ReportFolder2";

   5:             string format = "PDF";

   6:             string historyID = null;

   7:             string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

   8:  

   9:             //DataSourceCredentials[] credentials = null;

  10:             //string showHideToggle = null;

  11:             string encoding;

  12:             string mimeType;

  13:             string extension;

  14:             Warning[] warnings = null;

  15:             

  16:             //ParameterValue[] reportHistoryParameters = null;

  17:             string[] streamIDs = null;

  18:  

  19:             ExecutionInfo execInfo = new ExecutionInfo();

  20:             ExecutionHeader execHeader = new ExecutionHeader();

  21:  

  22:             rs.ExecutionHeaderValue = execHeader;

  23:             execInfo = rs.LoadReport(ReportName, historyID);

  24:  

  25:             //ReportParameter[] param = execInfo.Parameters;

  26:  

  27:             // Prepare report parameter. Currently the report executes with Default paramerts.

  28:             // Here we retrieve the 

  29:             // But if required the Report Paramaeters can be added.

  30:             

  31:             ParameterValue[] parameters = new ParameterValue[execInfo.Parameters.Length];

  32:  

  33:             rs.SetExecutionParameters(parameters,"en-us");

  34:             String SessionId = rs.ExecutionHeaderValue.ExecutionID;

  35:  

  36:             try

  37:             {

  38:                 result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

  39:                 execInfo = rs.GetExecutionInfo();

  40:             }

  41:             catch (SoapException e)

  42:             {

  43:                 throw e;

  44:             }

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

  46:             try

  47:             {

  48:                 FileStream stream = File.Create("report.pdf",result.Length);

  49:                 stream.Write(result, 0, result.Length);

  50:                 stream.Close();

  51:                 ///Now Send the email

  52:                 AttachAndSendEmail("report.pdf");

  53:             }

  54:             catch (Exception e)

  55:             {

  56:                 throw e;

  57:             }

  58:         }

Next, we have the AttachAndSendEmail() function to connect to the SMPT server and send the email. This function achieves the following tasks

  1. Create an SMTP client to connect to the SMTP server.
  2. Create the From and To Addresses for the email.
  3. Creates the MailMessage with the attachment.
  4. And Sends the email.
   1: public void AttachAndSendEmail(string ReportFileName)

   2:         {

   3:  

   4:             SmtpClient client = new SmtpClient("Address of the SMTP Server", 25);

   5:             client.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

   6:  

   7:             MailAddress from = new MailAddress("<from Address here>","<DisplayNamehere>",

   8:                             System.Text.Encoding.UTF8);

   9:  

  10:             MailAddress to = new MailAddress("<To Address here>","<DisplayNamehere>",

  11:                 System.Text.Encoding.UTF8);

  12:  

  13:  

  14:             MailMessage msg = new MailMessage(from,to);

  15:             msg.Subject = "Report Attached " + DateTime.Now.ToString();

  16:             msg.Body = "Report From Report Server";

  17:             msg.BodyEncoding = System.Text.Encoding.UTF8;

  18:             msg.Attachments.Add(new Attachment(ReportFileName));

  19:  

  20:             try

  21:             {

  22:                 client.Send(msg);

  23:             }

  24:             catch (System.Net.Mail.SmtpException e)

  25:             { throw e; }

  26:         }

 

This code sends the messages in an synchronous manner. In case if required, SMTPClient.SendAsync method can also be used.