Send email with multiple attachment report in PDF through SQL Reporting Services

Question

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);

Answer 1

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).

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.

Answer 2

SSRS report subscriptions can only be configured with one attachment per email.

In a past life where I developed executive SSRS reports in a SQL 2005 environment, our team had a report generation utility application written in VB.NET that worked alongside a web service. The report generation utility would be executed by SQL Agent jobs or SSIS packages with a command line switch for the specific report needed to be generated at that time.

The application first performed any special data processing, then it would send up arguments to the web service. The web service used the SSRS API to send in parameters to the report, generate the necessary report file, and save it to a server. After all report files were generated, the application sent the email with all report files as attachments to the business group.

The downside to this approach is that you create a maintenance overhead in supporting the report generation utility as well as the metadata you need to create for it to run dynamically (business group email addresses, report ids, params, etc). The payoff for us was worth it, as this approach allowed any reports that were dependent on other enterprise systems to only be executed when the data was ready.