SSRS Data-Driven email only when query returns data

We are trying to develop integrity report to keep tables clean.

For example:

To: mail@mail.com From: sender@sender.com Subject: Individuals that have multiple primary email address

Body:

We were expecting a way to conditionally send reports bases on results count. Example: when resultset > 1 send email to mail@mail.com else dont send email.

We have found work arounds of forcing an error. But this seems like it would be a fairly standard practice. Does SSRS offer a native ability to do this?

There is no native way to prevent a blank report from being sent unless you have the Enterprise Edition with Data Driven Subscriptions.

With a Data Driven subscription, you would make the Data Driven query based with a count of your records and add it in the Dataset section by pressing the Dataset button.

SELECT TOP 1 'mail@mail.com' [TO], 'sender@sender.com' [FROM], 'Individuals that have multiple primary email address' [SUBJECT] ,  [ID]
FROM <TABLE/VIEW/CTE...>
GROUP BY [ID]
HAVING COUNT(*) > 2

Then match the fields up with the email fields for the data driven query.

If there are no matches for ones with more than 2 primary email addresses, there will be zero rows of data and the report won't be generated.

There are a few work arounds that you might be able to use if you do not have the Enterprise Edition.

One is to add an error like you mentioned.

Another is to send a report that says there are no duplicates today.

The way to work smoother is to add a one-time subscription and trigger it with an SP that runs daily with SQL Server Agent. The SP would count your records and use an IF to trigger the report subscription.

DECLARE @RESULT_SET INT = (SELECT COUNT(*) FROM ...)

IF @RESULT_SET > 1
    EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = 'b8ed8dbc-49b2-40ba-b21a-123456789ABC'

Where EventData is the Subscription ID.