Display the data horizonatally - repeater, crosstab not serving the purpose

[davidcognos]

Hi,

I have a requirement to display the data horizonatally for one column (Product ID) in the report, but the data corresponding to that product ID should come in the rows and based on the maximum product ID number present in the table. I have tried using a repeater table, crosstab but none of them show me the correct result. The data in the table is as below. Any ideas??

Prod ID  Product_name
1   ABC
1         DEF
2   XYZ
2   JKL
3   MNP 


Required report:

Prod1  Prod2  Prod3
ABC     XYZ    MNP
DEF JKL 

 

Thanks in advance

[navissar]

Hi David,
This is the type of requirement I would normally prefer to handle in the database in as much as possible. Using stored procedures or ETL routines you could loop through the data and pivot it properly.
Provided that you are unable to do that, you can accomplish what you're describing. I created a quick Excel sheet with your data, uploaded it to Cognos and created the following:


How?
I created two queries. Father Query contains just product id. Child Query contains product id and name.
I hen created a list with Child Query as the query, which contains a single column - Product name. I highlighted the entire list, clicked on properties and checked the product id item. then I clicked on the list label, and changed the label to be a report expression, which is 'Product ' + number2string([Child Query].[Product ID]) or something to that effect.

Then I dragged in a repeater table with Father Query. I highlighted the repeater table, removed all padding, set it to go across 100 times and never down (So all in the same row), and checked "Product ID" in the properties option.

Then I dragged the list in the repeater table, set the master detail relationship to break on Product ID - and Bob's your uncle! it's done.

[davidcognos]

Thanks a lot. It is working fine.  :)