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.
' http://cc.msnscache.com/cache.aspx?q=72465960679242&mkt=en-
US&lang=en-US&w=577f5001&FORM=CVRE8
' drastically updated for .NET by LSN
{"Thousand ", "Million ", "Billion ", "Trillion ", _
"Quadrillion ", "Quintillion ", "Sextillion "}
{"","One ", "Two ", "Three ", "Four ", "Five ", _
"Six ", "Seven ", "Eight ", "Nine "}
{"Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", _
"Seventy ", "Eighty ", "Ninety "}
{"Ten ","Eleven ", "Twelve ", "Thirteen ", "Fourteen ", _
"Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen"}
System.Text.RegularExpressions.Regex("^-?\d+(\.\d{2})?$", _
System.Text.RegularExpressions.RegexOptions.None)
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
Else
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
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