Data driven subscription: if no report data => do not deliver

I am wondering if there is a simple way to tell the report server to not
deliver a report if there is no data in the report.
I have several reports which have table layout to display a set of data IF
there happens to be data. One example is that I have a polling process
looking at certain tables in SQL Server every 10 minutes. Is there a quick
way to not send me an e-mail if there isn't data to look at?
Thanks!
DavidCurrently there is not, although this feature has been requested several
times. The only way to do it now is to construct the Data Driven
Subscriptions query to return no rows when the report will return no rows.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"david boardman" <davidboardman@.discussions.microsoft.com> wrote in message
news:8D692421-D6E1-4185-B66B-3E2356F59CB6@.microsoft.com...
>I am wondering if there is a simple way to tell the report server to not
> deliver a report if there is no data in the report.
> I have several reports which have table layout to display a set of data IF
> there happens to be data. One example is that I have a polling process
> looking at certain tables in SQL Server every 10 minutes. Is there a
> quick
> way to not send me an e-mail if there isn't data to look at?
> Thanks!
> David|||Hi,
I have a similar data-driven subscription. The first query determines the
receivers for email, the second query prepares the email content for the
corresponding receiver.
So as Daniel wrote, first I had to eliminate no-data records then run the
query for the remaining set.
"david boardman" wrote:
> I am wondering if there is a simple way to tell the report server to not
> deliver a report if there is no data in the report.
> I have several reports which have table layout to display a set of data IF
> there happens to be data. One example is that I have a polling process
> looking at certain tables in SQL Server every 10 minutes. Is there a quick
> way to not send me an e-mail if there isn't data to look at?
> Thanks!
> David|||I guess I can post my interim solution for those looking to at least have
something working. I just am looking for a more elegant/built in solution.
For data-driven subscriptions, RS requires you to specify an SQL query which
returns data to the subscription (things like what e-mails to send to, and
what parameters to pass to the actual report, if any). I exec a stor proc in
order to return this information back to my subscription. The key lies in
the stor proc being called.
The stor proc which feeds the data driven subscription ends up calling the
stor proc which returns data for the report (which in reality is completely
compartmentalized from the subscription itself). The subscription proc
checks to see if at least 1 row of data is returned by the report proc. If
the report stor proc returns no data, then the subscription stor proc returns
a NULL SET back for the e-mail addresses to deliver the report to.
I don't know if I'm capitalizing on a bug or not, but there is no error
generated in the log files. I guess the report is actually generated (with
no data) and delivered into never-never land.
Hope I explained what I'm doing clearly, but just wanted people to know
there is an interim solution, although some what cludgy.
Cheers!
"eralper" wrote:
> Hi,
> I have a similar data-driven subscription. The first query determines the
> receivers for email, the second query prepares the email content for the
> corresponding receiver.
> So as Daniel wrote, first I had to eliminate no-data records then run the
> query for the remaining set.
>
> "david boardman" wrote:
> > I am wondering if there is a simple way to tell the report server to not
> > deliver a report if there is no data in the report.
> >
> > I have several reports which have table layout to display a set of data IF
> > there happens to be data. One example is that I have a polling process
> > looking at certain tables in SQL Server every 10 minutes. Is there a quick
> > way to not send me an e-mail if there isn't data to look at?
> >
> > Thanks!
> > David|||This is by design from the perspective of RS. If the query used to generate
the notifications returns no rows then the report will not be run for that
instance of the subscription until the next time it fires and the query
returns data.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"david boardman" <davidboardman@.discussions.microsoft.com> wrote in message
news:7855BBDB-18BB-4191-86A1-778126B1D049@.microsoft.com...
>I guess I can post my interim solution for those looking to at least have
> something working. I just am looking for a more elegant/built in
> solution.
> For data-driven subscriptions, RS requires you to specify an SQL query
> which
> returns data to the subscription (things like what e-mails to send to, and
> what parameters to pass to the actual report, if any). I exec a stor proc
> in
> order to return this information back to my subscription. The key lies in
> the stor proc being called.
> The stor proc which feeds the data driven subscription ends up calling the
> stor proc which returns data for the report (which in reality is
> completely
> compartmentalized from the subscription itself). The subscription proc
> checks to see if at least 1 row of data is returned by the report proc.
> If
> the report stor proc returns no data, then the subscription stor proc
> returns
> a NULL SET back for the e-mail addresses to deliver the report to.
> I don't know if I'm capitalizing on a bug or not, but there is no error
> generated in the log files. I guess the report is actually generated
> (with
> no data) and delivered into never-never land.
> Hope I explained what I'm doing clearly, but just wanted people to know
> there is an interim solution, although some what cludgy.
> Cheers!
> "eralper" wrote:
>> Hi,
>> I have a similar data-driven subscription. The first query determines the
>> receivers for email, the second query prepares the email content for the
>> corresponding receiver.
>> So as Daniel wrote, first I had to eliminate no-data records then run the
>> query for the remaining set.
>>
>> "david boardman" wrote:
>> > I am wondering if there is a simple way to tell the report server to
>> > not
>> > deliver a report if there is no data in the report.
>> >
>> > I have several reports which have table layout to display a set of data
>> > IF
>> > there happens to be data. One example is that I have a polling process
>> > looking at certain tables in SQL Server every 10 minutes. Is there a
>> > quick
>> > way to not send me an e-mail if there isn't data to look at?
>> >
>> > Thanks!
>> > David