SSRS Expression #ERROR In Field


  • Please see attached sample.  I'm using Visual Studio 2008, and report services.  The four columns to your right of the "invoice amt" use the following expressions based on the "Due Date" and "invoice Amt".  As you can see from the graphic example, the sum total for "Not Due Yet" and "0-30 Days" (as well as the other two columns for which you can't see the #ERROR) have and #ERROR.  It seems to be happening only if one of the values from the "Invoice Amt" is not included in the other fields which they correctly should not be.  If there are no values at all in the  "Not Due Yet" and "0-30 Days" or any of the other two columns, the expression correctly displays $0.00 as the Sum.

    My question is why the #ERROR in those first 2 cases and how do I fix my code to correctly display the SUM result for the values that should appear in each column?

    Thank you for your response and here are my expressions;

    Not Due Yet
    =SUM(iif(datediff("d",fields!duedate.Value,now())<= 0,fields!valuehome_1.Value,0))

    0 - 30 Days
    =SUM(iif(datediff("d",fields!duedate.Value,now())> 0
    and (datediff("d",fields!duedate.Value,now())< 31),fields!valuehome_1.Value,0))

    31 - 60 Days
    =SUM(iif(datediff("d",fields!duedate.Value,now())> 30
    and (datediff("d",fields!duedate.Value,now())< 60),fields!valuehome_1.Value,0))

    > 60 Days
    =SUM(iif(datediff("d",fields!duedate.Value,now())> 60,fields!valuehome_1.Value,0))SSRS Expression #ERROR In Field
    Wednesday, August 28, 2013 10:42 PM

All replies

  • Hello,

    According the error list in the screen shot your post above, you receive the following error: uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

    If you are using version of SQL Server 2008? Sometimes a #ERROR instead of the value expected is caused by an invalid type being sent through a format expression.

    I recommend you try to use the expression below:
    =SUM(IIF(DateDiff("d",Fields!duedate.Value,Now())<= 0,Fields!valuehome_1.Value,0.0))

    Hope this help. If you have any problem, please feel free to let me know.

    Alisa Tang

    Thursday, August 29, 2013 12:18 PM
  • Thanks Alisa for the response, but that did not work.  I think I tried that once before too.  The thing is individual records are based on the same expressions but not summing.  It's not like I'm summing the records directly above the subtotals.  I'm using the expression that gives me the detail with a sum function wrapped around it.  Is there a way to sum the detail in the expressions above the subtotals (if I'm saying this right)?
    Friday, August 30, 2013 4:41 AM
  • I have found that putting the SUM inside the iif can resolve the #ERROR issue.  I know it seems counterintuitive to put the SUM inside the iif statement, but it does work.

    =iif(datediff("d",fields!duedate.Value,now())<= 0,SUM(fields!valuehome_1.Value),0)

    Martina White

    Monday, September 23, 2013 10:39 PM
  • Thanks Martina, it solved my problem

    Friday, October 27, 2017 7:06 AM