Linking report prompts with an AND or an OR option?

[MPritchard55]

Hello. 

I've built a report that returns a list of courses that each faculty member taught in a given semester with a grade distribution count (# of As, Bs, etc) for each student enrolled that semester.  I have included a prompt page for the Users with a set of optional EXCLUSION prompts and optional INCLUSION prompts.  The exclusion prompts allow the user to, for instance, exclude the BIOL dept instructors/courses, any courses flagged as Private Lessons, or courses with fewer than 2 course credits.  The exclusion prompts utilize NOT IN or <> operators in the filter/prompt definition.   The inclusion prompts are an identical set of prompts but are defined by an IN or = versus a NOT IN or <>.   

The prompts work great except when I want to combine the EXCLUSION prompts to, for instance, eliminate all private lesson courses that are less than 3 credit courses.  By selecting Private Lesson='Y' and course credits<3, the report filters out any courses that are private lessons AND and courses with less than 3 credits, not the just the 1 or 2 credit courses that are Private Lessons.

So I'm wondering if there is a way to use a control on the prompt page to define the relationship between 2 prompts as and AND or an OR statement?  (kind of like you can do in Query Studio).  Or if there is a better way of accomplishing this?  I've considered cascading prompts but that doesn't really work in this situation because I still need/want the prompts to act independently. 

I've read through the Cognos User Guide and scanned for a similar topic on this forum and haven't found so I'm curious a solution exists.  Thanks.     

     

[MFGF]

Hello. 

I've built a report that returns a list of courses that each faculty member taught in a given semester with a grade distribution count (# of As, Bs, etc) for each student enrolled that semester.  I have included a prompt page for the Users with a set of optional EXCLUSION prompts and optional INCLUSION prompts.  The exclusion prompts allow the user to, for instance, exclude the BIOL dept instructors/courses, any courses flagged as Private Lessons, or courses with fewer than 2 course credits.  The exclusion prompts utilize NOT IN or <> operators in the filter/prompt definition.   The inclusion prompts are an identical set of prompts but are defined by an IN or = versus a NOT IN or <>.   

The prompts work great except when I want to combine the EXCLUSION prompts to, for instance, eliminate all private lesson courses that are less than 3 credit courses.  By selecting Private Lesson='Y' and course credits<3, the report filters out any courses that are private lessons AND and courses with less than 3 credits, not the just the 1 or 2 credit courses that are Private Lessons.

So I'm wondering if there is a way to use a control on the prompt page to define the relationship between 2 prompts as and AND or an OR statement?  (kind of like you can do in Query Studio).  Or if there is a better way of accomplishing this?  I've considered cascading prompts but that doesn't really work in this situation because I still need/want the prompts to act independently. 

I've read through the Cognos User Guide and scanned for a similar topic on this forum and haven't found so I'm curious a solution exists.  Thanks.     

   

Hi,

From what I'm reading, I'd say the answer lies in consolidating the filters currently being driven by the prompts into a single filter expression - this will allow you to code the desired combinations of AND or OR (and the relevant parentheses to group things) in the one expression.

Cheers!

MF.

[MPritchard55]

Right on.  Okay, I see what you are saying.  Thanks.  I'll give it a whirl. 

[MPritchard55]

I tried this and...no joy.  The problem is that these are optional prompts so when I put them in a single filter but only chose to use 1 of the prompts, Cognos is confused I guess and is looking for selections for all prompts.  Any other ideas?

[Lynn]

If you set default values on each of the prompt controls you would have greater flexibility in coding an expression to address the desired behavior.

The default could be a legitimate response that should be considered as the default, or you could use a dummy default value that would allow you to know the prompt wasn't responded to.

[MPritchard55]

Okay....that's what I was thinking, but wondered if there were other ways.  That's cool.  Thanks!!!   :)