Manual / Conditional email report from reporting services

Microsoft Windows
SQL Server
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
    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 :/
    0 pointsBadges:
    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.
    0 pointsBadges:
  • GeoffD
    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 = '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.
    160 pointsBadges:
  • Sqllion
    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.)
    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: