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:-

1

Go to next tab, Credentials:-

1

Set to Windows Credentials.

Now create Shared Dataset as shown below:-

ExchangeDataset

 

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

QueryDesigner

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

SharedDataSetFields

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

ManualAddedFields

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
UNION

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

Fields coming for dataset 1 are shown below:-

1

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

ExchangeDataSetinReport

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

Table

Now run the report as output look like below:-

Reportoutput

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.

 

Expressions

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:-

=mid(ReportItems!Textbox6.Value,14,8)

“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:-

Last

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

 

Now run the report:-

output

 

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

 

Thanks

Prasad

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

 

Advertisements
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: