Reporting Services: how to configure reports to be emailed ONLY when there is data returned?

I have a report created using MS SQL Server 2005 reporting services and deployed to the report server. I added a subscription for the report to be scheduled to run and emailed to someone on a regular basis. But now we want the report emailed ONLY when there is data returned. If no data is returned after the report runs then don't email it.
Does anyone know how to do that?

Thanks!

It sounds like you want a variant of a Data Driven Subscription. You will need the Enterprise version of SSRS to use Data Driven Subscriptions. Here is a tutorial on building one:

http://msdn2.microsoft.com/en-us/library/ms169673.aspx

The change that you will make to the standard script is in the Define a query to retrieve subscriber data section. You will just add to the standard query a where clause that excludes users where the associated report returns no data. You will need to know the source of the data and get the rows returned and such.

I came accross this article by Jason Selburg which claims to allow you to build your own data driven subscriptions using Standard Edition:

http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp

and his follow up:

http://www.sqlservercentral.com/columnists/jselburg/2824.asp

Good luck! Let me know how it turns out.

Larry Smithmier

|||

Please follow the following steps:

1. Go to Data Driven Subscription

2. When prompted to enter the query for subscription, enter something is a following manner:

SELECT DISTINCT 'email@.emailAddress.com' as emailAddress

FROM(

...enter your exact report query here,

make sure there are no parameters in it (@.parameterName)...

) as xx

3. When asked to enter emailAddress use email address from the database field

4. Set the time and finish

This should get you started on this.

-Rohit

|||Thanks Larry I'm trying it out. But how come the "New Data Driven Subscription" option is not enabled when I right click the Subscription folder.|||

oh yes it's standard edition. Let me try the other way. Is there a way to send the report from cmd line or sproc? Thanks!

|||

Yes, you can run reports from the command line using the rs command (documentation) as described in the following SQLJunkies article:

http://sqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk

which doesn't seem to be up for me so here is the Google cache link:

http://64.233.167.104/search?q=cache:XvoyQYWNs50Jqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk&hl=en&gl=us&strip=1

Larry Smithmier|||

Thanks a lot Larry! I'll look into that also. I was also thinking about creating a process to send reports and trigger the process to send reports only when there is data returned. Do you know any article which talks about this?

Anyway, I'll let you know finally on which I chose and how it turns out even though that may take some time.|||

No articles that I have found speak directly about this. I will try and get one up sometime soon. The process will rely heavily on the fact that you get to define a query to return users for a data drive subscription. Make the query not return any users if there is no data to be displayed on the report. You would just need to embed the report's query into the subscription query.

Larry Smithmier

|||

Larry,

My approach is like this: add a subscription for the report to be emailed to an email address and schedule the report to run once. Query the datbase table on report server to get the ScheduleID. And then create a sp like this:

CREATE PROC p_RunJobOnlyWhenThereIsData

AS

exact report query here

...

if @.rowcount > 0

begin

exec msdb..sp_start_job @.job_name = 'D7BADF75-A9BC-487B-B874-2BEF2F4A7E0A'

end

GO

Then create a SQL Server agent job to run this script (sp) once a week. So if there is data returned the report will run and be emailed to the right person and if there is no data returned the report will not run based on the condition set in the sp.

The only problem is that the sp runs on report server while the report query runs againt a database on a different server, so when I execute the report query against a different server by using a four part name in the source table it says can't find the server in sys.server. Is there other way around this other than adding linked server?

My case is a little different and easier than the case mentioned in the article as I only need to email the report to one person.

Thanks a lot for that article which helped a lot and gave me a lot of ideas!|||

You can get around the four part name by using an OPENQUERY but I believe that you will still need to add a linked server.

Larry Smithmier

|||

Thanks for the reply Larry!

I added linked server and linked server login but still cannot run the query on the report server.

I used sqladmin to log in to report server; sqladmin is also a login acct on the remote db server. I can use sqladmin as a windows acct to login to the db server and report server directly; but when I execute the query on report server against database on db server I got the following messagae:

OLE DB provider "SQLNCLI" for linked server "xxx" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0

TCP Provider: An existing connection was forcibly closed by the remote host.

Msg 18456, Level 14, State 1, Line 0

Login failed for user 'xxx\sqladmin'.

Thanks!