November 3, 2010 at 12:05 am
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....
November 3, 2010 at 4:44 am
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,
November 3, 2010 at 5:13 am
ya.. i've tried that but there's not there ..
indian currency format....
November 3, 2010 at 6:33 am
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
February 12, 2013 at 8:03 pm
Lowell's approach will be faster but here's one that is universal:
A SQL-Based Universal Currency Formatter[/url]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 12, 2013 at 9:40 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply