none
Datevalue in Switch statement gives Error for certain columns

    Question

  • Busy making a report (in Reportdesigner 3.0 using a Report Model as source) that in one column should show a datediff. This is a switch statement, I have 3 statuses and 2 date columns. Status 10 should return nothing as both datetime columns are NULL for these rows. Status 20 has 1 column filled in with a date, then I need the datediff between it and today, and status 40 has both columns filled in and then I need datediff today and the 2nd column. This worked, but then it was found that I needed to use DateValue, as everything of yesterday should give 1 day difference when the report is run today, and not only after 24hours have passed. Since I added DateValue to this switch statement I get weird errors ?! Hope you guys can help as I'm afraid it's a bug ??

    Anyway full statement I had in the end is this:

    =Switch(Fields!Ship_Stock_Status.Value="10", " ",
    Fields!Ship_Stock_Status.Value="20", DateDiff("d", Iif(IsDate(Fields!Mvin_Move_Date_Time.Value), DateValue(Today()), DateValue(Fields!Mvin_Move_Date_Time.Value)), DateValue(Today())),
    Fields!Ship_Stock_Status.Value="40", DateDiff("d", Iif(IsDate(Fields!Disc_Move_Date_Time.Value), DateValue(Today()), DateValue(Fields!Disc_Move_Date_Time.Value)), DateValue(Today())) )

    But after seeing the IsDate() even didn't solve the bug, I started to experiment with simpler statements to get the error and understand it:

    =Switch(Fields!Ship_Stock_Status.Value="10", " ",
    Fields!Ship_Stock_Status.Value="20", Fields!Mvin_Move_Date_Time.Value,
    Fields!Ship_Stock_Status.Value="40", Fields!Disc_Move_Date_Time.Value)

    This statement works. Status 10 returns an empty value, others return correct date columns (which are all filled in correctly)

    =Switch(Fields!Ship_Stock_Status.Value="10", " ",
    Fields!Ship_Stock_Status.Value="20", DateValue(Fields!Mvin_Move_Date_Time.Value),
    Fields!Ship_Stock_Status.Value="40", DateValue(Fields!Disc_Move_Date_Time.Value))

    This is where it starts to go wrong. I added DateValue(), and now I only get valid dates for status 40 ?!  All rows with status 10 or 20 give me #Error ?

    The weird thing is when I do DateValue() for only one column, the error shifts:

    =Switch(Fields!Ship_Stock_Status.Value="10", " ",
    Fields!Ship_Stock_Status.Value="20", CStr(DateValue(Fields!Mvin_Move_Date_Time.Value)),
    Fields!Ship_Stock_Status.Value="40", "aa")

    This will return correct values for status 20 (date) and 40 (aa) and only status 10 rows will show #Error ??

    Moving the order of the switch statement (so first 40, then 20 and 10) does not change anything ?

    I get the impression that when I add DateValue() for one column, reportserver will try to generate this value for all rows, even those where the switch statement does not apply to and where that row is NULL. So a datevalue for the datetime column that is only filled in with status 40 results in #Error for all other rows where this value is Null, even when the switch statement doesn't want to show these but another value) ??  in the example with only datevalue for status 20, status 40 works as for those rows the date column is filled in, and only fails for status 10 as there Mvin_Move_Date_Time.Value is NULL ?

    Looks like a bug to me ? Any ideas how to make a workaround ? I've tried to rewrite the switch statement but I'm always stuck when I do this DateValue() function.. also can't leave datevalue out and just add 1 day as entries of today should correctly show 0 days difference..

    Wednesday, March 09, 2011 12:59 PM

Answers

  • Found a workaround using the DateSerial function, as this doesn't seem to have a problem handling NULL values in dates..

    =Switch(Fields!Ship_Stock_Status.Value="40", datediff("d", DateSerial(DatePart("yyyy", Fields!Mvin_Move_Date_Time.Value), DatePart("m", Fields!Mvin_Move_Date_Time.Value), DatePart("d", Fields!Disc_Move_Date_Time.Value)), Today()), Fields!Ship_Stock_Status.Value="20", datediff("d", DateSerial(DatePart("yyyy", Fields!Mvin_Move_Date_Time.Value), DatePart("m", Fields!Mvin_Move_Date_Time.Value), DatePart("d", Fields!Mvin_Move_Date_Time.Value)), Today()), Fields!Ship_Stock_Status.Value="10", " ")

    Thursday, March 10, 2011 1:47 PM