Email reports based on a condition

Hi,

Data is fed to our database from 10 different places. We massage the data and then send out reports via email with rs subscriptions.

Everything works fine except when at least one of the data feeds does not load properly on time. The reports go out but with uncomplete data generating undesired effect in management.

I can create a "Loaded" flag on the database. Is there a way to use this or other method to send out the reports based on a condition?

Thank you.

One more thing, we only have the standard edition of rs, not the enterprise edition where you can use data-driven subscription.

In the meantime, we thought of a possible solution: Create a status table and new dataset in rs. Based on the status of the day, hide or make visible detail and header rows of the report. The same idea apply to a text box stating the status of the loads.

|||

I did some research on that subject for SQL2000 RPS.

Peter Blackburn had a solution, here is his message as well as my comments.

It should still work with 2005

For the condition, you can set a step to test your "Status" table ad success only when your data is ready and fire the report job.

Have fun,

Philippe

-

Hi,

Here the trick is to use SP_Start_Job to start the report from a sp or a dts.

You can also use something like exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription', @.EventData='6273f0b2-3899-416c-ada7-29c801a2c16f'

in your sp or DTS.

As Peter says, this breaks when you edit the subscription schedule from ReportServer but still, you can do it and you can also set a test like

DECLARE @.JobID BINARY(16)

SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'EF3028CF-D22A-4EA7-B197-D9018D6BA262')
IF (@.JobID IS NULL)
BEGIN

-- send some sort of alert to the developer so he recreates the correct calls....

EXEC msdb.dbo.sp_send_dbmail

@.recipients = 'someone@.someplace.com',

@.body = 'The report BlahBlah EF3028CF-D22A-4EA7-B197-D9018D6BA262 could not run, it may have been updated on the report server, sorry.',

@.subject = 'Report could not run';

else

-- run the report

exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription', @.EventData='6273f0b2-3899-416c-ada7-29c801a2c16f'

end

This is not optimal however this may be very handful for reports where you absolutely need it.

When you set your initial schedule in the past, set a time easy to find in the job list.

Phil

Subject: Re: Scheduling a Report based on an event

11/9/2004 10:38 PM PST

By:

Peter Blackburn (www.sqlreportingservice

In:

microsoft.public.sqlserver.reportingsvcs

Was this post helpful to you?

Sure this is real easy to do.Create a schedule that has completed in the past - so effectively it will never fire. Associate this schedule with a Report.Now what happens is that a SQL Agent Job is created - that maps to the schedule. You can run SQL Agent Jobs from the SQL Agent Management interface by hand - or you can cause that job to run through T-SQL.All that the SQL Agent Job does is create an entry in the Report Server's Event table at the scheduled time. The Report Server Windows Service is polling the Event table every 10 seconds or so - and if there are any events to process it gets on and processes them.So what you do is either include in your long running stored procedure a call that will create the required entry in the Event table directly - or a call that fires the SQL Agent Job.- One word of warning though if you start editing the schedule in the Report Manager, then the Report Manager can end up re-creating the SQL Agent Jobs - and you lose reference to the actual Job.However if you are disciplined enough then this approach works fine - (Schedule in the past, have your own process force the SQL Agent Job to run)Peter BlackburnHitchhiker's Guide to SQL Server 2000 Reporting Serviceshttp://www.sqlreportingservices.net"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message news:OWMxVAoxEHA.3080@.TK2MSFTNGP14.phx.gbl...> Is it possible to schedule a report based on a flag or stored procedure > completing. Currently have an overnight load process which must complete > before the report starts. Any suggestions would be most appreciated>> > Posted using Wimdows.net NntpNews Component -

|||

Thank you for your response.

We got the results we wanted by creating a load_status table. In my report I created a new dataset that looks up for the flag value on this table.

Then we condition the visibility of each row of the table based on this flag. We added another header row with the "sorry, not all data was loaded, report will be sent later" with the opposite condition and it works great.

Good luck!