Dynamic Currency reporting in HANA

In many reporting scenarios, it is often required to display the financial figures in multiple currencies. For example, most of the systems capture the "Net Sales in the Stores" in the local currency of the Store. For the Country level local reporting, it is often required to report the Net sales in the Local currency. But for the Regional Head Quarter reporting, the Net Sales values need to be converted to the Regional currency. The same figure at the Global Head Quarter might be required in the Global currency.

For a company with the following Organization structure the Reporting might be required in different currencies as follows:
               

Office	Location	Reporting Currency
Global HQ	Germany	EUR
Regional HQ	US	USD
Country Office	Argentina	ARS
Country Office	Brazil	BRL

The reporting is generally defined by the Global IT team with the same report catering to users from Local Country, Regional Headquarters (HQ) and Global Headquarters (HQ). The users can be empowered to choose the Currency in which they would prefer to see the data.

 

While converting the Net Sales from one currency to other, different exchange rates might be applicable. Based on the nature of report and Functional area of the users, the Exchange rate could be Monthly / Yearly Actual or Budgeted. Also depending upon the time, the users may prefer to apply the exchange rate applicable this month, previous month, beginning of this year or even last year.

 

So the factors that define the Net Sales value depend upon:

• Value in Local Currency
• Target Currency
• Exchange rate factor
• Exchange rate Date

HANA provides an easy feature to implement the same in HANA Information models allowing the Business users full flexibility in the financial reporting.

 

For example, consider the organization selling products in multiple countries in the stores. The sales for the Products is captured in the local currency for the stores. Considering the above Organization structure, the data that could be captured for the Sales KPI might look like:

The reporting needs to provide the data in any currency based on the User input with user defined Exchange rate and appropriate Conversion date. Such dynamic currency Measure can be implemented in HANA Information models with the following settings for the Measure.

Some of the important settings can be explained as follows:

• First and most important, define the measure like Net Sales of type "Amount with Currency"
• In the detail settings for "Amount with Currency"
• select the column which contains the Currency code value in Currency drop down
• Ensure the "Enable for Currency" check box is "checked"
• In the Conversion group:
• The Source Currency is selected automatically as the column selected in the setting for Currency code drop down
• Input Parameters can be defined for the User variables to accept the values for:
• Target Currency to be converted to
• Exchange Rate Type
• Conversion Date for the Exchange Rate
• Select the Schema which contains the Currency conversion tables like TCURR, TCURC, TCURX, TCURF, TCURT, TCURV.
• Select the Client value defined in the Currency tables
• The setting for "Upon Conversion Failure" to "Ignore" helps displaying the data in Local Currency by providing dummy value for the Conversion Rate. The dummy value causes to the conversion to Fail and the conversion process is "Ignored" displaying the original data in the table.

To display the data in Regional Currency USD with

• Exchange rate for Regional HQ (RHQ)
• Target Currency as USD
• Exchange Conversion date as defined at the beginning of the year

SELECT   

  "C_COUNTRY"  

, "C_SHOP"  

, "C_PRODUCT"  

, "C_SALES.CURRENCY"  

, sum("C_SALES") AS "C_SALES"   

FROM "_SYS_BIC"."currconv/ZAV_CURR_CONV"   

( 'PLACEHOLDER' = ('$$IP_TGT_CURR$$', 'USD')  

, 'PLACEHOLDER' = ('$$IP_EXCH_RATE$$', 'RHQ')  

, 'PLACEHOLDER' = ('$$IP_CONV_DATE$$', '2013-01-01')  

)   

GROUP BY   

  "C_COUNTRY"  

, "C_SHOP"  

, "C_PRODUCT"  

, "C_SALES.CURRENCY"  

order by "C_SHOP", "C_PRODUCT";  

The column "C_SALES.CURRENCY" displays the Converted Currency code.

The data can be shown in the Global HQ Currency with Last year Exchange rate for "What If" analysis as follows:

SELECT   

  "C_COUNTRY"  

, "C_SHOP"  

, "C_PRODUCT"  

, "C_SALES.CURRENCY"  

, sum("C_SALES") AS "C_SALES"   

FROM "_SYS_BIC"."currconv/ZAV_CURR_CONV"   

( 'PLACEHOLDER' = ('$$IP_TGT_CURR$$', 'EUR')  

, 'PLACEHOLDER' = ('$$IP_EXCH_RATE$$', 'GHQ')  

, 'PLACEHOLDER' = ('$$IP_CONV_DATE$$', '2012-01-01')  

)   

GROUP BY   

  "C_COUNTRY"  

, "C_SHOP"  

, "C_PRODUCT"  

, "C_SALES.CURRENCY"  

order by "C_SHOP", "C_PRODUCT"; 

To display the data in Local currency, just provide the Conversion Rate as any Dummy exchange rate which does not appear in the TCURR table.

SELECT   

  "C_COUNTRY"  

, "C_SHOP"  

, "C_PRODUCT"  

, "C_SALES.CURRENCY"  

, sum("C_SALES") AS "C_SALES"   

FROM "_SYS_BIC"."currconv/ZAV_CURR_CONV"   

( 'PLACEHOLDER' = ('$$IP_TGT_CURR$$', 'EUR')  

, 'PLACEHOLDER' = ('$$IP_EXCH_RATE$$', 'LOCAL')  --- Please ensure that the Exchange rate does not appear in TCURR table  

, 'PLACEHOLDER' = ('$$IP_CONV_DATE$$', '2013-01-01')  

)   

GROUP BY   

  "C_COUNTRY"  

, "C_SHOP"  

, "C_PRODUCT"  

, "C_SALES.CURRENCY"  

order by "C_SHOP", "C_PRODUCT";

Source: scn.sap.com