List report to show one row of data per products

[apatel]

Hi
Can this be achieved  in cognos 8.4 ? if so how?

I am trying to create a report to show one row of data per credit note with products.

This is what i am achieving
Date          Credit Note No     Product No
01/05/11    1234                     789
01/05/11    1234                     826
02/05/11    4584                     562
03/05/11     5696                    586 
03/05/11     5696                    741
03/05/11     5696                    124

What I would like the report to look like is
 
Date          Credit Note No     Product No   Product No  Product No
01/05/11    1234                     789              826
02/05/11     4584                    562
03/05/11    5696                     586             741              124

Any help would be appreciated.
Thanks   

[Mpotla]

Hi,

I have tried and got the solution to this but not very efficient solution. Create two queries in the report.

Query 1 with Date and Credit note no.
Query 2 Date,Credit note no , calculated date item with the expression rank (product no for date,credit note no). Which would assign 1...124,2...586,3...741.

Now lets add 5 data items assuming i will have max 5 product no at any given case
Data1:
Max(Case
when  rank (product no for date,credit note no)=1 then
(Product No)
Else
(0)
End)

Data2:
Max(Case
when  rank (product no for date,credit note no)=2 then
(Product No)
Else
(0)
End)
... repeat the similar calculations for other three data items.

now join the Query1 with Query 2 with Date and Credit note no.

My join query will have query items.

Date, Credit note no from Query 1 and 5 Calculated data items from Query 2.

If ur create report see the output

Date            Credit Note no    Prod1  Prod2 Prod3 Prod4 Prod5
01/05/11    1234                     789     826    0            0      0
02/05/11    4584                     562     0         0           0      0
03/05/11     5696                    586     741     124       0      0

By using Data format property u can remove zeroes in the output.

Hope this would help.

Regards,
Potla