Replace function is not working in OTBI Dashboard Prompt

Ankur Jain--Oracle

Hi Team ,

we have created dashboard prompt where we have two parameters . Parent department and Child Department. Parent department is sql result type and have presentation variable defined as R1. Parent department is multiple choice enabled.

Now child department is dependent on R1 input so here is query we are trying to use.

SELECT "Department"."Department Name" FROM "Workforce Management - Worker Assignment Real Time"
where "- Hierarchy"."Level 1 Department" IN ('@{R1}')
and "Department"."PER_ORGANIZATION_UNIT_DFF_LEVEL_NR_" >1

In clause is not working and we found the possible reason could be it use ; for multiple values instead of comma so we are trying to replace semi colon with comma.

Could someone help how we can replace the semi colon with comma in prompt query as replace function is not working in prompt sql.

Regards,

Ankur

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/63384#Comment_63384

Hi @Ankur Jain--Oracle,

Can you supply the default value that you want to be there, please?

The code I provided 'XYZ' is the default value. So if you just put your default value instead of XYZ, it should work fine.

Hope this help.

Cheers,

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/24472/replace-function-is-not-working-in-otbi-dashboard-prompt

Hi @Ankur Jain--Oracle,

Can you please try the following?

(@{R1}['@']{'XYZ'})

So the query will look like:

SELECT "Department"."Department Name" FROM "Workforce Management - Worker Assignment Real Time"

where "- Hierarchy"."Level 1 Department" IN (@{R1}['@']{'XYZ'})

and "Department"."PER_ORGANIZATION_UNIT_DFF_LEVEL_NR_" >1

Hope this help.

Cheers,

Ankur Jain--Oracle

Hi @Bhaskar Konar ,

Thanks for providing the syntax. Could you please let me know how it works. It is working for multi select but default value is not working .

I am still unable to understand this weird syntax.

Regards

Ankur

Ankur Jain--Oracle

Hi @Bhaskar Konar ,

Thanks . I have used my default value only.

SELECT "Department"."Department Name" FROM "Workforce Management - Worker Assignment Real Time"
where "- Hierarchy"."Level 1 Department" IN (@{R1}['@']{'ΔΙΟΙΚΗΣΗ [1]'})
and "Department"."PER_ORGANIZATION_UNIT_DFF_LEVEL_NR_" >1

I need to find out direct and indirect department if I select the parent department from first Prompt (R1) . I was checking level 1 department which is wrong. I need a condition which work hierarchical . we have level field as well . Could you please suggest something ? How I can fetch direct and indirect department based on Parent department input.

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/63386#Comment_63386

Hi @Ankur Jain--Oracle,

It seems the issue you are mentioning now is bit different from the initial one that you raised. I would recommend you to raise a new question with associated details like screenshots etc. as more fellow community members can have a look and help you to get the resolution.

Also if you got your initial question answered please select the corresponding answer as it'll help the fellow community members in future.

Thank you!

Cheers,