Easy question with OR statement on Prompt -issue RESOLVED :)

[Anke]

Hello!
  I have 2 prompts on the prompt page.
I would like for the user to select either promptA or PromptB or both.

My filter on Qry1 is Optional: says [field 1] or [field 2].

If the user picks prompt 1, then I get the correct value.  If the user picks prompt 2 only and leaves prompt 1 blank, then I get all.

How can I have two prompts on a prompt page, and let the user pick either prompt or both and get correct results? How do I write that in the filter??

Thanks :)
:)

[MFGF]

You should probably use two separate, optional filters, one for each prompt.

Regards,

MF.

[Anke]

Yes,
  I tried that.
I am then able to get all of prompt1 and leave prompt2 blank
OR
all of prompt2 and leave prompt1 blank.

BUT
I cannot pick prompt1 and prompt2 and get both....is this a limitation of the program or me?

Thanks  :P

[MFGF]

Hmmmm... Are the prompts mutually exclusive? I was assuming that you wanted rows that corresponded to both prompts (eg Product = 'X' and Region = 'Y')  What logic are you looking for overall - the filters being combined with AND or OR?

MF.

[Anke]

Both prompts are optional...

So user could get X or Y...or X and Y.

I can get X and Y by using one filter with an OR
OR
I can get either X or Y by using two separate filters.

I cannot get all :(
They want both prompts or either prompt....I do not think this is possible ??

Code Select Expand


select empl_num from emp_certi
where (<optional prompt> in list
OR <optional prompt> in list)
   and empl_num  in   (select empl_num from perTB
where status <> 'N')

[Riotknight]

You could try a case statement in the filter checking if the values are empty like
case
where (x <> '' and y <> '') then
     all
where (x <> '' and y = '') then
     just x
where (x = '' and y <> '') then
    just y
else
    whatever you want to do if they put nothing
end

if that helps

[Anke]

Where you say
where (x <> '' and y <> '') then
     all

They really want ...where X = a,b,c, and Y = a,b,r,c,h)
then show X and Y....

There are 2 prompts...some emp could have prompt1, some prompt2 or show list of emp with in both prompt1 & 2.

I am not sure a case statement will work where I need to show both X and Y....I'll give it a try though...I hadn't thought of trying a case statement in the filter :)
Thanks,
anke

[MFGF]

What do you mean when you say "I can't get all"? Do you mean that no rows are returned when you select an item in both prompts? This might actually be valid, if no rows contain the two values you selected. Is this possible?

MF.


Sent from my iPad using Tapatalk

[Anke]

I can get either all from prompt one (optional) or all from prompt two (optional)...I cannot get results if I pick both prompt1 and prompt2....yep, there should be results when both are picked...so right now, I have 2 reports one where they have to pick from prompt 1 & 2 and one where prompt 1 and 2 are optional.

still no progress  :'(

[absriram]

Anke,

What is the data item that you are filtering on.  Are the two prompts filtering on the same data item?

If you can give an example, it might be easier.

Thanks,
Sriram
http://cognosonsteroids.blogspot.com

[Anke]

This is what I am trying to do in one query filter.  There are 2 prompts and one query....

in SQL, I would be doing this (below), in the SQL, I had hardcoded 2 values, but I want these values to be optional to the user to pick, either or or both values.  I do not think this is possible to do in one query filter.

So, for example...say I have to prompts....one has a list of Firstnames, and the other has a list of Lastnames.  I want to select from prompt A and get the list of first names, I want to select from prompt 2 and get a list of last names, or I want to pict from prompt A & B and see the big list of all first, all last, and all first and last that match and the strays where I may have picked from either list and they do not match up with any. 

So, I have been able to make prompt A & B required and get a list of matching first and last.  I have also been able to get a list from prompt A or Prompt B....but I am unable to get all scenarios together.  I know there is a way to do this, but I do not have the answer....I don't think it is possible to do using one query and filters for both prompts in it.  :)

Code Select Expand

select empl_num from emp_certi
where (<optional prompt> in list
OR <optional prompt> in list)
   and empl_num  in   (select empl_num from perTB
where status <> 'N')


Thanks so much for the insight!!   :D

[absriram]

Anke,

You should use one filter expression to do this. Since you want the prompts to be optional, here is what you can do:

I am assuming that you have two value prompts ?first_name? and ?last_name?

1. Set both prompts to be "Required".
2. In both the prompts add a static choice 'All'.
3. Set the "Default" value property of both prompts to 'All'.
4. Now, your filter expression should look like this:
       ([First Name] in (?first_name?) and [Last Name] in (?last_name?))  OR
       ([First Name] in (?first_name?) and ?last_name? = 'All')  OR
       (?first_name? = 'All and [Last Name] = ?last_name?)

Hope this helps,

Sriram.
http://cognosonsteroids.blogspot.com

[Anke]

Interesting...let me work on this idea and get back with you :)
Thanks!
Anke

[Anke]

Hi all :)
I wanted to thank everyone for their advise...and give the solution to the report issue.

Okay, there were 2 prompts on the prompt page, and now there is a third prompt with static values 1,2,3 and display text as follows:
1. Use both prompts
2. Use left prompt
3. Use right prompt

These values are then used in the Query filter (optional filter) to determine which statement to apply as the filter:

If #1, then name in prompt 1 OR name in prompt 2
if #2, then name in prompt #1 (left prompt)
if #3, then name in prompt #2 (right prompt)on

Thank you for all of your awesome instruction!!
Anke