CODEWITHSUNDEEP
ssrs-2008
George Rippeto
George Rippeto

Reputation: 13

SSRS #Error in Column With Mixed Text and Numeric Values

I have a SSRS report that has different data types in the same column of a matrix report. When I display the data without formatting all data is displayed. However I want to have comma separators in whole numbers, 3 decimal places for the smaller values and display the values as is if they are text.

The whole numbers and decimal values display correctly but any cell that has text in it displays a #error. Here is how the values are formatted.

=IIF(InSTR(Fields!KPIValue.Value, ".") > 0, FORMAT(CDbl(Fields!KPIValue.Value), "0.000"), 
     IIF(InSTR(Fields!KPIValue.Value, "/") > 0, Fields!KPIValue.Value, FORMAT(CInt(Fields!KPIValue.Value), "###,###,###,##0")))

I am using this code in the value expression of the detail data area of the report. Here is a sample of the data with and without formatting.

Unformatted   Formatted

17398549      17,398,549

94/04/28      #Error

00/03/34      #Error

00/00         #Error

1.067         1.067

0.829         0.829

0.000         0.000

24020         24,020

Any assistance is resolving this issue would be appreciated.

Upvotes: 1

Views: 3717

Answers (1)

Ian Preston
Ian Preston

Reputation: 39586

Due to SSRS not short-circuiting on IIf statements, you're getting errors as all parts of your expression are formatted, and some get data type errors.

To solve this and achieve your goal you'll need to do this in report Custom Code. Create a function like:

Function FormatValue (KPIValue As String) As String
  If IsNumeric(KPIValue) And InStr(KPIValue, ".") > 1 Then
    FormatValue = Format(CDbl(KPIValue), "0.000")
  Else If IsNumeric(KPIValue) Then
    FormatValue = Format(CInt(KPIValue), "###,###,###,##0")
  Else
    FormatValue = KPIValue
  End If
End Function

Which you can then call in your report like:

=Code.KPIValue(Fields!KPIValue.Value)

Worked fine for me on your data:

enter image description here

You'll note in the Custom Code I moved your logic around slightly; this made sense to me but go with whatever works for you, and on your complete data set.

Upvotes: 3

Related Questions