Possible to attach SSRS report as PDF to email?

Camillia
Camillia used Ask the Experts™
on
I built my first SSRS report. We have 2 requirements

1. Send the SSRS report in form of PDF to *internal users*. To do this, I email the internal users a link to the SSRS report in PDF format. I also email them a link to the actual report to view it on the report server.

2.  We have to email the report to external users/clients. I can't give them a URL in email. The report has to be in PDF format, attached to the email.

I can get the report in PDF format using the SSRS URL with "PDF" as the format. But, anyway I can attach this to an email? Someone here said I need to use memory stream.

This is MVC/ C#/ SQL 2015.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Russell FoxDatabase Developer
Top Expert 2014

Commented:
Easiest is to set up a subscription on your SSRS site, selecting the PDF output. If you can't do that, you may need to do something fancy like specify a file share as the output location and set up some other code to iterate over the files and email them on-demand:
Subscriptions, SSRS
Thanks. Another question...if it's a subscription, how do I attach it to the email? how do I get to it..where is it stored?

OR, is it automatically emailed?? Looks like it's automatically emailed. Correct? if so, how can I specify who to email it to? We have specific users per PDF we need to email to.
I don't think we can have an email Subscription here. Each PDF has a specific set of users that needs to PDF to go to.

Now, my coworker says SSRS has web services, we can call it and get the PDF in binary format and then use memory stream to save it in database or convert it to PDF in code and attach it. Is that correct? I'll Google but is that correct?
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

This is what someone else says here

"what I remember you can create the PDF file consume the file and convert into byte array and store that as binary in db....



I don't know if there is a built in function of the webservice that will return it as a binary string
something like this:
string sTargetURL = "http://stl-sql-01/TulNasrvr?" + "/EmpTmpHrs&rs:Command=Render&rs:format=PDF&ReportParam=" + ParamValue; HttpWebRequest req = (HttpWebRequest)WebRequest.Create( sTargetURL ); req.PreAuthenticate = true; req.Credentials = new System.Net.NetworkCredential( strReportUser, strReportUserPW, strReportUserDomain ); HttpWebResponse HttpWResp = (HttpWebResponse)req.GetResponse(); Stream fStream = HttpWResp.GetResponseStream();
then you can store that fStream object in the database"
Database Developer
Top Expert 2014
Commented:
To use a subscription you would need to set up a report parameter and set that parameter for each recipient in the subscription. For example, if you needed a sales report to go to each salesperson with only their information, you would put a "WHERE SalesPersonID = @SalesPersonID" in your query which will create a parameter, and then you would set up a subscription for each person with their email address and set the parameter to their ID. Report parameters will appear at the bottom of the subscription page.

If you're lucky enough to have the Enterprise Edition of SQL Server, you can skip a lot of the tedium by using Data-Driven Subscriptions.

You might be able to use the "rs:command=render" URL parameter to output to PDF, but I don't think that will work for you since they won't have access to the site:
http://.../ReportName/&rs:Command=Render&rs:Format=PDF...

Open in new window

You might also be able to create an SSIS package to do this, but I don't have any experience with doing so. Beyond that, I suspect you're looking at building a .Net application to generate and send the files.
Thanks, Russell. I think I have to a have .net application. But I'll look at subscriptions as well.
Top Expert 2011

Commented:
You can check this article. It might give you another option for a data driven subscription without Enterprise edition: https://sqlswimmer.wordpress.com/2014/07/01/data-driven-subscriptions-1/

There are also inexpensive 3rd party tools , which  can handle data driven subscriptions. This one is a good example : http://www.r-tag.com/Pages/ReportManager.aspx
Thanks, I'll take a look

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial