We have a requirement to generate the report that reports having multiple currencies with a currency symbol.
The report looks like as below
We have
written the SP which returns this result set.
We are using
the AdventureWorksDW2017 database
for the demo.
Below is the
SP
Create PROCEDURE [dbo].[FactInternetSales_rpt] AS BEGIN select replace(dp.EnglishProductName,'''','') as ProductName ,N'₹'+ cast(SUM(fis.SalesAmount) as nvarchar(50)) as
SalesAmount_Indian_rupee ,N'$'+ cast(SUM(fis.SalesAmount)*0.013 as nvarchar(50)) as SalesAmount_USD_dollar ,N'$'+ cast(SUM(fis.SalesAmount)*0.017 as nvarchar(50)) as
SalesAmount_Canadian_dollar ,N'€'+ cast(SUM(fis.SalesAmount)*0.011 as nvarchar(50)) as
SalesAmount_European_euro ,N'¥'+ cast(SUM(fis.SalesAmount)*0.087 as nvarchar(50)) as
SalesAmount_Chinese_Yuan_Renminbi ,N'₨'+ cast(SUM(fis.SalesAmount)*2.12 as nvarchar(50)) as
SalesAmount_Pakistani_rupee from FactInternetSales fis join DimProduct dp on dp.ProductKey=fis.ProductKey group by dp.EnglishProductName
END; |
Script to insert the currency code andsymbol Read here:
https://bageshkumarbagi-msbi.blogspot.com/2021/06/sql-script-to-insert-world-currency.html
Taking
Source as OLEDB Source and selecting Data Access Mode as Sql command text
Calling the SP to Generate the Resultset.
We can see the data after clicking on the preview button.
Click ok.
Now taking the Destination as Excel and creating the connection manager.
Selecting the Data access mode as Table and clicking on the
New button to create the table in the excel.
By default are getting the below tale structure.
CREATE TABLE `Excel Destination` ( `ProductName` LongText, `SalesAmount_Indian_rupee` LongText, `SalesAmount_USD_dollar`
LongText,
`SalesAmount_Canadian_dollar` LongText, `SalesAmount_European_euro`
LongText,
`SalesAmount_Chinese_Yuan_Renminbi` LongText,
`SalesAmount_Pakistani_rupee` LongText ) |
Now we need to make the changes in the data type and table
name as below.
CREATE TABLE `FactInternetSales_Report` ( `ProductName` LongText, `SalesAmount_Indian_rupee`
nvarchar(100), `SalesAmount_USD_dollar`
nvarchar(100),
`SalesAmount_Canadian_dollar` nvarchar(100), `SalesAmount_European_euro`
nvarchar(100),
`SalesAmount_Chinese_Yuan_Renminbi` nvarchar(100),
`SalesAmount_Pakistani_rupee` nvarchar(100) ) |
Click ok.
Selecting
table name.
Mapping the
columns.
Click ok.
Now package
is ready to run.
Running this package.
Report is generated successfully.
See the report.
Get the
expected result.