delivery rule, need to not send report when it contains no value

[nattyborneo]

Dear all,

in publication, there is function for delivery rule.
Deliver individual document when condition is met
then I choose deliver only when report contains data

I already put formula in the report, to supress report if no value. So it means if there is no value, report will be empty.

but somehow when I run publication.
These empty reports are still come with the not empty reports.

Any one has idea , how to only deliver not empty reports?
thx

rgds,
Nat

 

[KMX2]

I use some SQL code so that the report fails if there is no data to send.

Kevin.

 

[nattyborneo]

Hi Kelvin,

may you share the code?
how to make it fails?

rgds,
Nat

 

[KMX2]

Basically in the SQL code I put in:

update #ktemp
set TOUPDATE = 'Y'
where TOUPDATE = 'N'
if @@rowcount = 0 raiserror 31000 'no rows'

I create a temp table and add a column to update. If there is no data and therefore no update made the code errors out and stops the report from running.

Hope it helps.

Kevin.