Cognos Report Studio / Concur Reporting - multiple rows into one

Question

first off, i am new not only to cognos report studio, but to working with databases/tables and queries in general. I am tasked with building reports using sap concur's implementation of IBM Cognos report studio (v10.2.1). That means the data model is defined by the concur people.

I am using 5 queries joined together in 4 Joins and now have all the columns and values the way i want them in the report (through more trial and error than i am comfortable with, to be honest), except for one thing: Adding the column "Comments" generates duplicate rows, one for each comment. There can be no comments, 1 comment or multiple comments per item. So what i want to achieve is concatenating all comments (if there are any) into the same row.

I tried using a repeater doing the following steps:

1. adding the repeater from the toolbox giving it the same query as the list
2. "unlocking" the report to be able to make changes to the layout
3. placing the data item from the comment column into the repeater
4. placing the repeater containing the data item back into the column

Unfortunately this doesn't change anything. Any help would be much appreciated.

PS: Of course i used the available training resources (manuals & recorded training sessions) provided by concur concerning their reporting solution. However, these are rather low level and very light on the issue of joining multiple queries.

Answer 1

The steps assume you have already got Associate in Nursing existing list with the columns, aside from comment, that you simply want:

• Move as well as create a new list object to the end of the initial list and choose from the conversation that came up the correct query.
• You can see the outer list's last column containing its own unoccupied list.
• Push and locate the comment section in the inside list
• Choose the object from the list inside. it either click on the three red dots in the list's top left corner or select them from the various properties dialog.
• Click the property of Master Detail Relationships.
• Click the New Link button at the top and try to select the right and left columns and click ok again

Answer 2

This is serendipity. I was the principal modeler and report developer for Concur's Cognos solution for five years. It's definitely a small, small world.

As to your issue, I'm not sure you can successfully do what you are trying to do which is take a multiple rows from a table and merge them into a single column. In theory, you could do multiple self-joins, one for each comment and then concatenate the results but this would be both unwieldy and you'd have to set a limit as to how many comments would be supported this way as you'd have to have a manually defined join for each.

I did some exploring to see what can be done to solve your problem. I think your best bet is to use a Master Detail pattern. This embeds a list inside another list and establishes a relationship between the two using equivalent data items that exist in both queries. The disadvantage is that it will take up a lot of space as each comment will get its own row in the detail portion of the master/detail.

The basic steps are outlined-below. The steps assume you already have an existing list with the columns, other than comment, that you want:

1. Drag over a new list object and add it to the end of the existing list and select the appropriate query from the dialog that comes up.
2. You should see the last column of the outside list now contains it's own empty list.
3. Drag over the comment column and place it in the inside list
4. Select the inside list object. Either click on the three red dots at the top left corner of the list or select it from the properties dialog.
5. Click on the Master Detail Relationships property.
6. Click on the New Link button at the top and select the equivalent columns on both the left and right and click OK

When you run the report, you should see now all the associated comments in their own list within each row.