locked
Report Builder 3.0 #Error with Date Calcs RRS feed

  • 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?

    Thursday, August 16, 2012 2:59 PM

Answers

  • 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.

    • Proposed as answer by Syed Qazafi Anjum Monday, August 20, 2012 9:05 PM
    • Marked as answer by johns900 Tuesday, August 21, 2012 1:26 PM
    Friday, August 17, 2012 3:01 AM

All replies

  • 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.

    • Proposed as answer by Syed Qazafi Anjum Monday, August 20, 2012 9:05 PM
    • Marked as answer by johns900 Tuesday, August 21, 2012 1:26 PM
    Friday, August 17, 2012 3:01 AM
  • Thanks!!!  That works. 
    Tuesday, August 21, 2012 1:26 PM