Send Report Email only if data exists

Question

Send Report Email only if data exists

AVB

SSChampion

Points: 11418
More actions
June 17, 2010 at 6:56 am

#222876

I have an invoice report that I want to email to multiple customers. Each customer will have a separate subscription. Problem is some customers may not have an invoice for that week. If they don't then the report shouldn't run and email them.
How can you dynamically control the execution/send of a report based on this?
I forgot to add I'm on RS 2000.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

nabidavid SSC Eights! Points: 815 More actions · Answer 1

You will need to use a data-driven subscription. If the query for the instructions for the data-drive subscription does not return a row, no report will be sent. Just base the SQL statement for the data-driven subscription off of the same data from your report.

You could even have a statement as simple as:

SELECT TOP (1)

1 AS [n]

FROM ReportData

WHERE ReportField = @ReportParameter

You could actually take your the SELECT statement from your report and replace it with a SELECT TOP(1) for the data-driven statement. As a result the report will send as long as it returns at least a single row.

Let me know if you have any questions.

Thanks,

Mike

Sent from my iSandwich

AVB SSChampion Points: 11418 More actions · Answer 2

Wow.. fast reply! Thanks.

It appears that data-driven subscriptions are only available for the Enterprise version. I am running 2000 Standard.

Is it possible to execute a report subscription through DTS?

AVB SSChampion Points: 11418 More actions · Answer 3

Just found this article:

http://www.sqlservercentral.com/articles/Development/datadrivensubscriptions/2432/

nabidavid SSC Eights! Points: 815 More actions · Answer 4

When you create a subscription, a matching SQL Agent Job is created, named with the GUID of the subscription. I do not have access to SQL Server 2000 Reporting Services, but in later versions the dbo.Subscriptions table matched to the dbo.Catalog table in the ReportServer database will aid in matching the report to the corresponding Agent Job. The SubscriptionID is generally the GUID name of the SQL Agent Job.

Here is an example:

SELECT*

FROM [ReportServer].[dbo].[Subscriptions] AS S

INNER JOIN [ReportServer].[dbo].[Catalog] AS C

ON S.Report_OID = C.ItemID

Once you have the name of the Agent Job, you can execute it from T-SQL using "sp_start_job" (although the command may be different in SQL 2000, not sure).

😉

nabidavid SSC Eights! Points: 815 More actions · Answer 5

AVB (6/17/2010)
It appears that data-driven subscriptions are only available for the Enterprise version. I am running 2000 Standard.

Oh... and my bad for forgetting to mention in the first place the data-driven subscriptions are only available in Enterprise Edition (or higher). That is true regardless of the SQL Server 2000, 2005, 2008, or 2008 R2.

😉

AVB SSChampion Points: 11418 More actions · Answer 6

I was wondering that if it created an agent job. I am familiar with executing DTS packages from procedures so hopefully it'll be pretty easy!

Thanks again your help is much appreciated.

AVB SSChampion Points: 11418 More actions · Answer 7

I wanted to add that you need to look in [ReportingServices].[dbo].ReportSchedule for the ScheduleId. That is the Agent GUID for the job.

SELECT c.Name as ReportName,rs.ScheduleID as AgentGUID

FROM [ReportServer].[dbo].[Subscriptions] AS S

INNER JOIN [ReportServer].[dbo].[Catalog] AS C ON S.Report_OID = C.ItemID

JOIN [Reportserver].[dbo].[ReportSchedule] rs on s.subscriptionid = rs.subscriptionid