Skip to Content

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:

d_table_data.JPG

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.

d_conv_settings.JPG

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.

d_table_data_TY_CURR_USD.JPG

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";

d_table_data_LY_CURR1.JPG

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";

Hope this helps in defining your Dynamic Currency Conversion Reporting. 🙂

To report this post you need to login first.

9 Comments

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

  1. Rama Shankar

    Great Job! thanks!

    Good explanation and steps on Currency Conversion. One suggestion – you should named the title as “Currency  Conversion Reporting using HANA” or something like that…

    Rama

    (0) 
    1. Ravindra Channe Post author

      Thanks for the kind words Rama. Two reasons why “Dynamic” in the title.

      First, the conversion can be done based on Conversion rate, Currency and the conversion rate date. Since these values can be based on User input, these are dynamic.

      Secondly, it was the Title of the Implementation request I developed on BW BEx report couple of years back for the exact same requirement. 🙂

      Regards,

      Ravi

      (0) 
      1. Raj K

        Hi Ravi,

        Good blog.

        Regarding the word dynamic I am writing a blog on ‘Dynamic filters’ but got strucked with it.

        Regards

        Raj

        (0) 
  2. Divya Mahajan

    Hi,

    I was trying to setup my own schema with the currency tables – but get the message “System tables for currency conversion is missing in the default schema”. Did you face this error ?

    (0) 
  3. Víctor Machuca

    This was very helpful, thanks!!.

    is it possible to have something similar like in BW where you can select the time reference?

    I need to calculate the conversion based on end-of-month like in bw.

    Untitled.png

    (0) 
  4. VIJAYA SIMHA CHINTARLAPALLI REDDY

    I dont have the TCURR tables imported to the SAP HANA ,Can you please let me know how to build the below fields with out the TCURR table

    • Target Currency to be converted to
    • Exchange Rate Type
    • Conversion Date for the Exchange Rate
    (0) 
    1. Phani KV

      Hi,

      if you want above fields extract first need to import form ECC to HANA system using SLT or BODS  system.

      if not these will not give correct output.

      Thanks,

      Phani.

      (1) 

Leave a Reply