report based on 'not exists' table entry

[ScottOrich]

Gurus

I have table items which I want to display if another table entry does not exist. For example select all members (table #1) who do not currently have a medical savings account (table #2). How do I set this up?

 

[griffindm]

Scott,

You have two choices. Either setup the catalog with an outer join between the two tables, and then report on those where a value in the sometimes missing table is in fact missing, or use the Dataset option, where you create a simple report that lists the unique identifier in the sometimes missing table, and then make a second report on the main table and use the filter statement of:

unique_identifier Not In Dataset.

Point the dataset to the first report. This creates a subselect query where only those rows that have no corresponding value in the other table are selected.

Hope this helps.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com