Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

format the number in indian currency format Expand / Collapse
Author
Message
Posted Wednesday, November 03, 2010 12:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 11:21 PM
Points: 230, 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....
Post #1015036
Posted Wednesday, November 03, 2010 4:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, March 15, 2014 4:31 AM
Points: 2,808, Visits: 7,173
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,
Post #1015110
Posted Wednesday, November 03, 2010 5:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 11:21 PM
Points: 230, Visits: 157
ya.. i've tried that but there's not there ..
indian currency format....
Post #1015127
Posted Wednesday, November 03, 2010 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 12,744, Visits: 31,071
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1015160
Posted Tuesday, February 12, 2013 8:03 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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!
Post #1419274
Posted Tuesday, February 12, 2013 9:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 11:21 PM
Points: 230, 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]
Post #1419286
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse