Friday, May 11, 2007

Multiple selection for Parameter values in XML Publisher

With working BIP Enterprise 10.1.3.2, we can't use all three options (Single, Multiple and All) with using of Oracle provided options in BIP. You can use only two options of them at runtime.

Symptoms:

****CASE 1 ************
select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTSwhere DEPARTMENT_NAME IN (:myParameter)
  • All does not work
  • Single Selection is working
  • Multiple Selection is working

***********CASE 2 ************
select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTSwhere DEPARTMENT_NAME IN (nvl(:myParameter, DEPARTMENT_NAME))
  • All is working now
  • Singe selection is working
  • Multiple selection is not working any more (ORA-00909: Ung├╝ltige Anzahl von Argumenten / illegal amount of arguments)..

***********CASE 3 ************
select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTS where DEPARTMENT_NAME IN (:myParameter) or :myParameter IS NULL
  • All is working
  • Singe selection is working
  • Multiple selection is not working (ORA-00920: Ung├╝ltiger relationaler Operator / illegal relational operator)

Solution:

select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTS
where ((coalesce(null, :Parameter) is null) or (DEPARTMENT_NAME in (:Parameter)))

14 comments:

Tim Dexter said...

Hi Jayu
Glad to find others blogging about publisher. Can I add you to my blog roll at the oracle publisher blog ?
http://blogs.oracle.com/xmlpublisher
Tim

Jayu said...

Sure Tim. I've already seen my blog there and sorry for the late response.

Killer said...

Hi,
The solution u provided is good but does not work when multiple values are selected from the list.
Suppose, we select department as 'Account','HR', so the output should be based on 'Account' or 'HR' department.
But BI publisher treats this as a single string as "'Account','HR'" so none of the values are selected.
Please,Provide the solution for the same.

Killer said...

Adding to my previous comment....
This can be resolved by using the instr function. Following is the example
( coalesce( :Parameter, null) is null or instr(',' || :Parameter || ',' , ',' || DEPARTMENT_NAME || ',')>0
or instr(',' || :Parameter || ',' , ',''' || DEPARTMENT_NAME || ''',')>0)

Explaination of the conditions:
First we check whether the parameter contains any value or not with coalesce.
Second Condition : If the user selects a single parameter then the value will be received as "Account" (without quotes) then we insert a comma (before and after the value). So the value becomes (,Account,) and then we search the same value in the field with the help of instr function. Comma is put to ensure that instr does not for embedded seraching.
Last Condition : If the user selects the multiple values, say 'Account','HR' (values contains single quotes in case of multiple selection), then we insert a comma (before and after the value) in the parameter and while searching we put single quoted and comman in the field name.

Anonymous said...

Hi All,

My problem is also a similar case...I need to work out the query for all the 3 cases except for all, I need to pass -1...so that if I give -1 in the parameter the query should select all the values...

But when I use coalesce, in my case its not working out..feedback is appreciated...

Anonymous said...

to display all, please add the code in Template

Anonymous said...

It is a special case..

Anonymous said...

I want not acquiesce in on it. I assume warm-hearted post. Specially the title-deed attracted me to read the sound story.

Anonymous said...

Genial dispatch and this mail helped me alot in my college assignement. Say thank you you seeking your information.

Anonymous said...

Nice article as for me. It would be great to read more concerning this topic. Thanks for posting this material.

Anonymous said...

I really like when people are expressing their opinion and thought. So I like the way you are writing

Anonymous said...

Amiable brief and this enter helped me alot in my college assignement. Thank you seeking your information.

Anonymous said...

As a matter of a fact this is not news for me. But still thanks to the author.

Anonymous said...

I will add your site to favorites. Great work!