Manual / Conditional email report from reporting services
I've got a bit of a problem. Our company has consolidating reports, and intranet servers. One of the reports that is used quite a bit is a report delivered to warehouse management, and executives detailing orders that have been printed on a given day.
I'd origionally written this report in ASP using an ASP graphing component. The SQL has been optimized considerably, but, the method of generating graphs that I used was a major slow-down (took 15-20 seconds to generate). I've rewritten the report using reporting services and 1 query instead of 4. The report now generates in less then a second, and I'd like to depoly the report, BUT, here's where our problems start.
Our sending practices for the report are to send every time we finish a print run. There are some cases where I could schedule for these, but, for exception runs, and the like, there is no way to set up a strict schedule of when to send the report.
So far, we've been able to thin of two options.
1) use a piece of data or flag to feed to the generating stored procedure, and cause the report to fail if the 'send' flag isn't set. Then set up a schedule for the report to run every 5-10 min.
2) create a seperate application to scrape the report from reporting services, and then e-mail the scraped results.
I don't really like either of these options. It just feels like there should be an option in reporting services to manage this type of situation.
Any ideas?
thanks,
Kevin
Software/Hardware used:
Software/Hardware used:
ASKED:
October 11, 2006 12:00 PM
UPDATED:
November 30, 2011 7:24 AM
Answer Wiki:
You could alter option number two in the following way: instead of scraping the results, you could call the Reporting Services Web Service to generate the report and convert it to a PDF (or other acceptable format). Then, you could email the PDF from the application as an attachment. Do you have the resources to create an ASP.NET application? I believe calling web services has much more support for .NET.
To see all answers submitted to the Answer Wiki: View Answer History.
I came across option #3. You can set a subscription based on a data event, and I can then fire the to list based upon what a query returns. Now I just need to figure out how to cause the email to generate without any intervention. (I *THINK* I can manage to query out the necessary information)
Still, letting an op click a button on reporting services when it was time to mail (or remail) the report would have been nice. I just can’t seem to find any functionality that will do that :/
Thanks to everyone who tried to help. I figured out a solution. We set up a data driven subscription, and I selected a count of order batches printed in the 16 min prior to last run, then I had it return null if there were none, or a list of e-mail addresses if there were, and then scheduled it to run every 15 min. not the most elegant solution, but it works.
The way I have dealt with a similiar need was to create a subscription in reporting services to run once. This creates an entry in the schedules table.
Then you can run the following to create the report:
use reportserver
insert into [Event]
([EventID], [EventType], [EventData], [TimeEntered], [ProcessStart], [BatchID])
Select NewID(),sc.Eventtype, sc.Eventdata,GETUTCDATE(), NULL, NULL
from Catalog c
Left outer join ReportSchedule RS
on c.itemid = rs.Reportid
inner join Schedule sc
on rs.scheduleid = sc.scheduleid
where c.name = ‘Job Status Report’ and SC.NextRunTime is null
Once setup as a stored procedure you can pass various report names or ID’s as you please.
One of the effective and correct way to get the job Running Status report in SSRS for easy viewing and monitoring jobs running on servers. (.rdl file attached.)
http://www.sqllion.com/2011/11/job-running-status-report-in-ssrs/