In SCN community, noticed few people asking how to display unit as separate column instead of behind the value. Initial I thought its quite complex as we have only two options.
- Modeling level option: Create new Infoobject as characteristic and write routine/direct map(currency/unit object) in transformation to get currency/unit value and use in report, this option is good, if we know the requirement in initial phase like realization phase (while designing data model) after that it bit complex like once system Go-Live.
- Virtual Characteristics: Create new Infoobject as characteristic and write ABAP code to get currency/unit while BEx query execution however this will be less complexity if we compare with option 1. For virtual characteristics reference go to link SAP BW BEx Virtual Characters
However after thinking, got one more instant solution, we can derive currency/unit as separate column in query output by adding one extra FORMULA in BEx designer level.
Let See steps involved deriving currency/unit as separate column
Report output before implementing logic.
- Add basic key figure “Amount” to COLUMN and Rename “Amount BK” and Hide
- Create new formula Amount –> NODIM ( Amount ).
- Create new formula called “Currency” — > ( ( Amount BK >=0 ) AND ( Amount BK <= 0 ) ) * Amount BK
- As like below screenshot.
Report output after implementing logic.
As I said its not permanent solution, because in above screenshot currency column act as key figure even though it has alpha values, it just tricky way of separating currency as in different column just for looking not for anything else and it does not support filter and other options, if business users required to provide selections/filters , I recommend select any one of two options which are discussed in this blog above.
Thanks for reading this blog.