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

OTBI Dashboard Dependent Prompt is not taking another prompt value in sql ?

Received Response
1
Views
2
Comments
Ankur Jain--Oracle
Ankur Jain--Oracle Rank 5 - Community Champion
in Oracle Transactional Business Intelligence

We are trying to create dependent prompt in OTBI. Dependent prompt is created using sql as we need to put some additional conditions. We are using following syntax but it is not working:

Prompt2 Query:

SELECT "Worker"."Assignment Number" FROM "Workforce Management - Worker Assignment Real Time"  WHERE "Worker"."Person Number" IN (@{PNO}{'1000'})  FETCH FIRST 65001 ROWS ONLY

Here PNO is the presentation variable defined in prompt1. Please let us know if anyone has worked on similar requirement.

Regards

Ankur

Answers

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    Hi Ankur,

    Why are you building a custom solution with custom SQL?

    Did the out of the box feature not work for you for some reason "Limit values by?

    Your requirement is you want prompt B to only have values linked to what is in prompt A.

    Prompt B is assignment number

    "Workforce Management - Worker Assignment Real Time"."Worker"."Assignment Number"

    Assume prompt A person number? 

    "Workforce Management - Worker Assignment Real Time"."Worker"."Person Number"

    A person may have multiple work relationship assignments effective at the same point in time (they do 2 jobs). You want to restrict the assignments to the person selected if the user has already selected a person.

    Assume the assignment numbers for a person are unique (the assignment unique identifier is actually 5 cols; Id, effective start/end date and if latest change in same day N then sequence number in that day).

    So if you build your prompt like this …

    If person 1000 has 5 worker assignments then if you select that person(s) in prompt A then the list of values in prompt B will only have the 5 values for the assignments for that selected persons. But if person number is not selected then you get all assignments as normal. No need for any presentation variables.

  • Ankur Jain--Oracle
    Ankur Jain--Oracle Rank 5 - Community Champion

    Hi @Nathan CCC

    that limit by option will not work for me as I need to add more conditions here.

    Regards

    Ankur