Amount in certain currency is multiplied by 100 at Report Level
Hari Boda is working as a SAP Lead BW-HANA consultant at TekLink Software Pvt Ltd and has worked on multiple assignments in BW-HANA analytics domain.
Brief about issue:
Amount in certain currency is multiplied by 100 in bex report in other terms the decimal places in a query is returning wrong values, sometimes the amount might be too high or too low by a factor of 100.
Explanation and solution:
This generally happens due to the decimal places maintained in table TCURX.
The Table TCURX defines how currency amounts are converted between DB storage and display on the front-end.
Not only at front end,the TCURX settings are considered also when loading data. Therefore, in order to get consistency, always there should be inverse calculation done at loading time at Application level.
By default, all amounts are stored with 2 decimal places on the database and are later converted for display based on the currency entry in the table TCURX.
If a currency is entered with e.g. value 0, then the currency is displayed with 0 decimal places in BW Queries (which means that the amount is displayed with factor 100 higher than it is stored on DB Level).
TCURX = NO ENTRY: Database entry = 123.45
The above number is displayed as 123.45
TCURX = 3: Database entry = 123.45
The above number is displayed as 12.345(3 decimal places, as 3 is maintained in the TCURX table).
TCURX = 0 Database entry = 123.45
The above number is displayed as 12345(0 decimal places and shows 12345 as no decimal places maintained in the TCURX table)
In order to correct this in BW:
we have to correct the data for the currencies having issue while loading in BW within the transformation.
- In the routine, get the data from TCURX to fetch number of decimal places for the respective currency.
- for 0 decimal currencies that are having issue’s at reporting layer, assign 100 value to a variable and store it in internal table.
- using above internal table, for the respective KPI’s and Currency Unit we need to divide by 100.
By doing this division by 100, at the reporting layer the KPI will show correctly i.e. without multiplication of 100.
Thank you for reading my blog. Comments and feedback are welcome!
I've never encountered this problem when extracting from SAP systems, but it is a problem when loading data from other databases (e.g. Oracle), or from flat files. It's only a problem for those currencies which don't use 2 decimal places (e.g. JPY, TWD, HUF)
There is a standard function module I use for this (FLD_CVT_CURR_INTERNAL) . I've created a class which loops through a package of data and calls it for every currency value. I then use this on any transformation loading from non-SAP sources.
You can also get a similar problem if writing data out via openhub, in which case there is a corresponding function module - FLD_CVT_CURR_EXTERNAL
Thanks. I think that the reason why the number of decimals in database ("computer-readable") don't represent the actual number of decimals displayed or printed ("human-readable"), it's because originally (years ago...) the size of storage was limited and expensive, so instead of defining bigger amount fields so that number of decimals in database = number of decimals in real life, what is important being the significant number of digits (those digits before + those after decimal point, which are the same for all currencies), the solution was to ignore the decimal point in database ("right align" the number, keeping only actual number of decimals).
Now we have "decimal floating point numbers" which are based on significant number of digits (ABAP types decfloat16/decfloat34, DDIC types D*16*/D*34*).
The issue results from the fact the currency fields in SAP always comes in pairs: A currency and it's reference currency key (Then, an implicit conversion is being done before the output).
In case you wish to convert currency fields into decimal fields, FM CURRENCY_AMOUNT_SAP_TO_BAPI should do the trick.