on 03-05-2013 9:50 PM
I am using BOBJ 4.0 SP04 and have a Universe sourced from an Oracle 11 Database. I have $ amount values which are stored as character strings in Data type ‘AB’. I would like to only display the numerical values in my WEBI reports so that I can calculate Totals and possibly perform other calculations against these amounts.
I’ve tried converting the Data Type to ‘Numeric’ within the Business Layer, but I still receive ‘Incorrect Data Type’ error because of the other values stored in this same database field. (The value stored is based on the ATTR_ID field identifier so possible entries are not just the $amount).
What is the recommended best practice or approach to address this scenario? Would a derived field on the source table be required in order to store the numeric values only? I would also need to remove all non-numeric values or even convert them to zeros or spaces.
For example, I have records where ‘$10000’ , ‘$15000+’ or even ‘tbd’ is entered as the amount value. I would only like to show ‘1000’, ‘15000’ ‘0’.
Thank You in Advance,
A. Cook
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Cook,
When at the DB if a field is stored as character, you cannot convert it to numeric in the semantic layer. You will get "Incorrect Data type" as you have mentioned. Further you cannot perform any numeric operations on the string type. You can try using ToNumber() function at the report level. But I am not pretty sure if, it will convert special characters like $, or +. So What I suggest is to have that data at the DB as numeric.
Thanks,
Madhumitha
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.