Tuesday, January 13, 2009

Convert Numeric Values into Words

This is very common question that you can find in SSRS forums as many users needs to display numeric data in words. Although there is a function in Crystal Reports, there is no function in SSRS. Nevertheless, you can do this in SSRS by writing your own function.
Lets us do this by an example using the following query:
SELECT Sales.SalesPerson.SalesPersonID AS SalesPerson,SUM(Sales.SalesOrderDetail.OrderQty * Sales.SalesOrderDetail.UnitPrice) AS AmountFROM Sales.SalesOrderDetailINNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDINNER JOIN Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonIDGROUP BY Sales.SalesPerson.SalesPersonID
Follow the below steps to create a 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 the above query.
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 numeric values into words.
Go to the Report menu option and select report properties. Select the Code tab in that dialog box and copy and paste the following 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 StringpPrice = 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 adopted from web site in the code snippet.Next, you have to call this function in your table. You need to enter the following function call at the column in which you need to have your number in word.

=Code.ExpandPrice(Fields!Amount.Value,".")

Finally you will see following screen.



No comments:

Post a Comment