Post reply

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

  • 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 ...

  • December 14, 2011 at 2:15 pm

    #1421617

    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]

  • December 14, 2011 at 2:23 pm

    #1421621

    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.

  • December 14, 2011 at 2:25 pm

    #1421623

    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

  • December 15, 2011 at 5:39 am

    #1421855

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

  • December 15, 2011 at 7:32 am

    #1421949

    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 AccountNumber FROM 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

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

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