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.
Quote from: MPritchard55 on 13 May 2015 03:35:04 PM
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.
Right on. Okay, I see what you are saying. Thanks. I'll give it a whirl.
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?
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.
Okay....that's what I was thinking, but wondered if there were other ways. That's cool. Thanks!!! :)