Working With Currency Conversion in SAP HANA

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.

   Fig: Currency Conversion in SAP BW

   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.

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.

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.

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

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

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.

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.

Fig: Different semantic types

Fig: Value Help available for Currency Selection

Fig: Value Help available for Date Selection

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.

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.

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.

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

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

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.

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.

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.

Fig: Different parameters for Currency Conversion

Fig: data in TCURR table

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

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.

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.

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.

Step 10. Validate and Activate your Analytic View.

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

Step 12. Validate your Data.

     Fig: Data in Analytic View

     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