Don't send report if it has no data

Hi All,
I have the standard version of SQL Server installed.
I have setup a few reports that will be automatically sent to some out
clients on a daily basis via a normal email-based subscription in reporting
services.
On some days, these reports are blank i.e. no rows returned in the dataset.
I am wondering if it is possible for the report to not be sent if a
situation exists where the report is blank
Thanks
SanjeevIf you have access to the sql server agent and can write a quick query that
will let you know if the data is going to be available it is possible. Each
subscription creates a sql server job in the sql server agent. The job
basically fires a stored procedure that executes the event (get params->run
report->render->mail or save...). The sql server jobs are saved with GUID
names that link back to the report like below. So look in the catalog and
find the report link to the scheduleID or Subscription ID. That ID will
link with the sql server job. Edit the job and have it do some if logic.
IF (there are results in this quick query) THEN trigger the event ELSE
(return 0, dbsendmail an error or however you want to handle it). This is
the basic idea of it. Let me know if you need any more help/detail.
FROM dbo.Catalog INNER JOIN
dbo.ReportSchedule ON dbo.Catalog.ItemID = dbo.ReportSchedule.ReportID INNER
JOIN
dbo.Schedule ON dbo.ReportSchedule.ScheduleID = dbo.Schedule.ScheduleID
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Sanjeev Rampersad" <sanjeev@.sqr.com> wrote in message
news:O7miaQq9GHA.3960@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have the standard version of SQL Server installed.
> I have setup a few reports that will be automatically sent to some out
> clients on a daily basis via a normal email-based subscription in
> reporting services.
> On some days, these reports are blank i.e. no rows returned in the
> dataset.
> I am wondering if it is possible for the report to not be sent if a
> situation exists where the report is blank
> Thanks
> Sanjeev
>|||Hi Michael,
Works like a charm.
Thanks a million
Sanjeev
"Michael Abair" <michael.abair@.chicos.com> wrote in message
news:%23Xgxept9GHA.4708@.TK2MSFTNGP05.phx.gbl...
> If you have access to the sql server agent and can write a quick query
> that will let you know if the data is going to be available it is
> possible. Each subscription creates a sql server job in the sql server
> agent. The job basically fires a stored procedure that executes the event
> (get params->run report->render->mail or save...). The sql server jobs
> are saved with GUID names that link back to the report like below. So
> look in the catalog and find the report link to the scheduleID or
> Subscription ID. That ID will link with the sql server job. Edit the job
> and have it do some if logic. IF (there are results in this quick query)
> THEN trigger the event ELSE (return 0, dbsendmail an error or however you
> want to handle it). This is the basic idea of it. Let me know if you
> need any more help/detail.
>
> FROM dbo.Catalog INNER JOIN
> dbo.ReportSchedule ON dbo.Catalog.ItemID = dbo.ReportSchedule.ReportID
> INNER JOIN
> dbo.Schedule ON dbo.ReportSchedule.ScheduleID = dbo.Schedule.ScheduleID
>
> --
> Michael Abair
> Programmer / Analyst
> Chicos FAS Inc.
> "Sanjeev Rampersad" <sanjeev@.sqr.com> wrote in message
> news:O7miaQq9GHA.3960@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> I have the standard version of SQL Server installed.
>> I have setup a few reports that will be automatically sent to some out
>> clients on a daily basis via a normal email-based subscription in
>> reporting services.
>> On some days, these reports are blank i.e. no rows returned in the
>> dataset.
>> I am wondering if it is possible for the report to not be sent if a
>> situation exists where the report is blank
>> Thanks
>> Sanjeev
>