Hi everyone,
I have a cascading prompt setup in OAS Classic (OBIEE 12c) that works for single selection but fails on multi-selection due to variable formatting.
The Setup:
- Column Prompt 1: "Cost Center" (Multi-select enabled). Sets variable
@{COSTCENTER_CONCAT_PROMPT}. - Column Prompt 2: "Organizational Unit". Filters its SQL based on Prompt 1.
The Behavior:
- Single Select: Variable =
10100 CS1. SQL WHERE "CC" = '10100' (or = 10100) works fine. - Multi Select: Variable =
10100 CS1 ,10210 CS2,10220 CS3 (No quotes, just comma-separated numbers).
The Problem:
My current SQL in Prompt 2 uses:
WHERE "Cost Center" = SUBSTRING ('@{COSTCENTER_CONCAT_PROMPT}' FROM 1 FOR 5)
The Issue:
- Single Select: Works fine. If I pick one Cost Center, Prompt 2 shows the correct Organizational Units.
- Multi Select: As soon as I enable "Enable user to select multiple values" on the first prompt and select multiple Cost Centers in Prompt 1, Prompt 2 returns no results or errors.
This fails because it searches for the single string '10100 CS1,10210 CS2,10220 CS3' instead of three separate values.
Question:
What is the correct syntax or configuration to make Prompt 2 accept a multi-select variable from Prompt 1?
Basically: How do I correctly filter Prompt 2 using a multi-selected variable from Prompt 1?
Thanks for your help!
