Community
Data and Analytics Blog Posts
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Currency conversion is a common requirement in SAP reporting, analytics, pricing, and planning scenarios. Whether you are developing SQLScript Table Functions in SAP HANA XSA, building reusable data models in SAP HANA Cloud, or creating analytical models in SAP Datasphere, you will eventually need to compare and report data maintained in multiple currencies.

SAP provides the built-in CONVERT_CURRENCY() function to perform currency conversion directly within SQL. Rather than implementing custom exchange rate logic, developers can leverage SAP's standard currency conversion framework and exchange rate tables.

This article provides a practical overview of how to use CONVERT_CURRENCY() in SAP HANA XSA and SAP Datasphere, along with some implementation patterns that I've found useful in real-world projects.

If You Have Already Used Currency Conversion in Calculation Views...

If you've previously configured Currency Conversion for a measure within the Semantics node of a HANA Calculation View, many of the concepts used by CONVERT_CURRENCY() will already be familiar.

pradeep_gupta1_0-1790584644804.png

Both approaches rely on the same fundamental business concepts:

  • Amount
  • Source Currency
  • Target Currency
  • Exchange Rate Type
  • Reference Date

The primary difference is that Calculation Views expose these settings through a graphical interface, whereas CONVERT_CURRENCY() provides direct access through SQLScript.

Basic Syntax

CONVERT_CURRENCY(
    "AMOUNT" => NET_VALUE,
    "SOURCE_UNIT" => SALES_CURRENCY,
    "TARGET_UNIT" => 'USD',
    "REFERENCE_DATE" => CURRENT_DATE,
    "CLIENT" => '500',
    "SCHEMA" => 'MY_SCHEMA'
)

The function converts the amount from the source currency into the target currency using the exchange rate configuration defined in the SAP currency conversion framework.

Most Common Parameters

Although the function supports a large number of parameters, most implementations typically use the following:

  • AMOUNT – Monetary value to be converted
  • SOURCE_UNIT – Source currency
  • TARGET_UNIT – Reporting currency
  • REFERENCE_DATE – Date used to determine the exchange rate
  • CONVERSION_TYPE – Exchange rate type (for example M, P, or B)
  • ERROR_HANDLING – Controls behavior when exchange rates cannot be determined

Additional parameters are available for specifying the underlying currency conversion tables, which is especially relevant for HDI Container-based implementations.

Using CONVERT_CURRENCY() in SAP HANA XSA

One practical use case involved reporting SAP pricing conditions in a common currency. Pricing records were maintained globally using multiple currencies, but the business required global reporting in USD.

The following example converts a pricing condition value into USD:

CONVERT_CURRENCY(
    "AMOUNT" => "KBETR",
    "SOURCE_UNIT" => "KONWA",
    "TARGET_UNIT" => 'USD',
    "CLIENT" => '500',
    "SCHEMA" => 'MY_HDI_SCHEMA',
    "REFERENCE_DATE" => CURRENT_DATE,
    "CONVERSION_TYPE" => 'P',
    "ERROR_HANDLING" => 'set to null'
) AS "KBETR_USD"

This provides a standardized price regardless of the original currency in which the pricing condition was maintained.

Working with HDI Containers

Many SAP HANA XSA projects expose SAP currency tables through synonyms rather than directly accessing the underlying TCUR* tables.

Typical examples include:

TCURR_CV_SYN
TCURF_CV_SYN
TCURV_CV_SYN
TCURN_CV_SYN
TCURX_CV_SYN

In these scenarios, the synonym objects can be passed directly into CONVERT_CURRENCY():

CONVERT_CURRENCY(
    "AMOUNT" => "KBETR",
    "SOURCE_UNIT" => "KONWA",
    "TARGET_UNIT" => 'USD',
    "CLIENT" => '500',
    "SCHEMA" => 'MY_HDI_SCHEMA',
    "REFERENCE_DATE" => CURRENT_DATE,
    "CONVERSION_TYPE" => 'P',
    "ERROR_HANDLING" => 'set to null',
    "CONFIGURATION_TABLE" => 'TCURV_CV_SYN',
    "PRECISIONS_TABLE" => 'TCURX_CV_SYN',
    "NOTATIONS_TABLE" => 'TCURN_CV_SYN',
    "RATES_TABLE" => 'TCURR_CV_SYN',
    "PREFACTORS_TABLE" => 'TCURF_CV_SYN'
)

This is a common pattern in HDI Container-based applications where SAP ERP or S/4HANA metadata is exposed through synonyms.

Combining Unit Conversion and Currency Conversion

In many pricing scenarios, comparing currencies alone is not sufficient. Prices may also be maintained using different pricing units or units of measure.

In one implementation, the pricing condition was first normalized into a common reporting unit:

K."KBETR" * (UN."UMREZ" / UN."UMREN")

The normalized value was then converted into USD:

CONVERT_CURRENCY(
    "AMOUNT" => K."KBETR" * (UN."UMREZ" / UN."UMREN"),
    "SOURCE_UNIT" => K."KONWA",
    "TARGET_UNIT" => 'USD',
    "CLIENT" => '500',
    "SCHEMA" => 'MY_HDI_SCHEMA',
    "REFERENCE_DATE" => CURRENT_DATE,
    "CONVERSION_TYPE" => 'P',
    "ERROR_HANDLING" => 'set to null'
)

This produces pricing values that are comparable across:

  • Multiple currencies
  • Different pricing units
  • Different units of measure

Using CONVERT_CURRENCY() in SAP Datasphere SQL Views

CONVERT_CURRENCY() is not limited to SAP HANA XSA. Developers working with SAP Datasphere can also use the function directly within SQL Views running in SQLScript mode.

For example:

CONVERT_CURRENCY(
    "AMOUNT" => "GROSS_SALES_VALUE",
    "SOURCE_UNIT" => "SALES_CURRENCY",
    "TARGET_UNIT" => 'USD',
    "REFERENCE_DATE" => "SALES_DATE",
    "CLIENT" => '100',
    "CONVERSION_TYPE" => 'M',
    "ERROR_HANDLING" => 'set to null'
)

This approach provides full flexibility for implementing custom currency conversion logic directly inside Datasphere SQL models.

Using CONVERT_CURRENCY() in Datasphere Graphical Views

One capability that is easy to overlook is that developers are not limited to using CONVERT_CURRENCY() within SQL Views. SAP Datasphere also allows the function to be embedded directly within Calculated Columns in Graphical Views.

For example:

CONVERT_CURRENCY(
    "AMOUNT" => "GROSS_SALES_VALUE",
    "SOURCE_UNIT" => "SALES_CURRENCY",
    "TARGET_UNIT" => :P_REPORTING_CURRENCY,
    "REFERENCE_DATE" => :P_EXCHANGE_RATE_DATE,
    "CLIENT" => '500',
    "CONVERSION_TYPE" => 'M',
    "ERROR_HANDLING" => 'set to null'
)

This combines the simplicity of graphical modelling with the flexibility of parameter-driven currency conversion. Business users can dynamically choose reporting currencies and exchange-rate dates without modifying the model itself.

Making Currency Conversion Dynamic

Hardcoding target currencies and dates may be suitable for some reporting scenarios, but many business users require flexibility.

By utilizing input parameters, the same logic can support multiple reporting currencies:

CONVERT_CURRENCY(
    "AMOUNT" => "NET_REVENUE",
    "SOURCE_UNIT" => "DOCUMENT_CURRENCY",
    "TARGET_UNIT" => :P_TARGET_CURRENCY,
    "REFERENCE_DATE" => :P_RATE_DATE,
    "CONVERSION_TYPE" => 'M',
    "ERROR_HANDLING" => 'set to null'
)

This pattern works particularly well in SAP Datasphere analytical models and parameter-driven reporting scenarios.

Conclusion

CONVERT_CURRENCY() is a useful built-in function available across SAP HANA XSA, SAP HANA Cloud, and SAP Datasphere. Whether you're building SQLScript Table Functions, SQL Views, or Graphical Views, the function provides a consistent and flexible way to perform currency conversion using SAP's standard currency conversion framework.

By understanding the core parameters, HDI Container considerations, and Datasphere integration options, developers can significantly reduce the effort required to implement robust currency conversion logic across enterprise reporting and analytical solutions.

References

Comment