|
|
You're in the
sql server reporting services
group:Data driven subscription: if no report data => do not deliver
sql server reporting services:
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!
Currently 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. [quoted text, click to view] "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. [quoted text, click to view] "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!
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! [quoted text, click to view] "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!
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. [quoted text, click to view] "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
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |