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
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