Filter a query by the result of another query

[Kanebuddy]

Hi There,
thats probably an easy one but somehow I am struggling to get that running.
I am trying to get a filter set from a query. That result set will be dynamically changing so I cant use  fixed expresssion.

Table A has about 2000 usernames -> column usernames from Query1
Table B has about 100 usernames -> column adminusers fro Query2

Now I want to query the 100 usernames from B and reduce the result from A by that
I can do something like:

[User Name] in ([Query2].[Adminusers])   (this will show me the fraction of the admin users contained in Table A, nearly all 100)

but I cant run

[User Name] not in ([Query2].[Adminusers])

What I would like to get is 1900 Users, 2000 from A  minus 100 from B.
The result is always empty. Any idea what I am doing wrong or how that can be achieved? This is running against sql server and I am using Cognos 10.2

Thanks!
K

[dougp]

Have you tried joining the queries?

[sdf]

orif possible you can create another table out of A and B.

[HalfBloodPrince]

You can use except operation in report studio /in FM

[Kanebuddy]

ok, thanks for the help guys. The join seems to be the only way.

With a cardinality of Table A: 1:1 and Table B: 0:1
I get the full dataset and then do a filter like
[Query2].[Users] is null
in the resulting join.

cheers,
K