Cognos BI Newbie - Multiple Cust Phone Recs - Need One Rec Static Phone Typ Cols

[jeffreywstevens]

I am new to this system.  If I could skip the report writer I would.  I also have limited control over the SQL.  Maybe I could do some unions or joins, groupings, crosstabs, etc.

From the simple data sources adding to the reports, customer records get rendered this way.

Customer Key Phone Type Phone Number Other Columns, etc.
00000000001 HOME          555-555-5555
00000000001 WORK          555-555-5555
00000000001 CELL            555-555-5555
00000000002
00000000003 WORK          555-555-5555
00000000003 CELL            555-555-5555

Needs to get rendered this way. 

Customer Key WorkPhone      CellPhone        HomePhone    All other columns
00000000001 555-555-5555 555-555-5555 555-555-5555
00000000002
00000000003 555-555-5555 555-555-5555

This is a very specific output for integration.

Can someone help me, a newbie, out?

[BigChris]

Hi,

In your Query, you could create the following columns, which I think would do the job for you:

• [Customer Key]
• maximum(if([Phone Type] = 'HOME') then ([Phone Number])) else (Null)
• maximum(if([Phone Type] = 'CELL') then ([Phone Number])) else (Null)
• maximum(if([Phone Type] = 'WORK') then ([Phone Number])) else (Null)
• maximum(if([Phone Type] not in ('HOME', 'CELL', 'WORK') then ([Other Columns]) else (Null)

[jeffreywstevens]

This works and even groups the query automatically!  You rock! 

Now I have to back track.  I went full SQL with PIVOT. 

With COGNOS, it should be "never go full SQL unless you have too."   I to join this with multiple address tables, etc. so I may not be so lucky with them.