Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
72
Views
5
Comments
Mihajlo
Mihajlo Rank 3 - Community Apprentice
edited Aug 13, 2024 5:17PM in Oracle Analytics Cloud and Server

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

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    What is the exact data type of that column you use as filter in the RPD? "Number" isn't a data type.

    And which exact version of 12c? As there are various versions and various bundle patches, each can have an impact on what you see...

  • Mihajlo
    Mihajlo Rank 3 - Community Apprentice

    Hello Gianni,

    Thanks for your answer! The exact data type in RPD is Integer, and the version is 12.2.1.1.0. Unfortunatelly CAST to BigInt (since this column is Number (38,0) in DB) isn't working...

    BR, Mihajlo

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    INT has no decimals. It's an integer.

    pastedImage_1.png

    pastedImage_0.png

  • Mihajlo
    Mihajlo Rank 3 - Community Apprentice

    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]
    [Deleted User] Rank 2 - Community Beginner

    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).

    pastedImage_0.png

    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.