[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3386
  • Last Modified:

Convert currency to words in Reporting Services

Hello experts!
I need a custom code function for Reporting Services that will convert a currency amount to spelled out words for a report I am generating that will print checks.  The currency amounts are the sum of multiple fees in a group that are detailed in the statement on the top portion of the check.  Does anyone know of a function I can put into Custom Code or a Reporting Services function that will convert the report amount to the words?

Thanks,

Susan
0
suzygm71478
Asked:
suzygm71478
1 Solution
 
shorakCommented:
Try these sites as they have code which you can use as a custom function in your report. Just add the code to the Code tab of the report properties and call the relevant function by using the expression '=code.function()' replacing function() with the name of your function

http://www.vbexplorer.com/VBExplorer/tips/src36.asp
http://vbcorner.blogspot.com/2007/10/convert-number-currency-to-word-part-i.html
http://www.vbforums.com/showthread.php?t=192290

Hope this helps

Rob

0
 
lifegoodCommented:
We can use this Codes to create User Defined Function.
REport Body-Rt Clk-Properties-Codes
==================================================
Function NumberToString(ByVal num As Double) As _
    String
        ' Remove any fractional part.
    num = Int(num)

    ' If the number is 0, return zero.
    If num = 0 Then Return "zero"

    Static groups() As String = {"", "thousand", "million", _
        "billion", "trillion", "quadrillion", "?", "??", _
        "???", "????"}
    Dim result As String = ""

    ' Process the groups, smallest first.
    Dim quotient As Double
    Dim remainder As Integer
    Dim group_num As Integer = 0
    Do While num > 0
        ' Get the next group of three digits.
        quotient = Int(num / 1000)
        remainder = CInt(num - quotient * 1000)
        num = quotient

        ' Convert the group into words.
        result = GroupToWords(remainder) & _
            " " & groups(group_num) & ", " & _
            result

        ' Get ready for the next group.
        group_num += 1
    Loop

    ' Remove the trailing ", ".
    If result.EndsWith(", ") Then
        result = result.Substring(0, result.Length - 2)
    End If
    Return result.Trim()  
End Function


Function GroupToWords(ByVal num As Integer) As _
    String
    Static one_to_nineteen() As String = {"zero", "one", _
        "two", "three", "four", "five", "six", "seven", _
        "eight", "nine", "ten", "eleven", "twelve", _
        "thirteen", "fourteen", "fifteen", "sixteen", _
        "seventeen", "eightteen", "nineteen"}
    Static multiples_of_ten() As String = {"twenty", _
        "thirty", "forty", "fifty", "sixty", "seventy", _
        "eighty", "ninety"}

    ' If the number is 0, return an empty string.
    If num = 0 Then Return ""

    ' Handle the hundreds digit.
    Dim digit As Integer
    Dim result As String = ""
    If num > 99 Then
        digit = num \ 100
        num = num Mod 100
        result = one_to_nineteen(digit) & " hundred"
    End If

    ' If num = 0, we have hundreds only.
    If num = 0 Then Return result.Trim()

    ' See if the rest is less than 20.
    If num < 20 Then
        ' Look up the correct name.
        result &= " " & one_to_nineteen(num)
    Else
        ' Handle the tens digit.
        digit = num \ 10
        num = num Mod 10
        result &= " " & multiples_of_ten(digit - 2)

        ' Handle the final digit.
        If num > 0 Then
            result &= " " & one_to_nineteen(num)
        End If
    End If

    Return result.Trim()
End Function

++++++++++++++++++++++++++++++++++++++++=
Call the function
=code.numbertostring(100)
 will print
one Hundred
I tried this
Sabu Varghese
1

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now