Show one row across rather than duplicating rows

[Revan]

I have a report that I'm running where I need to pull the establishment ID Number, name, and owner. When the establishment has more than one owner, the line is duplicated except for the second (or third, fourth, etc) owner name. How can I get it to render across rather than down?

How it looks now:
Establishment ID          Establishment Name          Owner ID     Owner
278943                         Wonderful Works               33752         Jane Doe
278943                         Wonderful Works               39827         Jill Doe
278943                         Wonderful Works               48925         John Doe

How I want it to look:
Establishment ID         Establishment Name          Owner 1          Owner 2          Owner 3
278943                        Wonderful Works               Jane Doe        Jill Doe            John Doe

I tried ranking the Owner names for ID # using this formula in a new data item

Code Select Expand

[rank([Owner ID] asc for [Establishment ID])], which seemed to work. When I pull just that query, it shows Jane Doe Rank 1, Jill Doe Rank 2, and John Doe Rank three, which is exactly what I wanted. I then copied that query so that I have three identical queries, and filtered them by Rank 1, then Rank 2, then Rank three, linked them by entity number and then made another query where Establishment ID, Establishment Name, and Owner 1 are from Rank 1 Query, Owner 2 is from Rank 2 Query, and Owner 3 is from Rank 3 query. The report, however, is showing some of the Rank 1 Owners more than once, the Rank 2 query is pulling in far too few names, and the rank 3 query has way too many AND every name on the Rank 3 Query is on the list twice! No idea what is wrong. As I said, when I just pull the original Ranked query without filtering for Rank 1, 2, or 3, it shows the correct rank for each name.

[New_Guy]

Hi,
One way to do this is with a list and a crosstab inside the list. How big of a report is this?
Good luck
New guy

[Revan]

It pulls about 5,000 rows of data. I export it into excel, then use the list to populate name, ID #'s, and addresses on a mail merge letter, so I need the data to stretch horizontally for the mail merge fields.

[Revan]

How do you mean use a crosstab inside a list? The data is currently set up in a list. How would I incorporate a crosstab to show the values horizontally?

[New_Guy]

Hi,

Step 1
take a list, add Establishment ID and Establishment Name as 1st and 2nd column
Step 2
Grab a crosstab from the tool box and drag it as the 3rd column and point the crosstab to the same query as the list.
Step 3
Place the Owners name as the column in the crosstab.
Step 4
Create a master detail by Establishment ID and Establishment Name between the list and the crosstab.
Step 5
Do box type none for crosstab rows, and fact cells.


You might have to still need to do some formatting if required. Try it and let us know if you still nedd help.

Good luck
New guy

[Ravisha]

Here is the link suggested by IBM. This would certainly help you to achieve what you are looking for. The link also has the report xml specification doc to check for how the formatting is done.

Good luck !

[Revan]

Here is the link suggested by IBM. This would certainly help you to achieve what you are looking for. The link also has the report xml specification doc to check for how the formatting is done.

Good luck !

Forgive my ignorance (I just joined yesterday), but I'm not actually seeing a link. Can you post it again, or direct me to it?

[New_Guy]

http://www-01.ibm.com/support/docview.wss?uid=swg21404363

[Ravisha]

That's the exact link which I'm talking about.

Anyway, its weird why the link didn't appear in my post ?!?!  ???

Also, does anyone know the process to delete a wrong post?

[Revan]

Ok, so that does seem to be working on a small data sample. I think I've messed something up on the actual report, because it ran for well over an hour before I just cancelled it. I've probably changed something that won't throw an error, but will keep it spinning indefinitely. I'll rebuild the report and try again. One other question, though, and it's a minor issue, but instead of having column headers for each different owner, it's just one big merged cell with one header. Is that something that can be changed in Cognos? If not, as I said, its minor enough that I can change it from within Excel.

Also, while researching this issue, I saw a post where someone else had solved a similar problem using repeater tables. Any chance you could link me to an article on how to use those, like the one posted above for crosstabs?

Thanks so much for the assistance, New_Guy and Ravisha!

[Revan]

Sooo....is it a problem to put a crosstab in between two data items in a list? When I put the crosstab on the end, the report runs fine. However, if I insert it between data items, the report just runs indefinitely. Is there a way around that?

[MFGF]

Also, does anyone know the process to delete a wrong post?

Just ask a friendly moderator :)

What would you like deleted?

MF.

[AnalyticsWithJay]

I would not recommend you use this approach:

One way to do this is with a list and a crosstab inside the list. How big of a report is this?

because of this:

However, if I insert it between data items, the report just runs indefinitely. Is there a way around that?

What's your database vendor and is your data relational, DMR, etc? You could use functions like LISTAGG (Oracle), STRING_AGG (SQL Server), etc. which return a command separated list. You'll have to prefix those functions with "aggregate:" to get them to work in Cognos. Other options: You could possibly utilize stored procedures, or consider using views if need be.

There are many options, but a master-detail relationship is going to fire off a new query for every row, so expect a big hit on performance.

[Revan]

My data is relational, and I believe it is SQL, but I'm not sure. I'm asking our Information Services people, but it might be awhile before they respond.

I've noticed that anytime I run a query with a master-detail relationship, it slows down my reports dramatically. Not too big a deal on this report because it's small and runs quickly anyway, but I'll be using these techniques with other larger reports too, so a more efficient method would be nice.

I also realized after redoing the report listed above that I oversimplified it a bit in the original post. I stated that I needed the Owner names side by side, which the crosstab does (as does a repeater/repeater table, both of which I've been experimenting with). However, I actually need multiple fields to be rendered horizontally:

Original information:
Establishment ID          Establishment Name          Owner ID     Owner
278943                         Wonderful Works               33752         Jane Doe
278943                         Wonderful Works               39827         Jill Doe
278943                         Wonderful Works               48925         John Doe

How it looks now:
Establishment ID         Establishment Name                                 Owners
278943                        Wonderful Works               Jane Doe        Jill Doe            John Doe

How I want it to look:
Establishment ID     Establishment Name   Owner 1       Owner 1 ID    Owner 2      Owner 2 ID    Owner 3     Owner 3 ID
278943                    Wonderful Works        Jane Doe      33752            Jill Doe        39827           John Doe    48925

So far, I haven't been able to get either a crosstab or a repeater to allow me to split the information like this. Instead, they group all the owners together, then all the ID numbers together. If I use a repeater, it will set it up like OwnerOwner IDOwnerOwnerID, which at least gives me the list style I want, but all in one cell and concatenated.

I've also tried to use a rank formula

Code Select Expand

[rank([Owner ID] asc for [Establishment ID])] that would say Rank 1 for Owner 1, rank 2 for owner 2, etc., then create child queries from the master parent query that would filter for Rank 1, then another child query filtered for Rank 2, etc., and join them together, but that gave completely bizarre results that I have no explanation for.

[Revan]

Our database is an IBM Netezza SQL database.

[Ravisha]

Just ask a friendly moderator :)

What would you like deleted?

MF.

I would like to delete the 6th post of this thread. How would I be able to delete my own posts?

[New_Guy]

Hi Ravisha,
Did you try to modify the message you post, using the Modify Icon.
Good luck
New guy

[New_Guy]

My data is relational, and I believe it is SQL, but I'm not sure. I'm asking our Information Services people, but it might be awhile before they respond.

I've noticed that anytime I run a query with a master-detail relationship, it slows down my reports dramatically. Not too big a deal on this report because it's small and runs quickly anyway, but I'll be using these techniques with other larger reports too, so a more efficient method would be nice.

I also realized after redoing the report listed above that I oversimplified it a bit in the original post. I stated that I needed the Owner names side by side, which the crosstab does (as does a repeater/repeater table, both of which I've been experimenting with). However, I actually need multiple fields to be rendered horizontally:

Original information:
Establishment ID          Establishment Name          Owner ID     Owner
278943                         Wonderful Works               33752         Jane Doe
278943                         Wonderful Works               39827         Jill Doe
278943                         Wonderful Works               48925         John Doe

How it looks now:
Establishment ID         Establishment Name                                 Owners
278943                        Wonderful Works               Jane Doe        Jill Doe            John Doe

How I want it to look:
Establishment ID     Establishment Name   Owner 1       Owner 1 ID    Owner 2      Owner 2 ID    Owner 3     Owner 3 ID
278943                    Wonderful Works        Jane Doe      33752            Jill Doe        39827           John Doe    48925

So far, I haven't been able to get either a crosstab or a repeater to allow me to split the information like this. Instead, they group all the owners together, then all the ID numbers together. If I use a repeater, it will set it up like OwnerOwner IDOwnerOwnerID, which at least gives me the list style I want, but all in one cell and concatenated.

I've also tried to use a rank formula

Code Select Expand

[rank([Owner ID] asc for [Establishment ID])] that would say Rank 1 for Owner 1, rank 2 for owner 2, etc., then create child queries from the master parent query that would filter for Rank 1, then another child query filtered for Rank 2, etc., and join them together, but that gave completely bizarre results that I have no explanation for.

Hi,
You can create a function in the database with Owner number as the parameter and get each owner in a column or get all the owner in a single string separated by a delimiter and separate them in reportstudio using the delimiter.
Good luck
New guy

[Revan]

New Guy, can you expand on that a bit? How would I create that function to use the Owner as a parameter?

[New_Guy]

Hi,

You have to create a function with 2 parameters one for owner number and one for establishment ID. And select the owners of an establishment based on the criteria that you are getting the Owner number currently and fetch it into a variables and then return that variable as a return value of the function.
Import this function using Framework manager and add it to the package and publish the package and you can use it in a dataitem like Ownerinfo(1,[EstblishmentID]) for 1st owner and Ownerinfo(2,[EstblishmentID]) for 2nd owner and so on.

Good luck
New guy