Home » Developer & Programmer » Reports & Discoverer » Discoverer 11g - Nested parameter condition issue (Oracle BI Discoverer 11g (9.0.4.00.00))
Discoverer 11g - Nested parameter condition issue [message #619074] Thu, 17 July 2014 16:38 Go to next message
5FingerDiscount
Messages: 1
Registered: July 2014
Junior Member
I've tried googling the issue, but couldn't find a solution so I ask for your help.

Version:
Oracle BI Discoverer 11g (9.0.4.00.00)
Oracle Business Intelligence Discoverer Plus 11g (11.1.1.3.0)

What I am trying to do:
First, the report should allow the user to enter multiple different parameters. Both of which are supposed to be optional to enter. I have no problems creating 2 separate conditions and asking for the parameters separately, but this creates an 'AND' instead of an 'OR' scenario.
Ex.
select * from a
where a.column1 in (:first_value)
and a.column2 in (:second_value);

What I am trying to do is use an 'OR' condition instead so that it returns values for any combination entered.
Example of what it should be:
select * from a
where (a.column1 in (:first_value) or a.column2 in (:second_value));

Issue:
I've created 1 Condition and used the advanced options to create the 'OR' scenario. However, when I enter 1 parameter and leave the other blank, the report returns all values for the value I entered as well as all values for the second parameter. The second parameter ignores my null entry and instead pulls all combinations possible.
I've attached a screenshot of how I created the condition.

The only way I was able to get it to work using this condition setup was by entering a dummy value into my 'empty' parameter (essentially any value that was not in the LOV of the 'empty' column).

I've tried different methods of creating the parameters, but still doesn't work.
- Tried NULLIF, DECODE in a calculation to try and set the empty parameter value to NULL
- Created each parameter conditions separately then used them in a new condition
- Tried used the = operator instead of IN
- Unchecked 'allow users to enter multiple values' in the parameters settings

I've pulled Discoverer's generated SQL and it works perfectly fine, just inside Discoverer it's as if Discoverer is interpreting the empty parameter as an 'ALL' and just pulls all records. I feel like this is a simple fix, maybe some setting I am missing...


TLDR:
Nested parameter conditions using 'OR' not filtering dataset correctly
  • Attachment: discparam.png
    (Size: 43.92KB, Downloaded 1379 times)
Re: Discoverer 11g - Nested parameter condition issue [message #619199 is a reply to message #619074] Sat, 19 July 2014 03:55 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As noone replied yet (true, it is weekend, there's still hope), here you go: unfortunately, OraFAQ lacks in people who use Discoverer and most of those questions remain unanswered. As I said, I *hope* someone will assist. However, consider asking for help elsewhere, such as OTN forums.
Previous Topic: how to write CF in the Report
Next Topic: what is this Sign Star At Field?
Goto Forum:
  


Current Time: Fri Mar 29 10:41:08 CDT 2024