Report Builder 3.0 #Error with Date Calcs

Question

I am using Report Builder 3.0.  In one of my columns, I'm pulling a date from a cube.  A few entries come back with a value of "Unknown".  I'm trying to use the following formula to replace "Unknown" with a blank space.

=Iif(Fields!Test_Rep_Recd_Date.Value="Unknown","",cdate(Fields!Test_Rep_Recd_Date.Value))

I've also tried:

=Iif(IsDate(Fields!Test_Rep_Recd_Date.Value),cdate(Fields!Test_Rep_Recd_Date.Value),"")

Both formulas come back with "#Error" if the value returned from the cube is "Unknown".  If the value is a date, it converts it to a date which lets me change the format of the date.

Any ideas as to why these formulas would return "#Error" instead of the value specified in the IIF statement?

Answer 1

Hi There

Thanks for your posting. Please try this expression, it might resolve your problem

=iif(Fields!Test_Rep_Recd_Date.value = "Unknown", "",

formatdatetime(iif(IsDate(Fields!Test_Rep_Recd_Date.value) ="1",Fields!Test_Rep_Recd_Date.Value,"01/01/1900"),dateformat.ShortDate  ))

=iif(Fields!Test_Rep_Recd_Date.value = "Unknown", "",
formatdatetime(iif(IsDate(Fields!Test_Rep_Recd_Date.value) ="1",Fields!Test_Rep_Recd_Date.Value,"01/01/1900"),dateformat.ShortDate  ))

A screenshot for your help

If you have any query please let me know

Many thanks

Syed Qazafi Anjum

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 2

Thanks!!!  That works.