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

Remove decimal places in OBI Dynamic Repository Variable

Received Response
32
Views
7
Comments
Ebin Cherian
Ebin Cherian Rank 5 - Community Champion
edited Aug 13, 2024 5:52PM in Oracle Analytics Cloud and Server

Hi All,

Oracle Business Intelligence 12.2.1.1.0

We have created repository variables for Fiscal Year, Previous fiscal year etc. Query to populate this variables return the correct result in database without decimal place. But when I execute the same in OBI RPD initblock, it appends a decimal place to the result.  Also When we try to call this repository variable in the column heading in answers, it has a decimal place appended to it.

How can I format the variable to remove decimal places ? Also is it possible to do subtract an year from this variable in the column header... I would like to do Previous Fiscal Year -1 ?

Thanks

Ebin

pastedImage_4.png

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    Can you show the SQL statement of the initialisation block?

  • Ebin Cherian
    Ebin Cherian Rank 5 - Community Champion

    pastedImage_0.png

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Your case statement is totally pointless given your where clause, it might be worth trying it without.

    select variable_value_int

    from obiee_rpd_variables

    where variable_name = 'RVAR_PRV_SALES_FISCAL_YEAR'

    Would achieve the same.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    It's faster and reusable if you do your Previous Year -1 in your variables as well ... if you do it in the report header - then you have to do it for every report ...

    SQL developer does a lot to 'hide' conditions of data types given you can set up formats for your sessions (I often get people coming to me with this issue - similar to SQL Dev only showing them DD-MON-YYYY from the datetime stored in the database ... then they get it to their visualization tool of choice only to find it's really DD-MON-YYYY HH24:MI:SS)... so the decimal in the RPD needs to be handled in the RPD via data types.

  • Ebin Cherian
    Ebin Cherian Rank 5 - Community Champion

    Hi Robert,

    Thanks for the reply. I tried without statement, still same issue. I even tried casting the value to INT.

    Edit : Removed Screenshot

  • Ebin Cherian
    Ebin Cherian Rank 5 - Community Champion

    Hi Thomas,

    Our plan to create a new variable. Since it was an immediate requirement, I was curious if we can do the calculation at the column heading level.

    Also regarding the actual issue, I tried the cast option and it is still returning a decimal place. Please see the screenshot in above reply.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Does this actually cause you any problem if you use it as the basis of a filter, I am sure I have seen year variable with a decimal place previously in OBIEE, but when I actually used it against the tables it did NOT cause an issue, and if you display it you can format it obviously.