Home > Home, MSDN, SSRS > SSRS: Live Exchange Rate Conversion

SSRS: Live Exchange Rate Conversion

Let Say we have Report with USD Amount and Local Currency and user want to have a report with Local currency amount as per the live exchange rate.

Live exchange rate can get on web using XML or RSS feed.

XML file : needs XML query to retrieve the data

RSS feed : no need to write the query only execute it in the dataset without query

Lets use RSS feed , on web I got one click which provide the RSS feed live data for exchange rate.:-

RSS Feed:- http://www.sloomedia.com/currency/feeds/USD.xml

Lets go to the report Shared Data Source Connection:-

Create XML DataSource as shown below:-


Go to next tab, Credentials:-


Set to Windows Credentials.

Now create Shared Dataset as shown below:-



Now go to the query designer and execute without writing query as shown below:-


Note this data-set fields will not come automatically. As shown in below screen:-


Need to add manually whatever fields name showing in the query designer after executing it. I have added manually as shown below:-


After this lets create the Report with two datasets:-

One dataset having database table data which is the actual data to show.

Second dataset having above created shared dataset.

First Data Set sample query:-

select 50000 SalesAmount, ‘USD’ currencycode ,’INR’ localCurrency

select 2000 SalesAmount, ‘USD’ currencycode,’AUD’localCurrency

Fields coming for dataset 1 are shown below:-


Now Create another dataset as dataset2  from shared dataset created above using XML data as shown below:-


Now come to report design and drag tablix control from toolbox and assign dataset name as dataset1 .


Now run the report as output look like below:-


Now We need to create a column which will show SalesAmount in local currency.

To understand better , i created 3 columns

1st column will show what is  the exchange rate returned from dataset2

2nd column will show what is the amount per 1 USD in local currency  derived from first column

3rd column is final output of the salesamount calculated in localcurrency.  multiplication of Salesamount * 2nd column.



Above Expressions:-

“Exchange Rate” column expression:-

=Lookup(Fields!localCurrency.Value,MID(Fields!title.Value,instr(Fields!title.Value,”=”)+2,3),Fields!title.Value, “DataSet2”)

“Exchange Amount With 1 Dollar” column expression:-


“SalesAmount in Local” Column expression:-

=Fields!SalesAmount.Value  * ReportItems!Textbox12.Value


Now in below report, we should show what was the last updated currency rate we got from web:-


Expression is :- =”Currency Rate Last Updated: “+First(Fields!pubDate.Value, “DataSet2”)


Now run the report:-



Every time refreshed data will show in the “salesamount in local” column.




Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools


Categories: Home, MSDN, SSRS Tags: ,
  1. April 24, 2016 at 1:51 am

    Excellent info on using RSS feed as datasource.

  2. April 19, 2017 at 2:24 pm

    I had use MarketXLS. It works great for me.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: