locked
Convert Amount into Words in SSRS Report RRS feed

  • Question

  • Hi,

    I tried this code in Reporting Services 2014 Report Designer but I got an error:

    There is an error on line 29 of custom code: [BC30311] Value of type '1-dimensional array of String' cannot be converted to 'String'.

    Here is the code:

    SHARED suffixes AS String() = _
     {“Thousand “, “Million “, “Billion “, “Trillion “, _
     “Quadrillion “, “Quintillion “, “Sextillion “}
    SHARED units AS String() = _
     {“”,”One “, “Two “, “Three “, “Four “, “Five “, _
     “Six “, “Seven “, “Eight “, “Nine “}
    SHARED tens AS String() = _
     {“Twenty “, “Thirty “, “Forty “, “Fifty “, “Sixty “, _
     “Seventy “, “Eighty “, “Ninety “}
    SHARED digits AS String() = _
     {“Ten “,”Eleven “, “Twelve “, “Thirteen “, “Fourteen “, _
     “Fifteen “, “Sixteen “, “Seventeen “, “Eighteen “, “Nineteen”}
    SHARED expr AS NEW _
    System.Text.RegularExpressions.Regex(“^-?d+(.d{2})?$”, _
    System.Text.RegularExpressions.RegexOptions.None)
    Public Function ExpandPrice(Price AS Double, Optional pSeparator AS String = “.”) AS String
    Dim pPrice  As String = FORMAT(Price,“##############.00”)
    Dim temp AS New System.Text.StringBuilder()
     If Not expr.IsMatch(pPrice) Then
     ‘ temp.Append(pPrice) or whatever you want to do here
    Else
      Dim parts AS String = pPrice.Split(pSeparator)
      Dim dollars AS String = parts(0)
      Dim cents AS String = parts(1)
    End If
     If CDbl(dollars) > 1 Then
      temp.Append(ExpandIntegerNumber(dollars) & “Dollars “)
    End If
     If CInt(cents) > 0 Then
      temp.Append(“And “)
     End If
    ElseIf CDbl(dollars) = 0 Then
      temp.Append(ExpandIntegerNumber(dollars) & “Zero Dollars “)
    If CInt(cents) >= 0 Then
      temp.Append(“And “)
     End If
    ElseIf CDbl(dollars) = 1 Then
    temp.Append(ExpandIntegerNumber(dollars) & “Dollar ” )
    End If
    If CDbl(cents) > 1 Then
    temp.Append(ExpandIntegerNumber(cents) & “Cents”)
    ElseIf CDbl(cents) = 0 Then
    temp.Append(ExpandIntegerNumber(cents) & “Zero Cents “)
    ElseIf CDbl(cents) = 1 Then
    temp.Append(ExpandIntegerNumber(cents) & “Cent ” )
    End If
    End If
    RETURN temp.ToString()
    End Function
    Function ExpandIntegerNumber(pNumberStr AS String) AS String
    Dim temp2 AS New System.Text.StringBuilder()
    Dim number AS String() = _
    StrDup(3 – Len(pNumberStr) Mod 3, “0″) & pNumberStr
    Dim i AS Integer, j AS Integer = -1
    Dim numPart AS String()
    For i = Len(number) – 2 To 1 Step -3
    numPart = Mid(number, i, 3)
    If Clng(numPart > 0) Then
    If j > -1 Then
    temp2.Insert(0,suffixes(j),1)
    End If
    End If
    temp2.Insert(0,GetNumberUnder1000Str(numPart),1)
    j += 1
    Next
    RETURN temp2.ToString()
    End Function
    Function GetNumberUnder1000Str(pNumber AS String) AS String
    Dim temp1 AS New System.Text.StringBuilder()
    If Len(pNumber) = 3 Then
    If CLng(Left(pNumber, 1)) > 0 Then
    temp1.Append(GetNumberUnder100Str(Left(pNumber, 1)) & “Hundred “)
    End If
    End If
    temp1.Append(GetNumberUnder100Str(Right(“0″ & pNumber, 2)))
    RETURN temp1.ToString()
    End Function
    Function GetNumberUnder100Str(pNumber AS String) AS String
    If pNumber > 19 Then
    RETURN tens(Left(pNumber, 1) – 2) & units(Right(pNumber, 1))
    ElseIF pNumber >= 10 and pNumber <= 19 Then
    RETURN digits(Right(pNumber, 1))
    Else
    RETURN units(Right(pNumber, 1))
    End If
    End Function

    Friday, January 8, 2016 9:26 AM

Answers