Incorrect results from Report Studio with multiple prompted conditions on the sa

[nkt123]

Hello All,

2 relational tables. One with Users, one related by UserID to another table with courses taken (and date, grade, etc). Users need a report where they can do somewhat complex queries by prompt. I.e., show me all the users that have taken Class A OR Class B, but NOT Class C. ..show all users who have take Class A AND Class B, but NOT Class D or E.

A multi-select prompt creates an IN statment which functions fine as an OR. I have seperate prompts for classes TAKEN and NOT TAKEN with appropriate in/= or <>. The courses TAKEN propmt works fine, but the NOT TAKEN criteria for class brings back all or nothing. I know this relates to the one-to-many situation of classes per user, but I've tried everything I can think of. It's easy enough in SQL using subqueries, but....  Suggestions?

[Lynn]

If the user hasn't taken a course then you wouldn't find a record at all for that student/class combination in the courses taken table, right?

The "NOT TAKEN" criteria in SQL terms would be something like using a sub-query with a not exists condition, as I think you are alluding to.

Looking for a situation that doesn't exist is different than filtering a not equal condition.

Do you have a master list of classes available? If you outer join your courses taken with the master list you could coalesce a column so that not taken classes could be easily identified, then use that condition in your filter.

If you had one student and a total of 4 classes, the result of the outer join would be as below. The classes taken would show an actual grade, but the classes not taken would show whatever dummy value you assign in the coalese statment (I picked Z).

Code Select Expand


Lynn   Class1     A
Lynn   Class2     A
Lynn   Class3     A
Lynn   Class4     Z


The filter would evaluate both prompt selections in conjunction with the grade:

( [Class] in (?SelectClassesTaken? and [Grade] <> 'Z' )
or
( [Class in (?SelectClassesNotTaken? and [Grade] = 'Z' )

You could also try playing around with except or intersect.

Friday ramblings should always be taken with a healthy dose of skepticism  :o

[nkt123]

 ;D

Perfect!! I sort of simplified my query for explanation's sake, but a combination of EXCEPT, INTERSECT and a JOIN got me the correct results.
I can't thank you enough! I can have this one done and out before the end of the day.

Have a great weekend, Lynn!

[Lynn]

Hooray! Glad it worked out!