How to repeat different data with common same data for display?

[sjdig]

Good afternoon, I am attempting to create a report identifying those households where multiple people live at the same address. I've got all my data configured. However, I am having trouble displaying the report in the manner in which I would like.

Essentially, right now the report is displaying as follows:
John Doe | 123 Fake Street | Fakesburg | 01234
Jane Doe | 123 Fake Street | Fakesburg | 01234

This is how I would like the report to display:
John Doe | Jane Doe | 123 Fake Street | Fakesburg | 01234

I would like to avoid grouping in the display at the end as this will be output to Excel to run through a formula for a mailing list.

I have limited experience with Cognos, so perhaps I am overlooking a simple solution that would be helpful for this. For the most part, I have just built lists and not used any of the other report options available for display.

Any help would be most appreciated,
sjdig

[tjohnson3050]

Specify the requirement a little more. 

What if there are three or more names for an address, do you want a separate column for each one found, or only two names?  If there are more than two, only take the first two?  Based on what sort criteria?

Assuming you want only two names, you are talking about pivoting data really.  If you create a sort key by doing a running count of name for address, you can create a data item for the first and second names at an address based on a running count.

Start with one query:

NameNum = running_count([Name] for [Address],[City],[ZIP])

NameNum | Name | Address | City | ZIP
1 |John Doe | 123 Fake Street | Fakesburg | 01234
2 |Jane Doe | 123 Fake Street | Fakesburg | 01234

Then create a second query that uses the NameNum to create the new columns

Name1 = case when [NameNum] = 1 then [Name] else NULL end
Name2 = case when [NameNum] = 2 then [Name] else NULL end

Name1 | Name2 | Address | City | ZIP

[sjdig]

Specify the requirement a little more. 

What if there are three or more names for an address, do you want a separate column for each one found, or only two names?  If there are more than two, only take the first two?  Based on what sort criteria?

Assuming you want only two names, you are talking about pivoting data really.  If you create a sort key by doing a running count of name for address, you can create a data item for the first and second names at an address based on a running count.

Start with one query:

NameNum = running_count([Name] for [Address],[City],[ZIP])

NameNum | Name | Address | City | ZIP
1 |John Doe | 123 Fake Street | Fakesburg | 01234
2 |Jane Doe | 123 Fake Street | Fakesburg | 01234

Then create a second query that uses the NameNum to create the new columns

Name1 = case when [NameNum] = 1 then [Name] else NULL end
Name2 = case when [NameNum] = 2 then [Name] else NULL end

Name1 | Name2 | Address | City | ZIP

Okay, so we are attempting to build a household list for an upcoming mailing for my institution.

Essentially, I want all possible names residing at the same address.

However, the business unit has yet to define a "household" for me. Essentially, I am attempting to build something out in advance and then work backwards once that is defined.

Therefore, if we had:
| John Doe | 123 Fake Street | Fakesburg | 01234
| Jane Doe | 123 Fake Street | Fakesburg | 01234
| Mark Doe | 123 Fake Street | Fakesburg | 01234

I would want it as:
| John Doe | Jane Doe | Mark Doe | 123 Fake Street | Fakesburg | 01234

I will attempt your suggestion and see where I get.

Thank you for your response,
sjdig

[Invisi]

My suggestion is that you let the data warehouse designer do its work before you try to do some magic in Cognos. In my opinion, a well designed star schema makes your life much easier.

[sjdig]

I figured out how to do this using a repeater table at the display level. Thank you to those who responded. The suggestion of using running-count was helpful as I used that to find the maximum number of names which I was then able to use for the maximum size needed for the repeater table for the display at the end.  :)