SSRS Null Dates with nested IIF Statements.

Question

Post reply

SSRS Null Dates with nested IIF Statements.

SandyC

SSC Enthusiast

Points: 185
More actions
January 30, 2014 at 10:36 am

#285069

Hello,

I have been searching and hope that I am not posting a question that has been answered already, but this combo of isNothing/Dates/dateadd/IIF statements I have not really seen yet.

First I will post my expression (which gets a #error when I run the report - the date fields are either populated with dates or spaces, handled in the sp. This is a nested if and maybe I should be doing a SWITCH or maybe handling in SP, but I would like to handle it in SSRS, if possible. A date comparison to see what is not populated and to figure out which one to use, Sweep (first) or (if empty) Actual or (if empty) Estimated. And if Actual then add 180 days and if Actual not populated then use Estimated and add 180 days, if all empty, then nothing. If Actual or Estimated are used, then add the letter 'e' to the end. Thank you, (sorry so long)!

=IIF(IsNothing(Fields!DeadlineDate.Value) AND

IsNothing(Fields!ActualClosingDate.Value) AND

IsNothing(Fields!EstimatedClosingDate.Value)," "," "

IIF(IsNothing(Fields!DeadlineDate.Value) AND

isNothing(Fields!ActualClosingDate.Value) AND

NOT isNothing(Fields!EstimatedClosingDate.Value),

DateAdd("d",180,Fields!EstimatedClosingDate.Value) & "e" ," " )

IIF(IsNothing(Fields!SweepDate.Value) AND

NOT isNothing(Fields!ActualClosingDate.Value),

DateAdd("d",180,Fields!ActualClosingDate.Value) & "e" ," " )

IIF(NOT isNothing(Fields!SweepDate.Value),Fields!SweepDate.Value, " "))

Viewing 2 posts - 1 through 1 (of 1 total)

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

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 1

Here is my take on this with the sample data below

DECLARE @TestData TABLE



    (

      EntryNumber INT ,

      SweepDate DATE ,

      ActualDate DATE ,

      EstimatedDate DATE

    )

INSERT  INTO @TestData

        ( EntryNumber, SweepDate, ActualDate, EstimatedDate )

VALUES  ( 1, '2014-01-19', '2014-01-20', '2014-01-21' ),

        ( 2, NULL, '2014-02-20', '2014-02-21' ),

        ( 3, NULL, NULL, '2014-03-20' ),

        ( 4, NULL, NULL, NULL )

SELECT  *

FROM @TestData

Using the following expression in a SSRS matrix I get the following results which I think does what you ask (See attached image of output)

=switch(isdate(Fields!SweepDate.value) = TRUE,format(Fields!SweepDate.Value,"yyyy-MM-dd"),



		isdate(Fields!ActualDate.Value) = TRUE,format(dateadd("d",180,Fields!ActualDate.Value),"yyyy-mm-dd E"),

isdate(Fields!EstimatedDate.Value) = TRUE,format(dateadd("d",180,Fields!EstimatedDate.Value),"yyyy-mm-dd E"))