Oracle BI Publisher multiple select parameters

Question

I have a requirement to have multiple select parameters on a BI Publisher report. I am passing the parameters to a function that turns a comma separated list of values to a table. I then use the tables from the csv parser function in "IN" statements in the where clause. It looks likes below...

select * from table(my_report_function(my_csv_parser_function(:Parameter))

This works fine when I run it in SQL Developer. The problem is that seems when BI Publisher passes the parameter it interprets each comma as a separate parameter so I get an invalid number of parameters error. How can I stop it from doing this or is there a better way to handle multiple select parameters being passed to a function?

Answer 1

Yes, parameter is passed just as line in sql query in order to use this parameter in sql query later.

In your case it can be for example :

select * from table(my_report_function(my_csv_parser_function(1,2,3,4,5,6))

Instead you can use this parameter in yours sql query. In my case for example it is:

select *
from dev_dmart.FCT_CURRENCY_RATE
WHERE FROM_CURRENCY_CD = 840
and TO_CURRENCY_CD IN (:multiple_select_parameter_list)

In your case it can be something like this:

select * from table(my_report_function())
where field in (:Parameter)

Or you can create Event Trigger on "Before data" in Publisher and call function there which will calculate data and insert it in TABLE (for example PRECALCULATED_DATA). And in report you can then use query:

select * from PRECALCULATED_DATA where field in (:Parameter)