Convert Number to Words in SSRS

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:




Related Articles :

  • No Related Articles Found

2 Responses to “Convert Number to Words in SSRS”

  1. Hi,

    Look good but it give an error.

    There is an error on line 18 of custom code: [BC30648] String constants must end with a double quote.

    Please help me to sort out this error..

    you can mail me at ajay.v@sujataindia.com

    Thanks and Regards

  2. Sathish Kumar Sandra Reply August 30, 2011 at 8:52 am

    Hi,

    MY solution as sql function :

    Create FUNCTION exp2 ( @num INT )
    RETURNS VARCHAR(5000)
    AS BEGIN
    DECLARE @str1 VARCHAR(5000) ;
    DECLARE @str2 VARCHAR(5000) ;
    DECLARE @str3 VARCHAR(5000) ;
    DECLARE @str4 VARCHAR(5000) ;
    DECLARE @num1 INT ;
    DECLARE @num2 INT ;
    DECLARE @num3 INT ;
    SET @str1 = ‘, 1One , 2Two , 3Three , 4Four , 5Five , 6Six , 7Seven , 8Eight , 9Nine , 10Ten , 11Eleven , 12Tweleve , 13Thirteen , 14Fourteen ,15Fifteen , 16Sixteen , 17Seventeen, 18Eighteen, 19Ninteen ,’ ;
    SET @str2 = ‘ , , 2Twentey , 3Thirty , 4Forty , 5Fifty , 6Sixty , 7Seventy , 8Eighty , 9Ninty ,’ ;
    SET @str3 = ’1000000000Millon, 10000000Crore , 100000Lakh , 1000Thousand , 100Hundred , 1 ,’ ;
    SET @num1 = @num ;
    SET @num2 = 1000000000 ;
    SET @str4 = ” ;
    WHILE @num2 > 0
    BEGIN
    IF ( ( @num1 / @num2 ) > 0 )
    AND ( PATINDEX(‘%’ + STR(@num2, 10, 0) + ‘%’, @str3) > 0 )
    BEGIN
    SET @num = @num1 / @num2 ;
    SET @num3 = @num ;
    IF @num > 19
    BEGIN
    SET @num = @num3 / 10 ;
    SET @str4 = @str4 + SUBSTRING(@str2,
    PATINDEX(‘%’ + STR(@num, 2, 0) + ‘%’, @str2)
    + 2,
    ( CHARINDEX(‘,’, @str2, PATINDEX(‘%’ + STR(@num, 2, 0) + ‘%’, @str2)) )
    – ( PATINDEX(‘%’ + STR(@num, 2, 0) + ‘%’, @str2) + 2 )) ;
    SET @num = ( @num3 % 10 ) ;
    SET @str4 = @str4 + SUBSTRING(@str1,
    PATINDEX(‘%’ + STR(@num, 2, 0) + ‘%’, @str1)
    + 2,
    ( CHARINDEX(‘,’, @str1, PATINDEX(‘%’ + STR(@num, 2, 0) + ‘%’, @str1)) )
    – ( PATINDEX(‘%’ + STR(@num, 2, 0) + ‘%’, @str1) + 2 )) ;
    END
    ELSE
    BEGIN
    SET @str4 = @str4 + SUBSTRING(@str1,
    PATINDEX(‘%’ + STR(@num, 2, 0) + ‘%’, @str1)
    + 2,
    ( CHARINDEX(‘,’, @str1, PATINDEX(‘%’ + STR(@num, 2, 0) + ‘%’, @str1)) )
    – ( PATINDEX(‘%’ + STR(@num, 2, 0) + ‘%’, @str1) + 2 )) ;
    END
    SET @str4 = @str4 + SUBSTRING(@str3,
    PATINDEX(‘%’ + STR(@num2, 10, 0)
    + ‘%’, @str3) + 10,
    ( CHARINDEX(‘,’, @str3, PATINDEX(‘%’ + STR(@num2, 10, 0) + ‘%’, @str3)) )
    – ( PATINDEX(‘%’ + STR(@num2, 10, 0) + ‘%’, @str3)
    + 10 )) ;
    SET @num1 = @num1 % @num2 ;
    END
    SET @num2 = @num2 / 10 ;
    END
    RETURN @str4 ;
    END

    SELECT dbo.exp2(158777)+’ rupees ‘ +dbo.exp2(45)+’ Paise ‘

    Code in c# with same logic for conversion is
    public string ConvertNumtoWord(double number)
    String[] one = { “”, “one”, “two”, “three”, “four”, “Five”, “six”, “seven”, “eight”, “nine”, “ten”,”eleven”,”Tweleve”,”Thirteen”,”fourteen”,”fifteen”,”sixteen”,”seventeen”,”eighteen”,”ninteen” };
    String[] ten = { “”,””,”twentey”,”thirty”,”forty”,”fifty”,”sixty”,”seventy”,”eighty”,”ninty”};
    String[] hun = { “Crore”, “lakh”, “thousand”, “hundred”, “rupees”, “paise” };
    double[] num = { 10000000, 100000, 1000, 100, 1,0.01 };

    string ss = “”;

    textBox1.AutoCompleteCustomSource.Add(“one”);

    for (int i = 0; i 0)
    {
    float num1 = Convert.ToInt32(number / num[i]);
    if (num1 > 19)
    {
    ss = ss + ten[Convert.ToInt32(num1 / 10)] + ” “;
    ss = ss + one[Convert.ToInt32(num1 % 10)] + ” “;
    }
    else
    { ss = ss + one[Convert.ToInt32(num1)] + ” “; }
    ss = ss + hun[i]+ ” “;
    number = number % num[i];
    }

    }
    return ss;
    }

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |