Remove decimal places in OBI Dynamic Repository Variable

Ebin Cherian

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

Joel

Can you show the SQL statement of the initialisation block?

Ebin Cherian

Robert Angel

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

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

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

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

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.