Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generate and send report via email in PDF format which method better Expand / Collapse
Author
Message
Posted Friday, October 4, 2013 3:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:27 AM
Points: 2, Visits: 110
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
Post #1501478
Posted Friday, October 4, 2013 8:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:11 PM
Points: 35,769, Visits: 32,432
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1501617
Posted Sunday, October 6, 2013 3:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:27 AM
Points: 2, Visits: 110
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
Post #1501905
Posted Sunday, October 6, 2013 10:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:11 PM
Points: 35,769, Visits: 32,432
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1501927
Posted Sunday, October 6, 2013 11:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:11 PM
Points: 35,769, Visits: 32,432
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1501928
Posted Monday, October 7, 2013 5:25 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 6:12 PM
Points: 827, Visits: 342
Thanks Jeff! That's indeed a valuable information.
Post #1502422
Posted Tuesday, October 8, 2013 4:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:11 PM
Points: 35,769, Visits: 32,432
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1502540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse