A question that is often asked is how to convert a number to words in SSRS. For example 1 => One.
Let’s take a look at an example:
SELECT Sales.SalesPerson.SalesPersonID AS SalesPerson,
SUM(Sales.SalesOrderDetail.OrderQty * Sales.SalesOrderDetail.UnitPrice) AS Amount
FROM Sales.SalesOrderDetail
INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID
GROUP BY Sales.SalesPerson.SalesPersonID
For the sake of example, let us assume that we need to display sales person id along with the sales amount and the amount in words.
Listed below are the basic steps to create the report:
1. Create a SSRS project
2. Add new report the project
3. Add a data source in which database is pointed to adventureworks
4. Create a data set with above querys
5. Drag and drop a table to layout from the toolbox and drag and drop the fields to the table
The next step is to create a function to convert the numeric values into words.
Go to Report menu option and select report properties. Select the Code tab in the dialog box and copy and paste the following code.
‘ adapted from
‘ http://cc.msnscache.com/cache.aspx?q=72465960679242&mkt=en-US&lang=en US&w=577f5001&FORM=CVRE8
‘ drastically updated for .NET by LSN
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
pPrice = 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)
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
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
The above code is adapted from said web site, but I have mad some small changes to it.
Next, you have to call this function in your table list control of the report. You need to enter the following function call at the column in which you need to have your number displayed in words.
=Code.ExpandPrice(Fields!Amount.Value,”.”)
The report will be dispalyed as illustrated below:
