1

My datasource is XML (sharepoint list). I basically want to show the date formatted if the value in the field is a date and if not then show "NA". For some reason, even when the data is a a string it is still trying to convert it to a date somewhere. Here is my code..

=IIF
(
ISDATE(replace(First(Fields!ows_Manufacturing_Date.Value, "DataSet1"),"datetime;#","")),
formatdatetime(replace(First(Fields!ows_Manufacturing_Date.Value, "DataSet1"),"datetime;#",""),2),
replace(First(Fields!ows_Manufacturing_Date.Value, "DataSet1"),"string;#","")
)
pb2q
  • 58,613
  • 19
  • 146
  • 147
Paul Riker
  • 785
  • 2
  • 10
  • 30

1 Answers1

2

The problem is that the IIF statement in SSRS doesn't short circuit, it always evaluates both conditions, so even if the field is not a date, it still tries to do the formatdatetime function.

(See this: SSRS iif function question)

Instead of the IIF function, try using the SWITCH function instead:

=SWITCH(First(Fields!ows_Manufacturing_Date.Value, "DataSet1")="string;#NA",
"NA",
First(Fields!ows_Manufacturing_Date.Value, "DataSet1")<>"string;#NA",
formatdatetime(replace(First(Fields!ows_Manufacturing_Date.Value, "DataSet1"),"datetime;#",""),2))
Community
  • 1
  • 1
PaulStock
  • 11,053
  • 9
  • 49
  • 52
  • took me 3 hours try to understand why the formatDateTime still executes when if condition if false until I find your post – ZERO May 23 '12 at 20:34