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

Question

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

Ankur Jain--Oracle · Answer

Hi @Nathan CCC

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

Regards

Ankur

Nathan CCC · Answer

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.