Disclaimer: This article is completely based on my learning experience and your suggestions are most welcome. I hope, this article will be useful for those consultants who are new to SAP HANA.

Summary

This article explains in detail how you can perform currency conversion in SAP HANA.

Author: Prakash Kumar Sahu

Company: Cognizant Technology Solutions(Pune/India)

Author Bio:

Prakash Kumar Sahu is a SAP BI consultant with 6 years of SAP BI/BW experience and

currently working with Cognizant Technology Solutions(Pune/India). He has got rich experience and worked on

various BW/BI, BPC and BOBJ implementation/Support projects.He has also worked in SAP HANA for POCs.

Introduction:  Currency Conversion is an important aspect of any data modelling and reporting software. Talking about some of software for currency conversion, SAP BPC provides currency conversion functionality using RATE application, SAP Integrated Planning (IP) provides standard function for currency conversion, SAP BW provides currency conversion functionality at transformation and reporting level. Similarly SAP HANA software is not an exceptional and it provides functionality for Currency conversion. Below we will see step wise step how currency conversion takes place in SAP HANA.

/wp-content/uploads/2014/11/1_586006.png

   Fig: Currency Conversion in SAP BW

/wp-content/uploads/2014/11/2_586235.png

   Fig: Currency Conversion in SAP HANA


Business Scenario: A multinational manufacturing company headquartered in India has their sales order information in different currencies depending on country where sales order was created. Now company has a reporting requirement where they want to see sales amount in Local Currencyand equivalent amount in Reporting Currency INR. Report should include following information.


Sales Document

Item

Material

Sales organization

Distribution Channel

Division

Document Currency

Subtotal in LC

Subtotal in INR

Prerequisite:  Please check following points before you proceed for this scenario.

1. Make sure Currency Conversion related tables (TCUR*) are present in your schema and has data.

/wp-content/uploads/2014/11/3_586236.png

/wp-content/uploads/2014/11/4_586237.png

2. Make sure Sales order header and item tables VBAK and VBAP have been loaded into your HANA system.

Step by step process: We will create an Analytic View that will be based on sales table VBAK and VBAP.

Step 1. Right Click on your Package and select Newà Analytic View.  Give the name and Description of your new analytic view. Your screen will look like similar to below.

/wp-content/uploads/2014/11/5_586238.png

/wp-content/uploads/2014/11/6_586239.png

Step 2. Click on + sign on Data Foundation to include tables VBAK and VBAP.

/wp-content/uploads/2014/11/7_586241.png


Step 3. Do the joining of tables and select required fields for output by right clicking on field and selecting “Add to Output”.

/wp-content/uploads/2014/11/8_586242.png


Step 4. If you want to include any Attribute View here then you can do it here in Logical Join step here. We are not including any attribute view here.


Step 5. Create Input Parameter VAR_TARGET_CURR for Target Currency. Select Parameter type as “Direct”, Value as “INR” which will behave as default value at run time, Semantic Type as “Currency” which will give all the list of reporting currency during variable value selection. Select Data Type as VARCHAR and length 3.

/wp-content/uploads/2014/11/9_586243.png

/wp-content/uploads/2014/11/10_586244.png


A brief description of all the available Parameter type is below.


Direct: When you select Direct as Parameter type then you have option for selecting Semantic Type as Currency, Date or Blank. If you select Currency then system will provide you list of currency for variable value selection, if you select Date then calendar will come to select date during variable value selection, if you select nothing then during variable value selection no help values will be available and you have to provide variable value manually.

/wp-content/uploads/2014/11/11_586245.png

Fig: Different semantic types

/wp-content/uploads/2014/11/12_586246.png

Fig: Value Help available for Currency Selection

/wp-content/uploads/2014/11/13_586247.png

Fig: Value Help available for Date Selection

/wp-content/uploads/2014/11/14_586248.png

Fig: No Value Help available for variable value Selection


Column:  When you select parameter type as “Column” then you have to provide table or view name and column name of that table/view.  So whatever values are there in that table for that column will be available as value help during variable value selection.

/wp-content/uploads/2014/11/15_586249.png


Derived From Table:When you select “Derived from Table” as parameter type then you can select any table and one of it’ columns from schema. All the values from that field will be available as Value Help during variable value selection. You can further restrict the set of values using filters on other column.

/wp-content/uploads/2014/11/16_586250.png


Static List: When you select “Static List” as Parameter Type then you can manually maintained list of Values help. You can select the default value for variable value selection in the field Value.

/wp-content/uploads/2014/11/17_586251.png


Step 6: Create a calculated column “Target Currency” and select input parameter VAR_TARGET_CURR in Expression Editor and validate it. Click OK.

/wp-content/uploads/2014/11/18_586252.png

/wp-content/uploads/2014/11/19_586253.png


Step 7. Create the calculated column Subtotal_TargetCurrency and in the expression editor select measure KZWI2 (subtotal).  Select Data type, length etc. as below.

/wp-content/uploads/2014/11/20_586254.png


Step 8. Go to the advance tab and select Type as “Amount with Currency”.  Select your currency field which is WAERK in our case in Currency field. This field is our source currency. Select “Enable for conversion” check box. For field Target Currency select variable VAR_TARGET_CURR that we have created earlier. For Exchange type selects “M”. For Conversion Date select field ERDAT (document date) of our analytic view. Provide Schema and client number. 


Below is brief explanation for each field.


Source Currency:  Source currency can be either a fixed currency or it can refer to document currency field of our table/view.

Target Currency: Target currency can be any fixed currency or it can refer to any currency field of your analytic view or you can decide target currency at run time using Input parameter.

/wp-content/uploads/2014/11/21_586255.png

Exchange Type:  Exchange Type is one of key in the exchange rate table (TCURR) against which rates for conversion are maintained. You can select a fixed value or can decide its value at run time using Input parameter.

/wp-content/uploads/2014/11/22_586256.png

/wp-content/uploads/2014/11/23_586257.png

Fig: Table TCURW maintains text for Exchange Rate Type


Conversion Date: Conversion Date is a date as of which the exchange rate is effective. You can make it as a fixed date or it can refer to some date field like document posting date of your analytic view or you can select date at run time using input parameter.


/wp-content/uploads/2014/11/24_586258.png

/wp-content/uploads/2014/11/25_586259.png

Fig: Different parameters for Currency Conversion


/wp-content/uploads/2014/11/26_586260.png

Fig: data in TCURR table


Step 9. Select Set as Fail from drop down box for field “Upon Conversion Failure”.

/wp-content/uploads/2014/11/27_586261.png

If you select Fail then system will throw the error when system is not able to do the conversion because of wrong parameter provided or any other error.


/wp-content/uploads/2014/11/28_586262.png

Similarly if you select “Set as Null” then system will put null value for target field if system is not able to do the currency conversion.


/wp-content/uploads/2014/11/29_586263.png

If you select “Ignore” the target amount will be equal to source amount if system is not able to do the currency conversion because of any issue like giving the invalid parameters for currency conversion.

/wp-content/uploads/2014/11/30_586264.png


Step 10. Validate and Activate your Analytic View.

/wp-content/uploads/2014/11/31_586265.png

Step 11. Do the data preview for your Analytic View. Select target currency at run time.

/wp-content/uploads/2014/11/32_586266.png


Step 12. Validate your Data.

/wp-content/uploads/2014/11/33_586267.png

     Fig: Data in Analytic View

/wp-content/uploads/2014/11/34_586268.png

     Fig: Data in TCURR for validation


Here, we can see the conversion rate in 39.695 from USD to INR. In the highlighted data 100 USD has been converted to 3969.50 INR which is expected result as per rate maintained in Exchange rate table. Similarly other local currencies EUR and JPY have been converted into reporting currency INR as per their rates.


Reference 1

http://help.sap.com/

Reference 2

http://www.sdn.sap.com/irj/sdn

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply