Purpose
Currency conversion is a standard requirement in reporting. Financial measures from different countries only become comparable once they are expressed in a single currency. However, a converted amount is not always what the user needs. In some situations the amount in the original document currency is the relevant figure.
Building one report with conversion and a second one without it is not an ideal solution. It duplicates the modeling effort and leaves the end user with two objects that need to be kept in sync. A better approach is to make the conversion optional and let the user decide at runtime whether it should be applied. This blog post shows how to implement optional currency conversion in SAP Datasphere with a single input parameter, using a dedicated entry in the value help to skip the conversion.
The implementation builds on the standard SAP Currency Conversion Views. How to create them is described in Enabling Currency Conversion with TCUR* Tables and Views. An alternative approach, in which these views are based on the HANACurrency Data Product, is described in Using Currency Conversion with the HANACurrency Data Product.
Prerequisites
The following objects are assumed to be in place:
- The standard SAP Currency Conversion Views in the space.
- A fact view containing at least one financial measure and a column holding the source currency of each record. Throughout this blog post, that column is referred to as SOURCE_CURRENCY.
- An Analytic Model on top of that fact view, since the currency conversion is configured there.
Overview
The approach is based on a single input parameter of the fact view, in which the user selects the target currency. Its value help is provided by a currency dimension view that contains all available currency codes, plus one additional entry, 000, with the text "No currency conversion". A text view provides the currency texts, including the text for this additional entry in every language used in reporting.
In the fact view, a calculated column derives the actual target currency from this parameter: if the parameter holds a true currency code, that currency is used. If it holds 000, the source currency of the record is used instead.
The currency conversion is configured in the Analytic Model on top of the financial measure, using this calculated column as the target currency.
Preparation
The additional currency code is not part of the currency data delivered by the source system. It has to be provided before the conversion can be made optional. This blog post assumes that a graphical fact view is used. The implementation works in a similar way with SQL views.
Step 1: Create a table for the additional currency entry
Create a local table holding the additional currency code and its texts. It requires a currency key, a language key and the text columns. The currency key and the language key are the key fields of the table. In this example the table is named No_Currency_Conversion.
Enter one record per language. In this example, the currency key 000 is used, with texts in German and English.
| Currency Key | Language | Long Text | Short Text |
| 000 | DE | Keine Währungsumrechnung | Keine Umrechnung |
| 000 | EN | No currency conversion | No curr. conv. |
Further languages can be added in the same way.
Step 2: Create the currency text view
The text view combines the standard currency texts from the Currency Text View (SAP.CURRENCY.VIEW.TCURT) with the texts of the additional entry, taken from the table created in Step 1.
SELECT "MANDT" AS "MANDT",
"WAERS" AS "WAERS",
"SPRAS" AS "SPRAS",
"LTEXT" AS "LTEXT",
"STEXT" AS "STEXT"
FROM "SAP.CURRENCY.TABLE.TCURT"
UNION ALL
SELECT '000' AS "MANDT",
"WAERS" AS "WAERS",
"LANGU_ISO" AS "SPRAS",
"LTEXT" AS "LTEXT",
"STEXT" AS "STEXT"
FROM "No_Currency_Conversion"
Step 3: Create the currency dimension view
The dimension view combines the standard currency codes with the additional entry. It reads the standard Currency Codes View (SAP.CURRENCY.VIEW.TCURC) and adds the record from the table created in Step 1 in a union. The table is projected down to the additional currency key only.
SELECT "MANDT" AS "MANDT",
"WAERS" AS "WAERS",
"ISOCD" AS "ISOCD"
FROM "SAP.CURRENCY.TABLE.TCURC"
UNION ALL
SELECT '000' AS "MANDT",
"WAERS" AS "WAERS",
'' AS "ISOCD"
FROM "No_Currency_Conversion"Implementation
Step 4: Define the input parameter
Define an input parameter in the fact view as described in Create an Input Parameter in a Graphical View .
Property Value
| Business Name | Target currency |
| Technical Name | IP_CURRENCY |
| Data Type | String, length 5 |
| Default Value Help | Predefined Value from an Object |
| Object/Column | Currency dimension view / currency code column |
| Default Value | 000 |
The data type must match the currency column of the dimension view used as the value help. The default value determines which currency is preselected when the report is opened, 000 in this example, so that no conversion is applied unless the user chooses one.
Step 5: Create the target currency column
In the fact view, create a calculated column that derives the actual target currency from the input parameter. It is defined with the business name Target currency, technical name TARGET_CURRENCY, data type String and length 3.
The expression is as follows:
CASE WHEN :IP_CURRENCY != '000' THEN :IP_CURRENCY ELSE "SOURCE_CURRENCY" END
If the user selects one of the currency codes, that currency becomes the target currency. If the user selects 000, the target currency is the source currency of the record itself, so the conversion applied later has no effect.
Step 6: Configure the currency conversion in the Analytic Model
The Analytic Model consumes the fact view created in the previous steps. The input parameter IP_CURRENCY of the fact view is mapped to the input parameter of the Analytic Model with the same name. For the financial measure that should be converted, a currency conversion measure is created on top of it, using TARGET_CURRENCY as the target currency. The source currency is taken from the currency assigned to the source measure.
By default, the remaining settings are configured as follows: exchange rate type M, reference date Current Date, client 000, error handling Set to Null. Depending on the requirements, these settings can be adjusted.
Note that the client 000 in these settings has nothing to do with the currency code 000 used to skip the conversion. The two are unrelated.
The original measure in source currency should be hidden as a helper measure, and so should the converted measure, since on its own it does not yet return the expected result. The next step explains why.
Step 7: Handle missing source currencies
A fact view may contain non-financial measures as well, and records that only carry values for those measures. In such records the source currency is not determined and the currency column remains empty.
An example. The following fact view contains two records.
| Sales Organization | Amount | Currency | Quantity | Unit of Measure |
| 001 | 1000.00 | EUR | ||
| 001 | 50 | PC |
In the second record, both the financial measure and the currency column are empty. When the currency conversion is executed, it cannot process the empty currency and returns null. The aggregation cannot combine this null with the value of the first record and returns * instead of the expected amount.
To avoid this, a restricted measure is created on top of the converted measure with the following filter condition:
TARGET_CURRENCY IS NOT NULL AND TARGET_CURRENCY != ''
This filters out the records in which no target currency could be determined, which are exactly the records without a source currency. This restricted measure is the one exposed to end users. It can be used directly in a story or serve as the basis for further calculated measures.
Result
When the report is opened, the user is prompted to select a target currency. The default value 000 stands for "No currency conversion".
| Variables | Member Description |
| Target Currency | 000 |
The value help shows the additional entry together with its text:
| ID | Description |
| 000 | No currency conversion |
| EUR | Euro |
| USD | US Dollar |
| JPY | Japanese Yen |
With this default, no conversion takes place and every record is displayed in its source currency. The figures are correct, but they cannot be compared or aggregated across sales organizations, since each row is expressed in a different currency.
Selecting a true target currency such as EUR converts all amounts into that currency. The currency is now shown once in the column header instead of on every row, and the figures can be compared and aggregated.
About this blog
This blog post is part of a technical blog series published by the German SAP Consulting teams for BDC & Insights. Our goal is to foster innovation, share knowledge, and drive adoption. We are committed to helping turn your implementation into a success story.
If your project could benefit from the direct involvement of the company behind the product, please contact your SAP Service Account Executive or visit https://www.sap.com/germany/services-support.html. We are happy to support you with our experience and deep product expertise.
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.