Reporting Services: Value Either too Large or too Small for a Decimal

  • Article

I have seen a few reports recently that have been upgraded from SSRS 2005 to SSRS 2008 where a ‘Value either too Large or too small for a decimal’ appears in the chart under certain circumstances.  This typically manifest itself when an expression in the data series for the chart looks something like that below:

 =IIf(Fields!ISMONEYAMOUNTSW.Value = 0 and Fields!TIMEINSECONDS.Value > 0 , 
    Sum(Fields!TIMEINSECONDS.Value), 
    Iif(Fields!ISMONEYAMOUNTSW.Value = 1 and Fields!TOTALAMOUNT.Value > 0,
        SUM(Fields!TOTALAMOUNT.Value),""))

Notice the empty string in the last expression as part of the FalsePart of the IIf expression.  This also happens if ‘Nothing’ is used.  These values, “” or ‘Nothing’, don’t evaluate to decimals in this case and the rendering engine has trouble converting them.  In the previous version I imagine this was ignored.  In SQL Reporting Services 2008 the value attempts to convert to the decimal type unsuccessfully.  The fix is simply to change the expression to a ‘0’ so that it is evaluated properly as shown below.

 =Iif(Fields!ISMONEYAMOUNTSW.Value = 0 And Fields!TIMEINSECONDS.Value > 0 , 
    Sum(Fields!TIMEINSECONDS.Value), 
    Iif(Fields!ISMONEYAMOUNTSW.Value = 1 And Fields!TOTALAMOUNT.Value > 0,
        SUM(Fields!TOTALAMOUNT.Value),0))