Matched query in report studio? How to Join.

[jcrouch]

Hi All.

I have a query that I need to filter some items out of. I was not able to use a filter to do this. So I created a second query that contains only the items that I want displayed from the first query. In Access I can simply left join these 2 qry's and add a condition where the second qry's results are not null. I do not know how to reproduce that in Report Studio using a join.

..............

Some background as to why a regular filter is not working for me. And maybe someone has an idea on this as well: Here is the first query:

Month
Producer
Product
Sales

I am putting this into a Crosstab report with Month along the top, Producer on the left side. Also we are showing this for 12 months only. Now I need to only show Producers whos total 12 month Sales is > $####. When i try to filter this directly Cognos looks at each month seperatly, so only Producers who meet the Sales filter each month are returned.

So I setup a second qry with just the Producers that meet this goal for the entire 12 months combined.

Thanks,

Jeremy

[blom0344]

That is bit too complex. Joining the 2 queries as an inner join automatically acts as the filter. So do not use a outer join but a straight join instead!!

[jcrouch]

Thanks for the reply blom

I actually just found out how to filter the original query using a summary function with the 'for' keyword :)  Total(Sales for Producer)>=1000

But I would still like to know how to use a join quesry to do this. Could you clarify your straight join strategy? Im not sure how to do that.

Thanks,

Jeremy

[blom0344]

Let me clarify this.  Performing an inner join between query(sets) may have the same effect as adding a join to a query.
If query1 returns 100 rows (for each customer 1 row) and query2 returns 50 rows (again 1 row per customers) then an inner join will yield 100 - 50 = 50 rows.

If you can fix matters with 1 query and a bright filter then this is the preferred approach!!