0

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?

rdittmer
  • 13
  • 1
  • 4

1 Answers1

0

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)

Vova Vasiuta
  • 99
  • 2
  • I do the second example all the time. What I need is how to pass a multiple select parameter to a function. There is one particular multiple select parameter that I have that requires me to use a different query, so I want to write a single PL/SQL function that will run one of two queries based on whether the user selects a value in this particular parameter. I wrote a function that does this just fine in SQL Developer, but BIP gives me an invalid number of parameters error when I try to use it. – rdittmer Dec 09 '14 at 09:09