How to only send report if there is data records in report ?

Question

How to only send report if there is data records in report ?

mark_copley

Ten Centuries

Points: 1126
More actions
December 14, 2011 at 12:17 pm

#261605

I have developed a report that pulls out transactions of money transferred for that business day. I added the report to a nightly subscription so that the report goes out every weekday. I don't like the fact that the if there are no records for that day it sends a blank excel file so
I would like to enhance this report now so that when there are no records to pull for that day that the report does not run. I have done this before in 2005 but can't remember how I did it. Looking for a little help ...

Viewing 6 posts - 1 through 5 (of 5 total)

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

okbangas SSChampion Points: 11773 More actions · Answer 1

First thing that comes into my mind, a data driven subscription where you have a query that checks for any reconds that would be included in your report.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 2

Personally I prefer the NoRowsMessage property to indicate that the report ran but does not contain any data. This way you don't get calls asking why the report did not come out on a particular day. Everyone will know the report worked, but that there was simply nothing to report on that day.

Bill-89778 Ten Centuries Points: 1201 More actions · Answer 3

Hi Mark,

This will do the trick for you:

If Not Exists (Select ....)

Begin

RAISERROR ('Aborting report because no data was found.', 16, 1)

End

Else

Begin

Select ....

End

I found this in another post some time ago, so I can't take credit for it. But, it works very well for me.

Bill

mark_copley Ten Centuries Points: 1126 More actions · Answer 4

THanks for the info will give it a shot today and let you know how it works

mark_copley Ten Centuries Points: 1126 More actions · Answer 5

It did do the trick thanks appreciate it: so now when the subscription tries to run the report I get an error and no email is sent

to users.

(code used)

IF NOT EXISTS (SELECT AccountNumberFROM BkToOReallocHistory WHERE Tradedate =@rptDate)

-- no recs found aborting report

BEGIN

RAISERROR ('Aborting report because no data was found.', 16, 1)

-- exit proc

RETURN

END

ELSE

-- recs found pull report data

BEGIN

-- select records to put into report

SELECT

AccountNumber

,OwnerSSN

,BeneSSN

,FundOption

,GrossAmt

,Basisamt

,GainLossAmt

,CONVERT(date,Tradedate)

,GainLossInd

,Reverseflag

,ExchangeRollFlag

,CONVERT(date,CreatedDate)

FROM

BkToOReallocHistory

WHERE

Tradedate =@rptDate

END