Sql-server – Send email with multiple attachment report in PDF through SQL Reporting Services

reporting-servicessql serversql-server-2008

I am using SQL 2008 Reporting Services. I am opening the report in browser after setting the parameters from application. I create subscription of report from code, which sends email along with attachment in PDF.

But I want to create subscription of multiple reports from code and one email should send to client along with multiple PDF attachments. (Note: no need to send email against each subscription)
I tried myself but not find any solution. Could you please guide me that how can I send email with multiple PDF attachment reports data in one mail.

Or any other way to achieve this goal that sends multiple reports in PDF format in one email?

Code is given below which I am using for create subscription. It will send email along with attachment. But as I mention above that I want multiple attachment in one email.

RSServiceReference05.ReportingService2005SoapClient rs = new RSServiceReference05.ReportingService2005SoapClient();

rs.ClientCredentials.Windows.AllowedImpersonationLevel = new System.Security.Principal.TokenImpersonationLevel();            

string batchID = string.Empty;
RSServiceReference05.ServerInfoHeader infoHeader = rs.CreateBatch(out batchID);

RSServiceReference05.BatchHeader bh = new RSServiceReference05.BatchHeader()
            {
                BatchID = batchID,
                AnyAttr = infoHeader.AnyAttr
            };                    

string desc = "Report is attached.";
string eventType = "TimedSubscription";
DateTime dt = DateTime.Now;
string scheduleXml = "<ScheduleDefinition xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"><StartDateTime xmlns=\"http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices\">" + dt.AddMinutes(3).ToString("dd/MMM/yyyy HH:mm:ss")+"+05:00</StartDateTime></ScheduleDefinition>";


RSServiceReference05.ParameterValue[] extensionParams = new RSServiceReference05.ParameterValue[7];
extensionParams[0] = new RSServiceReference05.ParameterValue();
extensionParams[0].Name = "TO";            
extensionParams[0].Value = txtEmailTo.Text;
extensionParams[1] = new RSServiceReference05.ParameterValue();
extensionParams[1].Name = "IncludeReport";
extensionParams[1].Value = "True";
extensionParams[2] = new RSServiceReference05.ParameterValue();
extensionParams[2].Name = "RenderFormat";
extensionParams[2].Value = "PDF";
extensionParams[3] = new RSServiceReference05.ParameterValue();
extensionParams[3].Name = "Subject";           
extensionParams[3].Value = txtSubject.Text;
extensionParams[4] = new RSServiceReference05.ParameterValue();
extensionParams[4].Name = "Comment";            
extensionParams[4].Value = txtMessage.Text;
extensionParams[5] = new RSServiceReference05.ParameterValue();
extensionParams[5].Name = "IncludeLink";
extensionParams[5].Value = "False";
extensionParams[6] = new RSServiceReference05.ParameterValue();
extensionParams[6].Name = "Priority";
extensionParams[6].Value = "NORMAL";


string matchData = scheduleXml;

RSServiceReference05.ExtensionSettings extSettings = new RSServiceReference05.ExtensionSettings();            

extSettings.ParameterValues = extensionParams;
extSettings.Extension = "Report Server Email";
    string sub = "";
RSServiceReference05.ServerInfoHeader SubID = 
rs.CreateSubscription(bh, "/MyReports/MyTestReport", extSettings, desc, eventType, matchData, parameters, out sub);
RSServiceReference05.ServerInfoHeader EventID = rs.FireEvent(bh, "TimedSubscription", sub);
       rs.ExecuteBatch(bh);

Best Answer

  1. Begin by creating one master report - this will be the report in your subscription that is emailed to the user.
  2. Create all of your required reports on your report server as normal.
  3. Add a tablix to your master report
  4. Populate each text box with the heading for each report as required, e.g MyTestReport1, MyTestReport2 etc..
  5. For each report heading on the master report, right-click select Text Box Properties - Action - Go to URL. Enter the URL directly to where the report is stored on your report server (OR you can use the Expression box if you want anything more complex than just opening the report).

enter image description here

Now, when your subscription runs and your master report is emailed to the user they can select the report they want and the download in whatever format they require (eg PDF, Excel, CSV) when they open it.

ALTERNATIVELY, you can configure the hyperlink to prompt the user to save the attachment when clicked by appending the following to your URL:

for PDF

&rs:Format=PDF

or for Excel

&rs:Format=Excel

I think the alternative here is what you are trying to achieve and works very well.

Related Topic