Send ssrs report to variable emails

Question

Post reply

Send ssrs report to variable emails

dopydb

SSCertifiable

Points: 6687
More actions
November 26, 2015 at 4:22 am

#322815

hi
i have a report that i pass parameter supplier code to and it returns their orders.
what i would like to do is use a query to determine all the supplier parameters, pass supplier each one at a time to the report and email it to the respective supplier email address
can anyone advise on a tutorial that will assist me in this?
thanks
mal

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Why not just incorporate the query that determines the parameter values into the report?

John

dopydb SSCertifiable Points: 6687 More actions · Answer 2

hi John

i only want each supplier to get their own data, can i execute an ssrs report within a stored procedure and email it one report at a time? say using a cursor?

mal

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Mal

Have you tried using subscriptions? You can specify parameters for those - not dynamically, as far as I'm aware, but all you need to do is pass the supplier name in and your report can do the rest.

John

dopydb SSCertifiable Points: 6687 More actions · Answer 4

hi John

yea i have used subscriptions

i need this to run automatically on the 1 report (dont want to create a report per supplier), and have relevant supplier parameters passed to it.

from searching online i think data driven subscriptions is what i am after, but i don't seem to have this ability in 2005?

mal

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Mal

You don't need a separate report per supplier - just a separate subscription. You might need to change your report a little to get to where you want to be, though.

I've never used DDSs. As far as I know, they're only available in Enterprise Edition, and they run in response to changes in data. Is that what you're looking for? From what you've said so far, I don't think it is.

John

dopydb SSCertifiable Points: 6687 More actions · Answer 6

yea, but if i have 100 suppliers then i need 100 subscriptions?

iv managed to send the report using a script, so i think if i can pass the email address as a parameter to srs also i should be able to do what i want !

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

dopydb (11/26/2015)
yea, but if i have 100 suppliers then i need 100 subscriptions?

Yes.

iv managed to send the report using a script, so i think if i can pass the email address as a parameter to srs also i should be able to do what i want !

If you've found a way of doing that, that's great. I've never tried it myself, so I can't really advise. I'd be surprised if there isn't a way of doing it with Powershell, though.

John

dopydb SSCertifiable Points: 6687 More actions · Answer 8

BY USING

EXEC msdb.dbo.sp_start_job

i can execute the report at will

i just need to work out how to pass the haulier parameter!

tripleAxe SSCertifiable Points: 5706 More actions · Answer 9

Take a look at the T-SQL in the job step though, you will see that it executes something like this below.

exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='35e240e6-6ca7-415e-8eeb-36a880d264b7'

There is no way of sending the email address and any other parameter through. Doing it this way you would still need to have the 100 subscriptions. The @EventData parameter references a row in the ReportServer.dbo.Subscriptions table.

If you have Enterprise Edition you can do what you want using Data Driven Subscriptions. You provide a SQL statement that returns the email addresses and the parameter values to use for the report. This can be done on a timed subscription as well, not just when data changes.

If you don't have Enterprise Edition then one way of doing this is using a SSIS package to render the report for each customer and then email it out. This TechNet article outlines an approach for this that I have followed a couple of times. https://technet.microsoft.com/en-us/library/ff793463%5B/url%5D

dopydb SSCertifiable Points: 6687 More actions · Answer 10

yea i think ssis is where i need to go

i can dump the reports into a directory, and pickup them up from there and deliver to specifc email address's hopefully

Steven.Howes SSCertifiable Points: 6230 More actions · Answer 11

I do this for a few reports.

I use the rs.exe to generate pdf or excel files to a directory then use the sp_send_dbmail to sent to a list of email addresses pulled from an employee table. if you're sending the same file to the list of recipients then you can concatenate them together using an FOR XML PATH statement. otherwise you'll have to use a cursor or another loop structure.

I found this.[/url] It outlines how to use rs.exe.

I apologize for the terse answer. I haven;t looked at the code for this for awhile, so the exact details are fuzzy right now. I remember having to do a bit of futzing to get it to work but it was pretty straight forward. I can dig it out if you have questiois or run into problems.

dopydb SSCertifiable Points: 6687 More actions · Answer 12

Thanks Steve, will look into this next week, no need to apologise I appreciate the always great advice i get on here!