Click here to monitor SSC
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
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
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....
steveb.
steveb.
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2848 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
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 157
ya.. i've tried that but there's not there ..
indian currency format....
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14918 Visits: 38908
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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4239 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
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
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]
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