Possible to attach SSRS report as PDF to email?

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.
LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
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
CamilliaAuthor Commented:
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.
CamilliaAuthor Commented:
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?
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

CamilliaAuthor Commented:
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"
Russell FoxDatabase DeveloperCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CamilliaAuthor Commented:
Thanks, Russell. I think I have to a have .net application. But I'll look at subscriptions as well.
vastoCommented:
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
CamilliaAuthor Commented:
Thanks, I'll take a look
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.