Implementing Dynamic Currency Conversion Using Calculated Column Using Semantic Type Amount with Currency Code in SAP HANA
Currency Conversion is an important aspect of any data modelling and reporting software. SAP HANA software provides functionality for Currency conversion. Below we will see step by step process on how currency conversion takes place in SAP HANA. This article is completely based on my learning experience. I hope, this article will be useful for those consultants who are new to SAP HANA.
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.
|VBAK||Sales Document: Header Data|
Step by Step Implementation
- Let us start by creating a “Calculation View” in the HANA system based on VBAK table.
2. Provide name and label. Make sure to check the “With Star Join” option.
Note: This can be done for “non-star join” type of calculation view or with an “Analytical View” as well. But for this example, we are taking this as “star join”
By default, you will see that there is a “Star Join” node and a “Semantics” node which will appear.
3. Now, insert a “Projection” Click on the plus icon to add the table in the data foundation node and join it to the star join node. After that click on “Add Objects” in the projection to add a table.
Select Table VBAK and click OK
Select the fields in the VBAK table as shown below:
Apply a filter on the field WAERK to only bring “EUR”
Select the value EUR and then click OK
4. Now, we need to create few “Input parameter”: first.
Create a new “Input Parameters” in the projection node
Provide the necessary fields like “Name” and “Level” (DATE).
Select the “Parameter Type” as Direct
Select the “Default Value” as “Expression” and give the “Value” as format(now(),‘YYYYMMDD’)
This syntax will produce the current date when executed.
Also, select both the “Symantec Type” and “Data Type” as Date
After this Click OK
This will help us pick the date on which we want to get currency conversion.
5. Again create another “Input Parameter”. This input parameter is for the “Target” currency.
Provide the necessary details like name and label (ZCURRENCY)
Select Parameter types as Column
Now, in the “Column” section select “View/Table for value help” as the current view only and the “reference column” as WAERK
6. Now, create a new “Calculated Column” on the same “Star Join” node
Provide the necessary fields like “Name” and “Level”.
Make sure to provide the “Data Type” as DECIMAL and “Column Type” as MEASURE.
Give the syntax and validate it
After that click on “Semantics” tab and Select semantic type as “Amount with Currency Code”
And then click on the “Conversion” check box and then select the “Currency” as “<calculated column>_CURRENCY”. In this case, this will be “TARGET_CURRENCY_CURRENCY”.
After that click on the “Schema for currency conversion” and select the appropriate schema.
Select the schema under which the base tables exist and click OK
Provide all details as shown and then click OK
Source Currency: This we have to select the column from the source VBAK table.
Target Currency: This we have to select the “ZCURRENCY” input parameter, as this is something which the user will give.
Exchange Type: This we select that as “M” as this will ensure that the daily transaction rates are getting captured.
Conversion Date: This we have to select the “DATE” input parameter. This will give the user the flexibility to choose the date in which he/she wants to see the exchange rate.
Exchange Rate: This we again need to select the appropriate column in which the exchange rates are being stored. In this case, it is VKORG
7. Now, we click on semantics and check all the column are reflecting or not and then validate and Activate the calculation view
Click on data preview.
Provide values to the two “Input Parameter” as created.
Here we are changing the currency from EUR to USD, so we are giving the ZCURRENCY as “USD” and the conversion will happen based on the date range given.
Click on raw data to view the entire data.
Here we can see the output values with TARGET_CURRENY column.
By this exercise, we have implemented the business logic where business is dynamically converting the currency from EUR to USD based on the current date exchange rate and showing the end result based on the extra calculation done on the calculated column.