In OBIEE, through a Direct SQL request , I'm trying to concatenate ' USD' to a number. What I'm doing is SELECT ( TO_CHAR(AMOUNT) || ' USD' ).
However, this makes all entries in that column show up as blank, with no values at all.
I've set the column type to varchar.
Are you putting your character string into the Measures section of the pivot table? Because that will NOT work in a million years. Measures are numerical and never ever character strings. '200 USD' + '300 USD' as strings makes as much sense as 'blue' + 'dishwasher'. You always aggregate and calculate numerical. The display is a totally diffferent story:
Amount column -> Column Properties -> Data Format -> Override Default Data Format -> Currency.
Long story short: this isn't how OBIEE works and choosing DDRs from the get-go is a fundamentally wrong approach. OBIEE works with models. Not with SQL statements. Why pay for a Ferrari if you turn off the engine and push it manually?