Checking Groups for Distinct Values and Nulls

[Nicholas]

Hello everyone,

I've found myself suddenly deep in Cognos as an admin and could use some reporting advice.   :)

I'm trying to create a report which displays possible duplicate sales items in an order to determine which sales items need to be merged or deleted. I'm regarding a duplicate set as having the same name or process ID or other value like telephone.  Oh, also I'm using a relational package.

The issues I'm having are regarding the "process ID" in my sample table below. If a process ID exists and is distinct, the sales item or row is valid. A sale can have many detail rows, however if two rows in the same sale have different process IDs they're not a duplicate.

The logic I'm trying to accomplish: If there's two process IDs and a Null for the sale, I want to list it in the report. There can be X number of null value process IDs for a sale, and if there's at least one process ID, I want to list it in the report. If there's two null process IDs for a sale and no valid process IDs, I also want to list it in the report.

Here's a sample table:


     
         
         
         
         
         
         
         
         
         
         
         
         

| Row |	| R-Count |	| Sale Name |	| Process ID |	| Name |	| Phone |	| Stage |	| Dupes |
1	1	#2 April 20th	00001	John Doe	555-5555	Open	4
2	1	#2 April 20th	00002	John Doe	555-5555	Open	4
3	1	#2 April 20th	NULL	John Doe	555-5555	Open	4
4	1	#2 April 20th	NULL	John Doe	555-5555	Open	4
5	2	#5 May 2nd	0001A	Tomas Smith	NULL	Open	2
6	2	#5 May 2nd	0002A	Tomas Smith	NULL	Open	2
7	3	#29 June 9th	1000	Dan Richards	555-1111	Open	2
8	3	#29 June 9th	NULL	Dan Richards	555-1111	Open	2
9	4	#7 July 1st	NULL	Chris Mason	111-5555	Close	2
10	4	#7 July 1st	NULL	Chris Mason	NULL	Close	2

Hopefully someone has an idea how I could do this. I've been banging my head against this for a few days. I can accomplish most of what I want in SQL with some temp tables or a CTE or two, but it would be a pain and I need to know how to do this in SQL to help other users create additional reports.

Thanks!

-Nicholas

[bdbits]

I would probably build a query subject that returns a list of the Sales Names (assuming that is the key) that meet the conditions. As you said you can determine the rows you want with SQL, it should be straightforward to build the query subject. You can also create joins in FM from your new query subject to whatever related tables the users would want to be using in their report, so that the joins are automatically built properly and do not have to be figured out in Report Studio.

I know that is kind of high-level but maybe it will help.