I'm using BIDS in Visual Studio 2008 against a 2008 R2 Reporting Services Server. I have a requirement to only send out a report if there are rows in the data. The customer said I could also send out an email that said there is data or there is not. I cant figure out how to do this through the dashboard. I cant put an expression in the email subject . If I could that would probably sort it. I could just do = count(field, dataset) in the subject. The other solution I have come up with is, via BIDS, to have the report page say "There were ZERO records for this report query" (and only that) if there is no data. I'd then have SiteScope query the snapshot URL, grep for the string, and then send out a custom email saying "No Data Today"....or vice versa. The next thing I will try is to just do the same thing via c# .NET that the sitescope program is doing. I've also just in the last few minutes browsed the delivery extensions, but, at this point. I'm not sure that it will do what I want. The other challenge here is that if I use Sitescope, I have to correctly anticipate the snapshot timestamp.......that looks like its going to be problematic. So I'll probably have to do it in .NET run the query from there, then send the email from there AND, darn, I'll probably still have to, somehow, programattically figure out the snapshot time so that I can put the link to the snapshot in the email. It'd be far easier to just access "the most recent" snapshot than to use that timestamp url syntax. It'd be a lot easier if there were built in options for doing this or that (in terms of delivery) based on the report output......but, so far, i have not found anything like this. brad

When you create a subscription there will be a Job created under SQL Server Agent. The Job will be executing the ReportServer.dbo.AddEvent stored procedure. You can edit the Job Step to have a condition to execute the procedure. So, if you include a condition saying that if there is no. of record is greater than one then execute the procedure. Hope this helps. You can refer the following link : http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/0c2eee43-71f1-444e-86fd-4ab778a46609/#7c6e04da-207d-41a7-b973-d36f15430dcb

Thanks, the only challenge here is that I feel better, even when I code perfectly!, if the user is receiving something either way. So IF , there is data, generate the email as normal. IF there is NO data then send an email with the subject "NO SOUP FOR YOU!" (or whatever). This way I'm not in the condition of something going wrong and I get the call from the user saying they have not received the report in 2 months and just wanted to check in...........then I find something screwed up in an unanticipated way. However, I'm new to this Reporting Services stuff and had no idea about the ReportServer.dbo.AddEvent stored procedure. So thanks! Thats very helpful. I'll use your link to see if i can get some feedback in to MS somewhere on this point. I think it should be a standard offering, I mean users only want to know if something is amiss.........they dont want to pore over the same boring data every day. So "Conditional Reporting/Reporting Delivery" I think there is a world of opportunity here.

AND, know any good books on programming Reporting Services using C# .NET? I have Safari Premium.

Hi Brad, SQL Server Reporting Services has ability to use dynamic subscription data that is retrieved from an external data source at run time. In this case, we can use the dynamic query to check if there has data. If not, we don't provider the recipients. Then the report won't be sent. For more information about Data-Driven subscription, please see: http://msdn.microsoft.com/en-us/library/ms159150.aspx In addition, to learn more about developing in SQL Server Reporting Services, I would suggest you starting from here: http://msdn.microsoft.com/en-us/library/cc282207.aspx If you have any more questions, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT

Hello Brad What I do is setup a data-driven subscription that will only return email addresses to the subscribers if there is data returned for the query. You could code your query into a parameterised stored procedure (the source of the report which you have just built and want to have an automatic email for) which you then use in another stored procedure to give a list of emails (if there is any data returned from you main report stored procedure). INSERT INTO @tmpTable -- create above in proc EXEC dbo.usp_R001_ReportData 'param1','param2', 1000 SELECT email_address FROM udf_ParameterStringToTable2( @EmailAddress, ';') -- multi-value string to table parser -- google for code or see below. WHERE EXISTS (SELECT TOP 1 field1 FROM @tmpTable); This will only return the addresses where there is data returned for the query. Cons: Runs data query twice - once in the email check and then once for the report. Pros: no agent job hacking, no .Net, pure SQL. ----------------- CREATE FUNCTION [dbo].[udf_ParameterStringToTable2]( @string varchar(max) , @sep char(1) ) RETURNS @tbl TABLE ( element varchar(150) ) AS BEGIN -- start by creating a temp numbers table, -- simply a list of numbers from 1 to 5,000 (actually 2 12) DECLARE @tmpNums AS TABLE (n int not null) DECLARE @max as int , @rc as int SET @max = 5000 SET @rc = 1 INSERT INTO @tmpNums VALUES(1) WHILE @rc * 2 <= @max BEGIN insert into @tmpNums select n + @rc from @tmpNums set @rc = @rc * 2 END INSERT INTO @Tbl SELECT substring(@string, n, charindex(@sep, @string + @sep, n) - n) as element FROM @tmpNums WHERE n <= DATALENGTH(@string + @sep) + 1 and substring(@sep + @string, n, 1) = @sep RETURN END