One of my friend is working in SSRS and he got the
requirement to print the total sales amount in the report. To fulfill this
requirement we have created a user-defined function to convert the total amount
in the words.
Creating function using below script
|
CREATE FUNCTION [dbo].[udf_NumberToEnglishWords]
(
@InNumericValue NUMERIC(38,2)
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @NumericDetails TABLE
(
Number INT
,Word VARCHAR(20)
)
INSERT INTO @NumericDetails VALUES
(1,'One'),(2,'Two'),( 3,'Three'),(4,'Four'),( 5,'Five'),
(6,'Six'),(7,'Seven'),(8,'Eight'),(9,'Nine'),(10,'Ten'),
(11,'Eleven'),(12,'Twelve'),(13,'Thirteen'),(14,'Fourteen'),
(15,'Fifteen'),(16,'Sixteen'),(17,'Seventeen'),(18,'Eighteen'),
(19,'Nineteen'),(20,'Twenty'),(30,'Thirty'),(40,'Forty'),
(50,'Fifty'),(60,'Sixty'),(70,'Seventy'),(80,'Eighty'),(90,'Ninety')
DECLARE @DigitDetails TABLE
(
DigitPlace INT
,PlaceName VARCHAR(30)
)
INSERT INTO @DigitDetails VALUES
(1,'Hundred'),(2,'Thousand'),(4,'Billion'),(6,'Quadrillion')
,(8,'Sextillion'),(10,'Octillion'),(12,'Decillion'),(14,'DuoDecillion')
,(16,'QuattuorDecillion'),(17,'QuinDecillion'),(19,'SepDecillion'),(21,'NovemDecillion')
,(3,'Million'),(5,'Trillion'),(7,'Quintillion'),(9,'Septillion')
,(11,'Nonillion'),(13,'UnDecillion'),(15,'TreDecillion'),(18,'SexDecillion')
,(20,'OctoDecillion'),(22,'Vigintillion')
DECLARE @StrNumber VARCHAR(60)
,@LargeNumber
VARCHAR(60)
,@SmallNumber
VARCHAR(10)
,@DecimalNumber
VARCHAR(10)
,@Chunk
VARCHAR(50)
,@ChunkVal
INT
,@TenthVal
INT
,@UnitVal
INT
,@HundVal
INT
,@StrFinal
VARCHAR(1000)
,@StrLength
INT
,@DigitLoop
INT
SET @DecimalNumber = ''
SET @HundVal = 0
SET @StrFinal = ''
SET @DigitLoop = 2
SET @StrNumber = ABS(@InNumericValue)
SET @StrNumber = SUBSTRING(@StrNumber,1,CASE
WHEN CHARINDEX('.',@StrNumber)=0 THEN LEN(@StrNumber)
ELSE
CHARINDEX('.', @StrNumber)-1 END)
SET @StrLength = LEN(@StrNumber)
IF(@StrLength > 3)
BEGIN
-- SET @StrFinal = RIGHT(@StrNumber,3)
SET @LargeNumber = SUBSTRING(@StrNumber,-2,@StrLength)
SET @StrLength = LEN(@StrNumber)
WHILE LEN(@LargeNumber) > 0
BEGIN
SET @Chunk = ''
SET @HundVal = 0
SET @ChunkVal = CAST(RIGHT(@LargeNumber,3) AS INT)
IF (@ChunkVal >= 100)
BEGIN
SET @HundVal = @ChunkVal/100
-- SET @HundVal = CAST(LEFT(@LargeNumber,1) AS INT)
SELECT @Chunk = WORD FROM @NumericDetails
WHERE NUMBER = @HundVal
SET @Chunk = @Chunk + ' ' + 'Hundred'
-- SET @StrFinal = @Chunk + @StrFinal
SET @ChunkVal = @ChunkVal%100
END
IF (@ChunkVal > 20)
BEGIN
SET @TenthVal = (@ChunkVal/10)*10
SET @UnitVal = @ChunkVal%10
SELECT @Chunk=@Chunk +' '+ WORD FROM @NumericDetails
WHERE NUMBER = @TenthVal
SELECT @Chunk=@Chunk +' '+ WORD FROM @NumericDetails
WHERE NUMBER = @UnitVal
SELECT @Chunk=@Chunk+' '+PlaceName FROM @DigitDetails
WHERE DigitPlace = @DigitLoop
SET @StrFinal = @Chunk + ', ' + @StrFinal
SET @StrLength =LEN(@LargeNumber)
SET @LargeNumber=SUBSTRING(@LargeNumber,-2,@StrLength)
SET @DigitLoop = @DigitLoop + 1
END
ELSE
BEGIN
SELECT @Chunk=@Chunk +' '+ WORD FROM @NumericDetails
WHERE NUMBER = @ChunkVal
SELECT @Chunk=@Chunk+' '+PlaceName FROM @DigitDetails
WHERE DigitPlace = @DigitLoop
IF(@ChunkVal > 0 OR @HundVal > 0)
SET @StrFinal = @Chunk + ', ' + @StrFinal
SET @StrLength =LEN(@LargeNumber)
SET @LargeNumber=SUBSTRING(@LargeNumber,-2,@StrLength)
SET @DigitLoop = @DigitLoop + 1
END
END
SET @StrLength = 3
SET @StrFinal = RTRIM(@StrFinal)
SET @StrFinal = STUFF(@StrFinal,LEN(@StrFinal),1,'')
END
IF(@StrLength = 3)
BEGIN
SET @Chunk = ''
SET @SmallNumber = RIGHT(@StrNumber,3)
SET @ChunkVal = CAST(LEFT(@SmallNumber,1) AS INT)
IF (@ChunkVal > 0)
BEGIN
SELECT @Chunk=Word+' '+'Hundred' FROM @NumericDetails
WHERE NUMBER = @ChunkVal
IF(@StrFinal <> '')
SET @StrFinal = @StrFinal +', ' + @Chunk
ELSE
SET @StrFinal = @Chunk
END
SET @StrLength = 2
END
IF(@StrLength < 3 AND @StrLength > 0)
BEGIN
SET @Chunk = ''
SET @SmallNumber = RIGHT(@StrNumber,2)
SET @ChunkVal = CAST(LEFT(@SmallNumber,2) AS INT)
IF (@ChunkVal > 20)
BEGIN
SET @TenthVal = (@ChunkVal/10)*10
SET @UnitVal = @ChunkVal%10
SELECT @Chunk = WORD FROM @NumericDetails
WHERE NUMBER = @TenthVal
SELECT @Chunk=@Chunk+' '+WORD FROM @NumericDetails
WHERE NUMBER = @UnitVal
IF(@StrFinal <> '')
SET @StrFinal = @StrFinal +', ' + @Chunk
ELSE
SET @StrFinal = @Chunk
END
ELSE IF(@ChunkVal <= 20 AND @ChunkVal > 0)
BEGIN
SELECT @Chunk = WORD FROM @NumericDetails
WHERE NUMBER = @ChunkVal
IF(@StrFinal <> '')
SET @StrFinal = @StrFinal +', ' + @Chunk
ELSE
SET @StrFinal = @Chunk
END
END
SELECT @StrFinal = ISNULL(@StrFinal,'')
RETURN @StrFinal
END
|
Function has
been created.
Now I am
using this function in my sql script.
Here I am
using ‘AdventureWorksDW2012’
Database for this demo.
|
USE [AdventureWorksDW2012]
GO
select pc.EnglishProductCategoryName As Product_Category,
SUM(FIS.SalesAmount) AS Total_Sales_Amount,
dbo.udf_NumberToEnglishWords (SUM(FIS.SalesAmount)) AS
Total_Sales_Amount_In_Word,
SUM(FIS.TaxAmt) AS Total_TaxAmt,
dbo.udf_NumberToEnglishWords (SUM(FIS.TaxAmt)) AS Total_TaxAmt_In_Word,
SUM(FIS.Freight) AS Total_Freight,
dbo.udf_NumberToEnglishWords (SUM(FIS.Freight)) AS Total_Freight_In_Word
From FactInternetSales FIS
Inner join DimProduct P
on P.ProductKey=FIS.ProductKey
inner join DimProductSubcategory psc
on p.ProductSubcategoryKey=psc.ProductCategoryKey
Inner Join DimProductCategory pc
on pc.ProductCategoryKey=psc.ProductCategoryKey
Group by pc.EnglishProductCategoryName
UNION ALL
Select
'Total',
SUM(Total_SalesAmount) AS Total_SalesAmount,
dbo.udf_NumberToEnglishWords (SUM(x.Total_SalesAmount)) AS
Total_Sales_Amount_In_Word,
SUM(Total_TaxAmt) AS Total_TaxAmt,
dbo.udf_NumberToEnglishWords (SUM(x.Total_TaxAmt)) AS Total_TaxAmt_In_Word,
SUM(Total_Freight) AS Total_Freight,
dbo.udf_NumberToEnglishWords (SUM(x.Total_Freight)) AS Total_Freight_In_Word
FROM (
select pc.EnglishProductCategoryName As Product_Category,
SUM(FIS.SalesAmount) AS Total_SalesAmount,
SUM(FIS.TaxAmt) AS Total_TaxAmt,
SUM(FIS.Freight) AS Total_Freight
From FactInternetSales FIS
Inner join DimProduct P
on P.ProductKey=FIS.ProductKey
inner join DimProductSubcategory psc
on p.ProductSubcategoryKey=psc.ProductCategoryKey
Inner Join DimProductCategory pc
on pc.ProductCategoryKey=psc.ProductCategoryKey
Group by pc.EnglishProductCategoryName
)X
|
See the output
Result in Excel
Hope this helps you for the reporting.
