SSRS, How to avoid sending out a report when the dataset is empty

 

A great reference but I do not think they had this solution was http://blogs.msdn.com/b/bimusings/archive/2005/07/29/445080.aspx

and

http://stackoverflow.com/questions/6498271/send-report-only-when-the-attachment-has-data-in-ssrs

My solution is closer to the second reference but goes a little further.  I created a stored procedure with an extra parameter called @UseErrorAtEnd and if that is given as a 1 then it will error out if the dataset is empty which causes the email to NOT be sent.  In my report I change the parameter for @UseErrorAtEnd to default to =false and I set it to hidden for users that are using the report.  On the subscription I set it to true if I want no email on empty dataset or I set it to false if I want an email on an empty dataset.   

-- =============================================
-- Author:        Larry Bellou
-- Create date: 11/15/2012
-- Description:    
-- =============================================
create PROCEDURE [dbo].[RPT_CreditDebitByStatus]
(
    @StatusID INT,
    @UseErrorAtEnd BIT = 0
)
AS
BEGIN
            SELECT 
                cdm.CREDITDEBITMEMOID, 
                cdm.CREDITDEBITMEMOSTATUS,
                cdm.InvoiceAccount,
                cdm.REASONITEMID,
                Sum(cdl.lineamount) AS 'TotalAmount'
             FROM axdb_live.dbo.PTNCREDITDEBITMEMOTABLE cdm(nolock),
                    axdb_live.dbo.PTNCREDITDEBITMEMOLINE cdl (nolock)
            WHERE 1=1
                AND cdm.CREDITDEBITMEMOID = cdl.CREDITDEBITMEMOID
                AND CREDITDEBITMEMOSTATUS = @StatusID
            GROUP BY  cdm.CREDITDEBITMEMOID, cdm.CREDITDEBITMEMOSTATUS, cdm.CREDITDEBITMEMOTYPE,
                        cdm.InvoiceAccount, cdm.REASONITEMID
END 

IF (@@ROWCOUNT = 0 AND @UseErrorAtEnd = 1)
BEGIN
    RAISERROR('No data', 16, 1)
END