Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

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

We are trying to put the condition on 'Run Result Value' from the subject area 'Payroll -Payroll Run

Received Response
61
Views
6
Comments
Anjali_Kumari4121
Anjali_Kumari4121 Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

We are trying to put the condition on 'Run Result Value' from the subject area 'Payroll -Payroll Run Results Real Time' in OTBI report.
Condition is that Run Result Value > 1000 , for achieving this condition we are trying to convert Run Result Value to number as it's storing as string in subject area. Tried to_number ,double function but it's not working.

WE have tried (CAST("Payroll Run Results"."Run Result Value" as DOUBLE)) ,it's not working

Tagged:

Answers

  • Hassan El Bouihi-Oracle
    Hassan El Bouihi-Oracle Rank 5 - Community Champion

    Hello Anjali,

    Instead of casting the "Run Result Value" to a number, create a new filter on the "Run Result Value" as shown here and check the "Convert this filter to SQL" and press OK:

    image.png

    The new window will show this:

    "Payroll Run Results"."Run Result Value" > '1000'

    Just remove the single quotes around 1000 and press OK.

    The filter should look like this:

    image.png

    Now the results will return only values greater than 1000.
    Thank you
    Hassan

  • Hassan El Bouihi-Oracle
    Hassan El Bouihi-Oracle Rank 5 - Community Champion

    Please click on Answered if the solution works for you.

    Thank you

  • Anjali_Kumari4121
    Anjali_Kumari4121 Rank 1 - Community Starter

    Hi Hassan El Bouihi-Oracle ,

    I tried the logic on filter which you have suggested but it's not working , result is returning all values.

    image.png

    image.png

    Please suggest.

    Thanks,

    Anjali Kumari

  • User_PFO2E
    User_PFO2E Rank 1 - Community Starter

    Hello Anjali -

    Please confirm if the data is only numeric or it is a alphanumeric value. CAST ('Column' as DOUBLE) should work for the numeric data.

    EX : CAST("Payroll Run Results"."Run Result Value" as DOUBLE) or try INTEGER

    Define a aggregation and check results.

  • Anjali_Kumari4121
    Anjali_Kumari4121 Rank 1 - Community Starter

    Hi User_PFO2E

    Run Result Value holds alphanumeric value. There is another column 'Amount' , amount is having value if Run Result Value is returning number else Amount is blank. So to fetch the number only from the Run Result Value, we have added one more condition 'Amount is not null' , over this condition we have added condition for Run Result Value CAST("Payroll Run Results"."Run Result Value" as DOUBLE) or INTEGER , but when we are adding this condition result is getting failed.

    image.png
    image.png

    Thanks,

    Anjali Kumari

  • Anjali_Kumari4121
    Anjali_Kumari4121 Rank 1 - Community Starter

    This report has been requested for audit purpose , please suggest the better solution. It will be really helpful if working session can be arranged