"Any ideas?" is the most frequently-asked question in technical forums. My answer is: yes.

A Pedestrian Piece of Code Goes Walkabout

Somebody asked how to convert numbers to words on one of the Reporting Services forums today, and I was interested enough to look at a bunch of VB functions, pick one out, and adapt it for reports.  This is something that people often need for checkprinting, so it seemed worthwhile to look into it.

There are two callable functions in my version of this code: one that you can use with Integers and one for currency, which depends on the Integer functionality for its work.  You can change the regular expression pattern for a different number of decimal places, or pass a different separator than the default ".", if your currency is different.

The original code (cited in the comment in my version, below) is not great, and my version probably isn't great either, although I did fix numerous spacing issues and so on that had nothing to do with updating for .NET.  But I think this adaptation serves to illustrate a number of general principles that tend to trip people up when they're bringing old VB functions into RS in particular and .NET in general. 

The general .NET points are obvious : use honest-to-god RETURN statements in your functions, explicitly type your variables,  use StringBuilder instead of concatenating strings, and so on.  The RS-specific items are a little more subtle, and I decided to post this note to call out two in particular:

  • You notice that I am using some System.Text classes (StringBuilder and Regular Expression handling), and that they are explicitly qualified. People get confused by the fact that you can't add Imports statements into an RDL's custom code. It's possible to add Assembly references to the RDL (check the Report Properties dialog, there is a tab for this), but I personally find it a pain in the neck.  If the classes will be found at run time (especially if, as here, they are part of the base .NET Framework set) and if you explicitly qualify your class names as I have done here, you don't need to worry about assembly references and you don't feel the lack of an Imports statement capability in custom code.

  • If you compare my version withi the original -- it's almost unrecognizable, actually -- one glaring difference is that I've pulled the lists of words representing numeric parts (such as "Billion") into SHARED arrays of strings, declared at the top, rather setting them up within any function body.  There is absolutely no reason to recreate these lists for every invocation of the two callable functions during a report run.

Humdrum as this little bit of code is -- and you will probably be able to make it better -- these are still principles worth living by in an RS-code universe.

Speaking of RS-code universes, I'm getting ready to dive back into one.  C and I have pulled up stakes and started new work; you'll see a bit of difference here on our site, and I will probably be publishing some articles under the auspices of EC|Wise as well as here on Spacefold.  But my general interests will continue to be BI- and Reporting- related, and my posts here will continue.  I'll also get back to the forums, now that we're (almost) out of boxes.

Happy holidays, everyone.

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})?$",   _
 PUBLIC Function ExpandPrice(pPrice AS String, _
        Optional pSeparator AS String = ".") _
        AS String      
    Dim temp AS New System.Text.StringBuilder()
    If  Not expr.IsMatch(pPrice) Then
       ' temp.Append(pPrice) or whatever you want to do here
       Dim parts AS String() = pPrice.Split(pSeparator)
       Dim dollars AS String = parts(0)
       Dim cents AS String = parts(1)
       If CDbl(dollars) > 1 Then
          temp.Append(ExpandIntegerNumber(dollars) & "Dollars ")
          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
 PUBLIC 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
           End If
        End If   
        j  += 1
    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))
       RETURN units(Right(pNumber, 1))
    End If
 End Function