To display values passed in a parameter of type "Menu" in the Data Set as a field!

User_39I9Q

Content

I have a requirement where I need to fetch multiple values passed in the parameter(Menu) in the Data set as a field.

Using "SELECT :P_PARAMETER_NAME FROM DUAL" in the dataset works only in case of single value passed in the parameter. It does not display any value in case of multiple values selected in the menu.

Attaching the screen shot of above scenarios. Please suggest how can we achieve this in BI Publisher.

Thanks,
Ganapati Keni

SantoshKumarBhairi

Do you want to select multiple values from the prompt? If so, try go to 'Parameters' -> P_YEAR -> select 'Multiple Selection'?

User_39I9Q

"Multiple Selection" is already enabled.  When we select multiple values from the Parameter, SQL "SELECT :P_YEAR FROM DUAL" doesn't result any values.

SantoshKumarBhairi

can you share the sql behind your data-set 'TEST_DS' to check the operator being used?

SankarBalu

Hi Ganapati,

Check the value, when multiple values were chosen.
Its a collection and It was bundled with square parentheses [].

Check the below SQLs.

SQL1:

SELECT '2018' P_YEAR FROM DUAL WHERE '2018' IN (:P_YEAR)
UNION ALL
SELECT '2019' FROM DUAL WHERE '2019' IN (:P_YEAR)
UNION ALL
SELECT '2020' FROM DUAL WHERE '2020' IN (:P_YEAR)

SQL2:

SELECT LISTAGG(P_YEAR, ',') WITHIN GROUP (ORDER BY 1) P_YEAR
FROM
(
SELECT '2018' P_YEAR FROM DUAL WHERE '2018' IN (:P_YEAR)
UNION ALL
SELECT '2019' FROM DUAL WHERE '2019' IN (:P_YEAR)
UNION ALL
SELECT '2020' FROM DUAL WHERE '2020' IN (:P_YEAR)
)

Check this Oracle Note: Doc ID 2020360.1

SELECT PARTY_ID FROM HZ_PARTIES WHERE (COALESCE(null, :P_PARTY_ID) is null) OR (party_id IN (:P_PARTY_ID))
//
Can Select All — Inserts an "All" option in the list.
When the user selects "All" from the list of values, you have the option of passing a null value for the parameter or all list values.
For a long list we recommend NULL Values Passed.
In either case ("NULL Value Passed" or "All Values Passed") must handle null values in the list that is passed.
//

Thanks,
Sankar Balu