Concatinating Alternate Rows in Report Studio

[Praveena]

Hi All,
I have a query generated output in the below mentioned format.
Col1 Col2 Col3 Col4
A      B       C        D
A      B        C        D1
A      B        C        D2
A      B        C        D3
Now, my requirement is to Concatenate D and D1, D2 and D3 in Cognos. I tried various Concat options. But could not get the desired output. Kindly advise me on this. Thanks in advance!

[Lynn]

Hi All,
I have a query generated output in the below mentioned format.
Col1 Col2 Col3 Col4
A      B       C        D
A      B        C        D1
A      B        C        D2
A      B        C        D3
Now, my requirement is to Concatenate D and D1, D2 and D3 in Cognos. I tried various Concat options. But could not get the desired output. Kindly advise me on this. Thanks in advance!

Can you explain what you mean by concatenating and perhaps provide a sample layout of what you want? Have you looked into using a crosstab or repeater layout container? Concatenation is not a concept that works row-wise.

[Praveena]

Hi Lynn,

Col1           Col2            Col3   Col4
20-Jan   Mr. Swart    Available   Debit (last Month)- 5896
20-Jan   Mr. Swart    Available   Debit (last Month)Paid- 1589630
20-Jan   Mr. Swart    Available   Debit-8956
20-Jan   Mr. Swart    Available   Debit Paid-1258956

Above are the details. So, I need a solution to concatenate Col4 for first two rows as a single column and last two rows together separately.

Desired Output is
20-Jan   Mr. Swart    Available   Debit (last Month)- 5896 , Debit (last Month)Paid- 1589630
20-Jan   Mr. Swart    Available   Debit-8956 , Debit Paid-1258956

[hespora]

I have an (admittedly, ugly) idea for a solution in mind. Tell me, are these four rows you see the maximum number of rows for these values of (Col1, Col2, Col3)?

[Praveena]

Yes,

[hespora]

Okay, I realized during building this that I should have asked for always *EXACTLY* 4 rows per (Col1, Col2, Col3), not for maximum. Anyways, for that one, I have a solution (also see query setup in the attachment):

- In your original query, add a data item "[count]" with a definition of

Code Select Expand

running-count([Col4] for [Col1],[Col2],[Col3])

- Now add 4 referencing queries (I have no clue if that is the formal term, check attachment for what I mean) referencing your original query, with filters on [count]=1, [count]=2, and so forth. pass through Col1 through Col4

- Join referencing queries 1+2, and 3+4. Inner join on Col1, Col2, Col3. In these, you do the concatenation "[concat]" on [refQuery1].[Col4] || [refQuery2].[Col4]. Also, in these, I've added a data item "[row control]" which is set to a static value of 2 in the join of 1+2, and a static value of 4 in the join of 3+4

- add 2 referencing queries referencing the joins. you do nothing in these except pass through data items Col1 through Col3, concat and row control. (/edit: i am not quite certain why these are necessary, however, if I omit these on my system, cognos throws a java null pointer exception at me)

- add a union and put in the last 2 referencing queries. Pass through all 5 data items.

If in your result, you do not need lines 1 and 3 from your original data, you're done here. If you do, then

- finally join up the union back to your original query with an outer join on Col1, Col2, Col3, and [union].[row control] = [original query].[count]