SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


format the number in indian currency format


format the number in indian currency format

Author
Message
vinayak.v
vinayak.v
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1114 Visits: 157
hi.... to all..

i'm using sql server 2008 express with advance edition with BIDS...

i've developed one report in that employee salary..

i want to print the salary in the format (12,34,56,78,91,01,11,233.89)

and i want to print in words

such as (" ninty nine crores fifty one lakhs fourty nine thousand four hundred and fourty five rupees and seventy nine paise ")

i.e in crores and lakhs (indian currency format)...

please help me....
steveb.
steveb.
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25378 Visits: 7195
To change the currency format in a report you can change the language of the report to reflect your target language.

I am not sure what the indian currency format would be but there are most major language groups in there so have a look.

As for changing number to print words, you will have to do this manually either through nested replace statements in the query or textboc property, or even better would be to have a cacluated column or view on you source sql that does the conversion,
vinayak.v
vinayak.v
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1114 Visits: 157
ya.. i've tried that but there's not there ..
indian currency format....
Lowell
Lowell
SSC Guru
SSC Guru (182K reputation)SSC Guru (182K reputation)SSC Guru (182K reputation)SSC Guru (182K reputation)SSC Guru (182K reputation)SSC Guru (182K reputation)SSC Guru (182K reputation)SSC Guru (182K reputation)

Group: General Forum Members
Points: 182223 Visits: 41569
I saw you've been making the same request for a long time. I had shown you in another thread on the same issue an english number-to-words example that you could edit, but you never followed up on that.

here's a TVF that returns the formatted value via TSQL; dunno about reporting services; i guess you could make a similar vb function:

CREATE FUNCTION ITVFFormatIndianCurrency
(
@val Decimal(24,2)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH MyCTE AS (SELECT CONVERT(VARCHAR(50),@val) AS strVal )
SELECT
CASE
WHEN len(strVal) <= 6 THEN strVal
WHEN len(strVal) <= 8 THEN REVERSE( STUFF(REVERSE((strVal)),7,0,','))
WHEN len(strVal) <= 10 THEN REVERSE( STUFF(STUFF(REVERSE((strVal)),7,0,','),10,0,','))
WHEN len(strVal) <= 12 THEN REVERSE( STUFF(STUFF(STUFF(REVERSE((strVal)),7,0,','),10,0,','),13,0,','))
WHEN len(strVal) <= 14 THEN REVERSE( STUFF(STUFF(STUFF(STUFF(REVERSE((strVal)),7,0,','),10,0,','),13,0,','),16,0,','))
WHEN len(strVal) <= 16 THEN REVERSE( STUFF(STUFF(STUFF(STUFF(STUFF(REVERSE((strVal)),7,0,','),10,0,','),13,0,','),16,0,','),19,0,','))
WHEN len(strVal) <= 18 THEN REVERSE( STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(REVERSE((strVal)),7,0,','),10,0,','),13,0,','),16,0,','),19,0,','),22,0,','))
WHEN len(strVal) > 18 THEN REVERSE(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(REVERSE((strVal)),7,0,','),10,0,','),13,0,','),16,0,','),19,0,','),22,0,','),25,0,','))
END As Val,len(strVal) As TheLen
From myCTE
GO

WITH MyCTE AS
(SELECT 12345678910111233.89 As TheVal UNION ALL
SELECT 2345678910111233.89 UNION ALL
SELECT 345678910111233.89 UNION ALL
SELECT 45678910111233.89 UNION ALL
SELECT 5678910111233.89 UNION ALL
SELECT 678910111233.89 UNION ALL
SELECT 78910111233.89 UNION ALL
SELECT 8910111233.89 UNION ALL
SELECT 910111233.89 UNION ALL
SELECT 10111233.89 UNION ALL
SELECT 7111233.89 UNION ALL
SELECT 111233.89 UNION ALL
SELECT 11233.89 UNION ALL
SELECT 1233.89 UNION ALL
SELECT 233.89 UNION ALL
SELECT 33.89 UNION ALL
SELECT 3.89
)
SELECT *
FROM myCTE
CROSS APPLY dbo.ITVFFormatIndianCurrency(TheVal) MyAlias




Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
dwain.c
dwain.c
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43943 Visits: 6431
Lowell's approach will be faster but here's one that is universal:

A SQL-Based Universal Currency Formatter


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
vinayak.v
vinayak.v
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1114 Visits: 157
Hi thanks for the reply

I got the solution ..

u can check here

[url=http://vinayak-blog.blogspot.in/2011/07/indian-currency-format-for-rdl-or-rdlc.html][/url]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search