Emailing Exported File from SQL Server Reporting Services 2005

I have created a sample class to email exported reports [PDF, Excel etc.] from SQL Reporting Services. To do that, I had to add a Report Viewer into the form and set Visible property to False. This web form actually grabs the parameter values from query string and process the report accordingly. You may call this form from another web page and pass the parameters like this:

 

===================================================

Email form calling function

===================================================

private void emailReport()

{

try

{

string _period1 = Convert.ToString(Convert.ToDateTime(dtStartDate.Value.ToString()).ToString("yyyyMM"));

string _period2 = Convert.ToString(Convert.ToDateTime(dtEndDate.Value.ToString()).ToString("yyyyMM"));

string _division = Convert.ToString(ddDivisions.SelectedValue.ToString());

string _department = Convert.ToString(ddDepartment.SelectedValue.ToString());

string _employee = "";

string _reportName = "/ExpenseReportByEmployee";

string parameterSet = "_reportName=" + _reportName + "&_period1=" + _period1 + "&_period2=" + _period2 + "&_division=" + _division + "&_department=" + _department + "&_employee=" + _employee;

ClientScript.RegisterStartupScript(typeof(Page), "myScript", "<script language=JavaScript>OpenReport('../WebForms/SendEmail.aspx?" + parameterSet + "');</script>");

}

catch (Exception ex)

{

string _errorMessage = ex.Message;

lblMessage.Text = _errorMessage;

}

}

===================================================

Email form code behind [SendEmail.aspx.cs]

===================================================

 

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Net.Mail;

using System.Net;

using System.Text;

using System.IO;

using Microsoft.Reporting.WebForms;

public partial class WebForms_SendEmail : System.Web.UI.Page

{

static string exportFileName;

static string _period1;

static string _period2;

static string _division;

static string _department;

static string _employee;

static string _reportName;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack && !IsCallback)

{

_period1 = Request.QueryString["_period1"].ToString();

_period2 = Request.QueryString["_period2"].ToString();

_division = Request.QueryString["_division"].ToString();

_department = Request.QueryString["_department"].ToString();

_employee = Request.QueryString["_employee"].ToString();

_reportName = Request.QueryString["_reportName"].ToString();

switch (_reportName)

{

case "/ExpenseReportByDivision":

exportFileName = "Expense Report By Division - From " + _period1.ToString() + " To " + _period2.ToString();

break;

case "/ExpenseReportByDepartment":

exportFileName = "Expense Report By Department - From " + _period1.ToString() + " To " + _period2.ToString();

break;

case "/ExpenseReportByEmployee":

exportFileName = "Expense Report By Employee - From " + _period1.ToString() + " To " + _period2.ToString();

break;

case "/CallDetailsByAccount":

exportFileName = "Expense Report By Account - From " + _period1.ToString() + " To " + _period2.ToString();

break;

}

txtFileName.Text = exportFileName + ".pdf";

}

}

private void generateReport(string _ReportName, string _period1, string _period2, string _division, string _department, string _employee)

{

try

{

string _ReportServer = System.Configuration.ConfigurationManager.ConnectionStrings["ReportServer"].ToString();

string _ReportPath = System.Configuration.ConfigurationManager.ConnectionStrings["ReportPath"].ToString();

ReportViewer1.ProcessingMode = ProcessingMode.Remote;

Uri ReportURI = new Uri(_ReportServer);

this.ReportViewer1.ServerReport.ReportServerUrl = ReportURI;

this.ReportViewer1.ServerReport.ReportPath = _ReportPath + _ReportName;

int repParams = 2;

switch (_ReportName)

{

case "/ExpenseReportByDivision":

repParams = 2;

break;

case "/ExpenseReportByDepartment":

repParams = 3;

break;

case "/ExpenseReportByEmployee":

repParams = 4;

break;

case "/CallDetailsByAccount":

repParams = 5;

break;

}

ReportParameter[] parameters = new ReportParameter[repParams];

parameters[0] = new ReportParameter("p_Period", _period1.ToString());

parameters[1] = new ReportParameter("p_Period1", _period2.ToString());

if (repParams >= 3)

{

parameters[2] = new ReportParameter("p_DivisionID", _division.ToString());

}

if (repParams >= 4)

{

parameters[3] = new ReportParameter("p_DepartmentID", _department.ToString());

}

if (repParams == 5)

{

parameters[4] = new ReportParameter("p_EmployeeID", _employee.ToString());

}

this.ReportViewer1.ServerReport.SetParameters(parameters);

//Export to File

string mimeType, encoding, extension, deviceInfo;

string[] streamids;

Microsoft.Reporting.WebForms.Warning[] warnings;

string format = "PDF"; //Desired format goes here (PDF, Excel, or Image)

deviceInfo = "<DeviceInfo>" + "<SimplePageHeaders>True</SimplePageHeaders>" + "</DeviceInfo>";

byte[] bytes = ReportViewer1.ServerReport.Render(format, deviceInfo, out mimeType, out encoding, out extension, out streamids, out warnings);

//Email Report

FazlulsEmail.Email _EmailClass = new FazlulsEmail.Email();

string[] _to = txtTo.Text.Split(',');

string[] _cc = txtCC.Text.Split(',');

string[] _bcc = txtBCC.Text.Split(',');

lblMessage.Text = _EmailClass.sendEmail(_to, _cc, _bcc, txtSubject.Text, txtMessage.Text, "fazlul.chowdhury@Fazlulsalberta.com", convertByteArrayToStream(bytes), txtFileName.Text, true);

}

catch (Exception EX)

{

String _error = EX.Message;

lblMessage.Text = _error;

}

}

public Stream convertByteArrayToStream(byte[] input)

{

Stream str = new MemoryStream(input);

return str;

}

protected void btnSend_Click(object sender, EventArgs e)

{

try

{

generateReport(_reportName, _period1, _period2, _division, _department, _employee);

}

catch (Exception EX)

{

String _error = EX.Message;

lblMessage.Text = _error;

}

}

protected void btnClose_Click(object sender, EventArgs e)

{

ClientScript.RegisterStartupScript(typeof(Page), "myScript", "<script language=JavaScript>window.close;</script>");

}

}