Passing multiple values through a Presentation Variable in a Direct Database Request

0
votes

I have created multiple Direct Database Requests in OBIEE, I am able to pass a presentation variable to a Dashboard Prompt. However I am only able to select one value, if I select multiple values, then I get an error saying No results found. I have researched this on the Oracle community but have yet to find a solution, all similar questions have remained unanswered that I found.

When I select multiples values I notice that they are being passed as one long string. eg. 

GL_Period IN ('APR-19,AUG-19,FEB-19,JAN-19,JUL-19,JUN-19,MAR-19,MAY-19,NOV-19,OCT-19,SEP-19')

There are beginning and ending quotes instead of quotes around each value.

My Question is can anyone help me get this prompt to accept multiple values. I have tried to update the SQL behind the dashboard prompt, my thinking was that if I concatenate ' to the beginning and end of each value. Then TRIM the first and last ' off that this would work. However I have been unable to get the syntax correct for this, if it is even possible.

Here is the code for my Presentation Variable in my Direct Database Request

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

I am able to use the following code to add ' ' to each value - This is for the Presentation Variable P_Period

SELECT CHAR (39)||"Time"."Fiscal Period"||CHAR (39) FROM "Financials - AP Transactions")

I tried the below query to combine the two but to no avail

TO_CHAR(TRIM(BOTH ''' FROM SELECT CHAR (39)||"Time"."Fiscal Period"||CHAR (39) FROM "Financials - AP Transactions"))
2
obieebi-publisher

2 Answers

1
votes

Use gl_period_name IN (@{P_Period}['@']{''}) to pass multiple values. Only problem is this will give you no results if no value is passed (I haven't figured out how to solve that issue yet).

0
votes

You can turn the comma separated string into multiple values with a Recursive CTE

WITH CTE AS (SELECT 'APR-19,AUG-19,FEB-19,JAN-19,JUL-19,JUN-19,MAR-19,MAY-19,NOV-19,OCT-19,SEP-19' TEMP FROM DUAL)
SELECT TRIM(REGEXP_SUBSTR( TEMP, '[^,]+', 1, LEVEL)) Period FROM CTE 
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(temp, '[^,]+')) + 1

Replacing the hard-coded string with your variable, put that into a sub-query in your where clause and you'll be good to go. Something like this:

select 1 TEST
from dual
where 'APR-19' IN (WITH CTE AS (SELECT 'APR-19,AUG-19,FEB-19,JAN-19,JUL-19,JUN-19,MAR-19,MAY-19,NOV-19,OCT-19,SEP-19' TEMP FROM DUAL)
SELECT TRIM(REGEXP_SUBSTR( TEMP, '[^,]+', 1, LEVEL)) Period FROM CTE 
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(temp, '[^,]+')) + 1)