Mail SSRS report
hai, I want to create an on error table. I want to show this table in a ssrs-report. I want to mail this ssrs-report, when there is data in the table. It is possible that there is no data in the table, then the ssrs-reportis empty and I don't want to mail it. Is that possible?
October 20th, 2010 2:18pm
Depending on what edition of SQL Server you're using you can create a Data-driven subscription. The query in the data driven subscription would then return a single row (possibly containing the count of errors) that would act as an indicator that there is data to send. The data-driven subscription will send one report per row returned by the query (hence why you only want to return one row if there are any errors). You would then setup the subscription similar to how you would set up any subscription and have your error report (based on a different query containing the error details) sent to you or any other recipient on whatever schedule meets your needs. Dave
October 20th, 2010 3:02pm
When configuring a data driven subscription, you must provide a query which returns subscriber data: Most of the time this query simply returns rows from a table which lists your data driven subscription users and their preferences around delivery and parameter values for the report in question. Each row of data returned equals one report we'll deliver as part of the subscription. Just modify this query so that it ALSO filters the result based on whether or not the report itself will return rows. For example: SELECT * from DataDrivenSubscriptionInfoTable WHERE EXISTS(SELECT SomeField FROM TheDataSourceTable WHERE DateField Between DateAdd(dd,-3,GetDate()) and GetDate()) If you provide this query to the wizard, it will only return subscribers when there is data you wish to report on (records in the TheDataSourceTable table that have a date within the last 3 days) Thanks . Rajkumar Yelugu
October 20th, 2010 3:30pm
http://blogs.msdn.com/b/bimusings/archive/2005/07/29/445080.aspx Thanks .Rajkumar Yelugu
October 20th, 2010 3:33pm
Tnx. I will test this week.
October 20th, 2010 4:50pm
You don't have to return subscriber information. Typically that's what you do if you want to "burst" reports to a list of recipients. However, in a case such as this where I believe the requirement is to send a single email, you can simply return one row which acts as an indicator that there is data to send. One report will be generated and the recipient can be a hard-coded email address. You can get fancy and persist some kind of an "Email Error Recipient" address somewhere that can be returned in the data-driven subscription query so that the value is not hard-coded but that is not at all necessary.
October 20th, 2010 5:37pm