CODEWITHSUNDEEP
sqlsql-serverreporting-servicesssrs-2012
4stars
4stars

Reputation: 99

How to pass nothing (empty value) to date time data type in RDL

How can I give empty value to date time data type in RDL. I am trying out the following but I am getting 01/01/00 to those row which doesn't have any data. 

 =iif(Fields!D_date.Value is nothing,Nothing,Fields!D_date.Value)

Even if I use 

 =iif(Fields!D_date.Value=0,Nothing,Fields!D_date.Value)

or

 =iif(Fields!D_date.Value="",Nothing,Fields!D_date.Value)

I am getting an error in my data rows.

I am passing '' into the stored procedure. If there is not data for that column.

This is the stored procedure I am using:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[uspDBLines]
    (@InLinesPerPage int)
AS
    DECLARE @TotalRows int
    DECLARE @Remainder int
    DECLARE @NumPages int
    DECLARE @NextPageRows int

    SET @TotalRows = 0

    SELECT 
        ROW_NUMBER() OVER (ORDER BY P_id) AS InvoiceRow,
        CusID, B_id, Inv_No, B_Desc, D_date, Qty, Size, Price, Amt
    INTO
        #tempInvoice
    FROM 
        Purchase_Details

    SET @TotalRows = @@ROWCOUNT

    IF @TotalRows = 0
    BEGIN
        WHILE @TotalRows < @InLinesPerPage -- Add Blank Rows will generate blank invoice.
        BEGIN
            SET @TotalRows = @TotalRows + 1

            INSERT INTO #tempInvoice (InvoiceRow, CusID, B_id, Inv_No, B_Desc, D_date, Qty, Size, Price, Amt)
            VALUES (@TotalRows, '', '', 0, '', '', 0, '', 0, 0)
        END
    END
    ELSE
    BEGIN
        SET @Remainder = @TotalRows % @InLinesPerPage -- get remainder

        IF @Remainder != 0 
        BEGIN
            -- Get the current page increase by 1 because we have a remainder.
            SET @NumPages = @TotalRows / @InLinesPerPage + 1 
            SET @NextPageRows = @NumPages * @InLinesPerPage

            WHILE @TotalRows < @NextPageRows -- Add Blank Rows
            BEGIN
                SET @TotalRows = @TotalRows + 1

                INSERT INTO #tempInvoice (InvoiceRow, CusID, B_id, Inv_No, B_Desc, D_date, Qty, Size, Price, Amt)
                VALUES (@TotalRows, '', '', 0, '', '', 0, '', 0, 0)
            END
        END
    END

    SELECT * 
    FROM #tempInvoice 
    ORDER BY InvoiceRow ASC

    RETURN

Upvotes: 0

Views: 718

Answers (1)

StevenWhite
StevenWhite

Reputation: 6034

The table may very well have a date like 1/1/1900 filled in for all the invalid dates. So the report is just displaying what is there since they're not NULL.

So try this expression:

=IIf(Fields!D_date.Value <= CDate("1/1/1900"), Nothing, Fields!D_date.Value)

If they really are NULL use this:

    =IIf(Fields!D_date.Value = Nothing, Nothing, Fields!D_date.Value)

SSRS doesn't use the is operator to compare with Nothing.

Upvotes: 1

Related Questions