Post reply

SSRS Null Dates with nested IIF Statements.

  • 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, " "))

  • February 19, 2014 at 9:19 am

    #1690347

    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"))

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

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