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 value issue while displaying in Prompt

Received Response
461
Views
5
Comments
Rajagopal R-Oracle
Rajagopal R-Oracle Rank 5 - Community Champion
edited Aug 13, 2024 5:59PM in Oracle Analytics Cloud and Server

Hi.,

We have number(20) column in Database , when we import that in RPD that is converted as Double.

When we use that column in Reports we are able to remove the decimal places and able to display the value in integer format., but when we use the value in dashboard prompt it's showing the values in decimal places , when we do (Cast as int) getting "Numeric Overflow Occured" error,

Ex: if the value in database '140023000000000'

prompt - 140023000000000.0000

I wanted to display the value '140023000000000' in dashboard prompt.

any inputs?

thanks

Raja

Answers

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    Try using the Truncate function.

    Edit: Upon checking the above may not work.

  • Syedsalmancs110
    Syedsalmancs110 Rank 6 - Analytics Lead

    What method have you used in report to remove decimal? Column formatting?

    Could you chnage your column formula as following example

    REPLACE(CAST("PI"."DEC" AS CHAR),'.0000','')

    and then for your Dashboard Prompt ,In Prompt For Column put the same formula as above, you can change the Label to display column name instead of complete formula as Prompt Name

    and then in Options Choice List Values select "SQL Results" and put below example query

    SELECT REPLACE(CAST("PI"."DEC" AS CHAR),'.0000','') FROM "PC14" FETCH FIRST 65001 ROWS ONLY

    Disclaimar:Above will work given that your data has fixed number of zeros after decimal for all the values.

  • Gianni Ceresa
    Gianni Ceresa Mod

    Data types matter!

    A INT in OBIEE is a 32 bits signed integer, which means the maximum positive value is 2,147,483,647.

    Your number is definitely bigger than that, so don't even try to convert it to INT.

    A prompt doesn't have a format mask, it display the default format mask based on type and a double has a decimal component of the number.

    You can for sure convert it into a varchar and then get rid of the decimal part, but that's just a bad workaround. A prompt with such big numbers is already not really friendly ...

    PS: keep in mind any change you do in the prompt at the column formula level will have an impact on how you use the value selected. OBIEE will not match it anymore with your original column if it's filtered as "XXXX is prompted". If you use SQL based values etc. you also lose other pieces of functionality like limiting values by etc. It's a choice of the less worse solution if you really want to do that ...

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

    INT....nice. INT doesn't mean what you think it means. It's a 32bit SIGNED integer!

    https://www.slideshare.net/ChristianBerg8/50-shades-of-fail-kscope16  <-- Slide 10 & 11

  • Rajagopal R-Oracle
    Rajagopal R-Oracle Rank 5 - Community Champion

    Converted as Varchar in BMM and using it in Prompt , as a work around .