OBIEE中演示变量传递多值至直接数据库请求的问题求助

Got it, let's tackle this multi-value prompt issue you're facing. The core problem is that when you select multiple values in your prompt, OBIEE passes them as a single comma-separated string wrapped in one pair of quotes—like 'APR-19,AUG-19'—instead of individual quoted values ('APR-19','AUG-19'), which breaks the IN clause. Here are two reliable ways to fix this:

Option 1: Split the Variable String in Your Direct Database Request #

Instead of modifying the prompt, adjust your direct database request SQL to split the comma-separated variable value into individual entries. Use this conditional logic:

AND (
    -- Handle multi-value selections by splitting the string
    gl_period_name IN (
        SELECT TRIM(REGEXP_SUBSTR('@{P_Period}', '[^,]+', 1, LEVEL))
        FROM dual
        CONNECT BY REGEXP_SUBSTR('@{P_Period}', '[^,]+', 1, LEVEL) IS NOT NULL
    )
    -- Handle the "All" selection case
    OR 'All' = '@{P_Period}{All}'
)

How this works: #

• The REGEXP_SUBSTR function splits the @{P_Period} variable string at each comma, returning one value per row.
• The CONNECT BY clause generates enough rows to cover all split values.
• The TRIM removes any accidental whitespace around values.

Option 2: Modify the Dashboard Prompt to Return Quoted Values #

If you prefer to handle this at the prompt level, fix your prompt's SQL to return each period value wrapped in single quotes, then adjust your direct database request to use the variable directly in the IN clause.

Step 1: Update the Dashboard Prompt SQL #

Replace your current prompt SQL with this:

-- Return quoted values for multi-value compatibility
SELECT CHAR(39) || "Time"."Fiscal Period" || CHAR(39) AS period_value,
       "Time"."Fiscal Period" AS period_label
FROM "Financials - AP Transactions"
-- Add the "All" option
UNION ALL
SELECT '''All''', 'All'

• The period_value column returns each period wrapped in single quotes (e.g., 'APR-19'), while period_label keeps the human-readable name for the prompt dropdown.
• The UNION ALL adds the "All" option with a quoted value to match the format.

Step 2: Adjust the Direct Database Request Condition #

Update your direct database request's conditional logic to:

AND ( gl_period_name IN (@{P_Period}) OR '''All''' IN (@{P_Period}) )

Now when you select multiple values, OBIEE passes them as 'APR-19','AUG-19'—exactly the format the IN clause expects.

Why Your Previous Attempt Failed #

Your combined code had invalid syntax because you tried to nest a SELECT inside TO_CHAR directly. Instead, you need to generate the quoted values in the prompt's SQL first, then reference the variable without extra wrapping in your direct database request.

Quick Check: #

Make sure your dashboard prompt has the "Allow multiple selections" checkbox enabled, and that the variable P_Period is scoped correctly (to the page or dashboard) so it's accessible to your direct database request.

内容的提问来源于stack exchange，提问作者scubbastevie