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
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!!
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
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!!