| Author |
Topic |
|
Juason
Starting Member
17 Posts |
 Posted - 2005-01-14 : 09:56:28
|
| Hello everyone, Just recently I began working on a project to upgrade our current in-house report generation software over to SQL Reporting Services. Installation and report generation have been a breeze, and I am impressed with the capabilities of this software. Still, I am being asked to do several things I am not sure the software is capable of, and was wondering if you might be able to lead me in the right directions? For the time being I have converted one of our projects over to SQL Reporting. This is a set of reports that we as of now use many scripts to generate - and then copy/paste over into excel for production. With SQL reporting services I split each script into its own report, and use the save to excel function of the web-interface for report publication. Simple. But, I need to be able to batch run all the reports (there are 12 associated with this project) and generate excel and pdf formats of them in a destination directory. This is more in the interest of saving time of course, we could always manually launch every report, view it, and save it as an excel file - but this not going to save us much, if any time over what we currently do. I read another thread about batch pdf generation that included a snippet of script code, but it was incomplete and my knowledge of C# is too limited to fix it I expect to have to learn for this project, so all suggestions are helpful. Can batch report generations be done directly to an output file? Is there some sample script code I might modify to get this done? Would an easier method be to take this into consideration during the writing of the report, and somehow link all 12 into one large report? Is there a 3rd party utility I could use to acheive this same result? I appreciate any help you could provide me with. Thanks!-J |
|
|
jhermiz
3564 Posts |
Posted - 2005-01-14 : 11:00:54
|
Read up on subscriptions ??Subscriptions allow you to define the query parameters, and set a report to be saved / emailedat a specific time. Just like the NT Scheduler.
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
|
 |
|
|
Juason
Starting Member
17 Posts |
Posted - 2005-01-14 : 13:47:47
|
I had thought about using subscriptions, but unfortunately the timeliness of these reports varies from month to month. Sometimes I might need to run 12 of the 50 on the server on the 1st of the month, wheras the next month I might only have to do 6 of the 50 but on the 7th of the month. Does this make sense? Now, can I just write a script to email myself reports X, Y, and Z 10 minutes from now? This is kind of an interesting way of doing it, but might work. I would have to write a new script for each report run, but it would work. I could even hang onto the scripts to generate specific quantities (say all 50) and just change the time for them to be emailed depending on when I need them. Thanks for the thought, and am I thinking about this correctly?quote:
Originally posted by jhermiz
Read up on subscriptions ??Subscriptions allow you to define the query parameters, and set a report to be saved / emailedat a specific time. Just like the NT Scheduler.
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
|
|
|
|
Juason
Starting Member
17 Posts |
Posted - 2005-01-14 : 14:35:03
|
| I was just informed email is NOT a viable way of receiving reports. Due to the potentially large size of generated reports >60mb email will not be possible. Instead can I run reports A,B,C...Z and have PDFs or Excel files generated into a directory? Thanks as always! |
|
|
|
Juason
Starting Member
17 Posts |
Posted - 2005-01-14 : 14:49:16
|
| Ok, after looking into the subscription options (and getting it working, darn credentials) I see how each report can be saved to a share directory. This still does not solve my problem of running multiple reports at one time. I have to open each report individually and assign when for it to be run - its quicker just to run each one if this is the case. Am I missing some batch-run option? Or is there another way. I just want to run and save X number of reports at once. Thanks as always! |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2005-01-14 : 14:53:22
|
| This isn't going to help much with reporting services but it is a modelthat can be exploited real well sometimes. I was impressed this ReportingServices at first but realize some more had to be done before I could use it for everthing.Acrobat has the ability to generate a .pdf of a website. If you point Acrobatto a root page with links to a whole range of reports it will dig down and run themall for you and put them in a single .PDF The trick is to set up your actuallyreporting page structures so they can be reponsive to Request.QueryString. Then themain pointer page can be created dynamically.It really is the coolest thing once you see it work. Especially effective when complexreal time reporting can be run off peak hours hands free for large ammounts.I wish someone would start an Official XML Rant Thread. |
|
|
|
Juason
Starting Member
17 Posts |
Posted - 2005-01-14 : 15:00:13
|
| I see what you are saying, so I could point Acrobat to say a folder on the server containing the 50 or so reports I would like to generate, and then have it generate all the files into a PDF. Not going to solve my problem, but I would like to at least test it for effectiveness. How do I modify my reports to accept a Request.QueryString? I mean, if its something simple I'll just go ahead and give it a whirl. Unfortantely Upper Management is convinced Reporting Services has batch reporting capability (they've even showed me on Microsoft's Data Sheet for SQL Reporting that its possible). For the life of me I can't figure out a way to run multiple reports and have their outputs saved into a folder :sigh: |
|
|
|
Juason
Starting Member
17 Posts |
Posted - 2005-01-14 : 16:38:14
|
| I am a believer in always updating with possible fixes for problems you find, so here we go :)To solve my problem I located a VB script for publishing reports directly to file. This sample script can be found at [url]http://sqljunkies.com/HowTo/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk[/url] Notice it takes a value for report path, output file name, and file type. From the command line I can use the following to generate a single report : rs -i MyReportScript.rss -s http://MyServerName -v FileName="Output file path" -v reportPath="Report Source Path" . Now this is fine for generating a single report. I am going further and developing a VB form to query a database I have listing all my Report Definitions. This form allows me to select which report definitions to run, and what output file type I want them to be. Then I hit generate, and it runs a loop that generates a batch file to run all the selected report definitions. In essence it runs the above command line and changes the fileName and reportPath variables to reflect each Report I wish to generate. Now to see if it works!!! I will update as I can unless someone can point me in a better (or quicker) direction. |
|
|
|
Juason
Starting Member
17 Posts |
Posted - 2005-01-19 : 14:51:56
|
| Well I was successful in my endeavor to batch run multiple reports. I created an Access Database containing the Report Definitions, their location, and a selected column. Then I created an Access Form displaying all the report defs in my database. I allow the user to add reports to a second listing for batch generation. You type in an output path and a report format and click done, then a batch file is generated with parameters to run the first report and it is executed. Once complete the batch file is updated with parameters for the second report, and so on.Overall it means I can in about 15 seconds run 50 reports, rather than doing them all by hand and waiting in between. My new question is, can I somehow create a report from existing DTS packages? In trying to convert over some of our larger reports I am hitting walls. Right now we have 36 DTS packages that are executed to create several reports for an associate. Would I be better off re-creating the entire report inside of .NET? Or, can I somehow link up the DTS packages into the report designer and then tweak when necessary? Again, I apologize for the vague nature of my questions as I am pretty inexperienced with this. But, I'm learning every day! Thanks again. |
|
|
|
|
Need help with batch report generation