SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generate and send report via email in PDF format which method better


Generate and send report via email in PDF format which method better

Author
Message
Gergely Mészáros
Gergely Mészáros
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 131
Hi all,

I need some suggestions from experts, which way we should go.

Our company want to process and send various reports each week to a number of various recipients, and with various parameters. We have standard edition, so we don't have the opportunity to make data driven subscriptions.

Only our IT System Support department have sufficient rights to make subscriptions or to modify report definitions or the data sources of the reports. If we want to do such things, we have to get over a long process (approvals etc..).

The two ideas:
A.
Create data driven subscriptions wia stored procedure calls.
It must be done in two steps, because we dont have the rights to the reportserver db. So we put the subscription data somewhere, then a job creates the subscriptions from it.

B.
Load, render, save and send the reports via ReportExecutionService with an SSIS package.
similar to this: http://msbimentalist.wordpress.com/2011/12/27/execute-ssrs-report-from-ssis-package/

Feel free to suggest, write opinion, or form judgment!
Please consider security issues, possible load on support, maintanace costs.

Thanks in advance,
Greg
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87418 Visits: 41113
Gergely Mészáros (10/4/2013)
If we want to do such things, we have to get over a long process (approvals etc..).


Greg, there's a reason for that long process and if you bypass it, you could get yourself and others fired. Work within the established system. If the system is "broken", then work to fix it. It'll take some time to do that and you'll have to get management buy-in, but it'll be worth it in the long run.

If you suggest there should be no such system, you will fail, so don't start there.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Gergely Mészáros
Gergely Mészáros
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 131
Hi Jeff,

Thanks for your reply. There were a little miscomprehension. I ve tried to write it in a sort paragraph, but it was too short.
It's a really strange situation. A bit more information:

IT System Support dont want to spend their time with managing subscriptions. And as i mentioned we have standard edition, no data driven subscription on the UI. They asked us to find a workaround. I surely wont be fired.

The question is wich workaround is less bad.

My opinion the A version is violence on the system. I would be happy, if someone wrote any detailed confirmation on it.

What i really want to know, what is the problem with the B version.
- Every responsible have the rights to run the reports.
- Therefore we have rights to the Report Execution Service.
- I think its one of the recommended interfaces of Reporting Services.
- I think doesnt matter SSIS or my IE requests the report.
- Version B has no essential difference from running the report, saving, and sending it. It is a way to do it easier.

Thanks,
Greg
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87418 Visits: 41113
Ah... understood. I thought you were trying to "illegally" bypass established protocols of development in your company. Thanks for the clarification.

I believe this could be done in a rather simple manner if I'm reading the requirements correctly. I'm reading the requirements simply as there are several reports that need to be created and then certain reports need to be sent to certain groups of people.

I'm also reading that one of the biggest requirements is as you mentioned...
IT System Support dont want to spend their time with managing subscriptions.



I'm no SSRS nor SSIS expert so you'll need to figure out how to get either to save a file (I've been led to believe that's a simple task) but I believe that I'd do this as follows. I realize that this is just a functional overview but you have to start somewhere. As they say, you can't correct a blank piece of paper. ;-) As it turns out, this is a combination of both your "A" and "B" methods.

1. Create several nightly (or whatever schedule you need) jobs to create the reports as either PDF's or Spreadsheets depending on the need and have the system save them in a disk folder dedicated to the task.

2. Create a table that contains the report name, static file name, and the email address of each person to receive the report. There would be one row per report name/email address combination. It might also server you well to have a start and end date for each row so you have a history of who was enabled to receive which report and when. If you decide to do this (and I do recommend it), make the default end date '9999' which will be converted to 9999-01-01 for and end date. This will keep you from having to mess around with using NULL as an open end date. For reference purposes, I'd call this table something like 'ReportSubscription' (I almost never pluralize table names... I name tables after what a single row contains).

As a bit of a side bar, you should probably create more than just one table so that you can do a little normalization and establish some DRI (Declared Referential Integrity) for report and file names, etc. It's a minor complexity that almost always pays off in the long run.

3 The rest is fairly easy. Have a single job run a single stored procedure that that reads and finds the distinct values of all report names from the ReportSubscription table where "now" is between the start and end dates. Using the FOR XML PATH trick for concatenation, concatenate all of the "active" ("now" falls between the start and end dates) email addresses into the "to" list for each report, add the filename of the report for each report as a file attachment, and use all that to build the sp_senddbmail command for each report. Then execute each command. The construction of all of these commands can actually be done using a single well formed and fairly easy to construct SELECT to put all of these commands into a single variable and then just execute that one variable.

What all of that does for you is simple... it allows simple subscription management just by updating a small table or two. Therefor, privs need to only be granted to that small table or two. It will also keep a history of who was scheduled to get which report and when. Neither the stored procedure nor the job to send the emails will ever need to be changed. Only the content of the small table or two will need to be changed.

As yet another sidebar, you could also put a time to send on each report listing in a separate table and run the job several times a day. This would allow you to easily control multiple transmissions of the same (updated) report or when a single report was supposed to be sent. Of course, the jobs to create the reports would need to execute more than once per day if the report needed to be sent more than once per day.

The advantage of storing the reports in a dedicated disk folder is that if someone needed to see the report on an ad hoc rather than scheduled basis, they could simply retrieve it instead of needing privs to run the report creation job and the email job.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87418 Visits: 41113
p.s. I forgot one caveat of sp_Send_DBMail... the default size of any attachment is only 1MB. Please lookup sp_Send_DBMail in "Books Online" and look for @file_attachments. according to BoL, someone will have to make the onetime effort to run the "Database Mail Configuration Wizard" to change that default.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Amit Raut
Amit Raut
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 342
Thanks Jeff! That's indeed a valuable information.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87418 Visits: 41113
Thanks, Amit. Please let us know how it works out for you. As a bit of a sidebar, I'm sometimes a little amazed at how tightly some folks lock down reporting servers internally.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search