Cognos 11 report listing contact phone number

Question

I am hoping to get some assistance with creating a Cognos report. I have some experience making basic reports but I feel out of my depth here.

The following are the fields I have available:
Contact ID
Phone Type
Phone

Table resembles this:

Contact ID	Phone Type	Phone
1	C	1234567890
1	H	1234567890
2	M	1234567890
2	H	1234567890
3	H	1234567890
3	W	1234567890

Contact ID and Phone are integers. Phone Type can either be C, H, W, or M for Cell, Home, Work, and Mobile respectively. Contacts don't always have all types listed but can.

What I need: A two column list, first column is the Contact ID, the second column Phone, with each contact only having 1 row in the report. I need the following priority: C > M > H. I don't want the Work phone at all. So if they have a Cell phone listed, I want that number. If they don't have Cell or Mobile, but have a Home phone listed, I want that number. Output should look like this, which will be exported in CSV:

Contact ID    |   Phone
_____________________________
10292         |   1234567890
102847        |   0987654321

I tried doing a Join, with query 2 providing the Cell number and query 3 providing Home. My Join is structured like this:

             Query 2
            |
Query 1  <-- 
            |
             Query 3

I then had an IF statement in the Phone data item of the List (query 1) like this:

IF ([Query2].[Cell Phone] is not null)
THEN ([Query2].[Cell Phone])
ELSE
IF ([Query3].[Home Phone] is not null)
THEN ([Query3].[Home Phone])
ELSE null

I was not able to get the desired results because there are users that do have a Home phone type which are not in the report output. I am not clear on how the cardinality works, so that might have been a part of the issue. The output I am getting looks right, but when I dig into it, I have found data missing. At least 1 contact that only has a home phone type is not included in the output.

Please let me know if I need to provide any other useful details. Thanks in advance!

Answer 1

Ultimately, you'll want something like this basic SQL:

COALESCE([Cell], [Mobile], [Home])

COALESCE() should work fine in Cognos.

Answer 2

You could create an expression to detect the types that exist and a case statement to select the desired value to show

For example, create 3 new data items to figure out if an entry exists This flattens out the results so you can show the id and desired number

Data Item CType

If ([Phone Type] = 'C')Then([Phone])else(Null)

Data Item MType

If ([Phone Type] = 'M')Then([Phone])else(Null)

Data Item HType

If ([Phone Type] = 'H')Then([Phone])else(Null)

Then a coalesce or case statement

Coalesce([CType],[MType],[HType])

or if you want this would also work

case
when ([CType] IS NOT NULL)Then([CType])
when ([MType] IS NOT NULL)Then([MType])
when ([HType] IS NOT NULL)Then([HType])
else(Null)
end

<table border=1>
<tr>
<td>ID</td><td>Phone Type</td>
<td>Phone</td>
<td>C Phone</td>
<td>M Phone</td>
<td>H Phone</td>
</tr>
<tr>
<td>1</td><td>C</td><td>5554567890</td><td>5554567890</td><td></td><td></td>
</tr>
<tr>
<td>1</td><td>H</td><td>1114567890</td><td></td><td></td><td>1114567890</td>
</tr>
</table>

For example