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 

SQL Server, Get Business Days Function

We have a database for functions so they generic ones can be shared across multiple databases.  This is one I created for business days.

First you need to create a holiday table or non-shipping days table and put your companies dates in it.  I went ahead and put around 10 years of dates.  Most dates are easy to figure out if you go look up the holiday on the web.

Once you have this table you are ready for your function, below is the code..

USE [PTNFunctions]

GO

/****** Object:  UserDefinedFunction [dbo].[fn_GetExpectedShipDate]

        Script Date: 11/09/2012 13:49:11 ******/

--select ptnfunctions.dbo.fn_GetBusinessDay('11/27/2012', -3)

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

alter function [dbo].[fn_GetBusinessDay]

(

    @StartDate DATETIME,

    @NumberOfBusinessDays INT = 1

)

returns DATETIME

AS

BEGIN

DECLARE @BusinessDate DATETIME

DECLARE @BusinessDayCount INT

DECLARE @IncrementalDay INT

    SELECT @BusinessDayCount = 0

    SELECT @BusinessDate = @StartDate

    IF @NumberOfBusinessDays > 0

    BEGIN

        SELECT @IncrementalDay = 1

    END

    ELSE IF ( @NumberOfBusinessDays < 0 )

    BEGIN

        SELECT @IncrementalDay = -1

    END

    SELECT @NumberOfBusinessDays = Abs(@NumberOfBusinessDays)

    ----------------------------------------------------

    --add or subtract days to the current date based off number 

    --of business days, may work out this way

    ----------------------------------------------------

    WHILE (@BusinessDayCount < @NumberOfBusinessDays)

    BEGIN

        SELECT @BusinessDate = @BusinessDate + @IncrementalDay

        IF(datename(weekday,@BusinessDate) != 'Saturday' 

            AND datename(weekday,@BusinessDate) != 'Sunday')

        BEGIN

            IF(NOT EXISTS(SELECT * FROM 

                            Holidays (nolock) 

                            WHERE HolidayDate=

                                    convert(DATETIME,Convert(nvarchar(20),@BusinessDate,101))))

            BEGIN 

                SELECT @BusinessDayCount = @BusinessDayCount + 1

            END

        END

    END

return @BusinessDate

end

The power of this is in the use when you need to get back a select statement based off the number of business days like this..

 

SELECT 

    * 

FROM 

    yourTableHere cm (nolock)

WHERE 

    cm.yourFieldHere=3

    AND cm.CREATEDDATE >= ptnfunctions.dbo.fn_GetBusinessDay(cm.CREATEDDATE, 3)

Or you need to use it in the select part to return number of business days for a sql statement, or report..

SELECT 

    ptnfunctions.dbo.fn_GetBusinessDay(cm.CREATEDDATE, 3) AS '3BusinessDays', * 

FROM 

    yourTableHere cm (nolock)

WHERE 

    cm.yourFieldHere = 1

Dynamics Ax multiselect grid

For reference: http://www.axaptapedia.com/index.php?title=Multiple_grid_selections

I wanted to test this out here are the steps to produce with a twist thrown in for Active on the datasource.

1. Create a new form, called TestMultiSelect
2. copy CustTable to the data source
3. under design,
   1. add a grid,
      1. on grid copy "Invoice" field group to the grid
   2. add a button
      1. on the button select Multiselect = Yes
4. code for the buttons clicked event see Figure 1
5. on active code override of the datasource custtable put code for figure 2
6. That is it, now you have a multiselect button and a way while form is loaded to check things like should the button be lit up or not in the active method.

   1: void clicked()

   2: {

   3:  

   4:     custTable   currentCustTable;

   5:     ;

   6:     super();

   7:  

   8:     for (currentCustTable = custTable_ds.getFirst(true) ?

   9:                                     custTable_ds.getFirst(true) :

  10:                                     custTable_ds.cursor(); currentCustTable; currentCustTable = custTable_ds.getnext())

  11:     {

  12:         info(currentCustTable.InvoiceAccount);

  13:     }

  14:  

  15: }

Figure 1

 

   1: public int active()

   2: {

   3:     int ret;

   4:     CustTable currentCustTable;

   5:  

   6:     ret = super();

   7:     

   8:     for (currentCustTable = custTable_ds.getFirst(true) ?

   9:                                     custTable_ds.getFirst(true) :

  10:                                     custTable_ds.cursor(); currentCustTable; currentCustTable = custTable_ds.getnext())

  11:     {

  12:         info(currentCustTable.InvoiceAccount);

  13:     }

  14:  

  15:  

  16:     return ret;

  17: }

Figure 2