Decimal figures solved in filter with Replace(Cast... but no 'Between' criteria in More/Search

Question

Hello everybody,

We ran into an issue in OBIEE with the filter we use for one of the dashboards. The filter is 'Column value' type, and data type for this filter in the table is Number. At first, the problem was with displaying the values for this filter in the drop-down box, since it placed 2 decimals at the end of each value. We resolved this with altering the Column Formula this way: REPLACE(CAST("table"."column" AS CHAR),'.00',''), so no decimals this way, but now the problem is that client cannot search using 'Between' criteria with advanced filter (when he hits More/Search at the end of drop-down for the filter), since the data type has been changed into CHAR and 'Range' filtering is not applicable now. Our OBIEE version is 12c.

Any ideas how to solve this ? Thank you very much in advance

BR, Mihajlo

[Deleted User] · Answer

These are the data types allowed in the RPD - most of the numerical ones (note: not "Numeric", which is a specific data type) will never be used when you have an Oracle Database).

One does not choose this at random. This actually means something. Example: INT means a 32-bit SiGNED integer. So from -2,147,483,647 to 2,147,483,647

So if you use INT and have values outside that range...it will yield wrong results.

Mihajlo · Answer

Gianni, you are right, i just passed the information from a colleague to you, since i don't have access to Repository. I suppose than that the type is Numeric or Decimal (since in DB it is set as Number), anyways the problem persists. I am sure that the data type in RPD is set without decimals, but we had 2 decimals in OBIEE. We surpassed that with CAST to CHAR, but in that way we lost the option to filter using 'Between'...Is there any workaround ? Sorry for the misinformation, but i'm quite sure that problem is not related to data type in RPD, rather with OBIEE.

Thanks !

[Deleted User] · Answer

INT has no decimals. It's an integer.