COGNOiSe.com - The IBM Cognos Community

I want to create a CrossTab report in Cognos ReportNet, however my data is Text instead of numbers as:

Table#1



AcctIDÃ,  Ã,  Ã, QuestionDataÃ,  Ã,  Ã, AnswerData
------Ã,  Ã,  Ã,  Ã,  ------------Ã,  Ã,  Ã,  Ã,  Ã,  Ã, ----------
100006Ã,  Ã,  Ã, NameÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Keith
100006Ã,  Ã,  Ã, Address1Ã,  Ã,  Ã,  Ã,  Ã,  51 West Road
100007Ã,  Ã,  Ã, NameÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ray
100007Ã,  Ã,  Ã, Address1Ã,  Ã,  Ã,  Ã,  Ã,  21 Grey Street

I want CrossTab report like this from the above Table#1:

Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, NameÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, Address1
100006Ã,  Ã,  Ã, KeithÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  51 West Road
100007Ã,  Ã,  Ã, RayÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  21 Grey Street


When I use any of the Aggregate functions as Max or Min ( which works with SQL Server ), I get the following output

Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  NameÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, Address1
100006Ã,  Ã,  Ã, #!ErrorÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  #!Error
100007Ã,  Ã,  Ã, #!ErrorÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  #!Error


Is there any way of generating the report as above ? Most of the examples in Cognos ReportNet Help says about Numerical Data, which is not the case here...

THANKS A LOT 8)

I would create an Alias table ,and the following SQL.

select distinct T1.ID, T1.DESC as name , T2.DESC as address
from `test23.tab` T1, `test23.tab` T2
where T1.ID = T2.ID
Ã,  and T2.TYPE = 'Address1'
Ã,  and T1.TYPE = 'Name'
order by 1


Then the result is:

ID	Name	Address
100006	Keith	51 West Road
100007	Ray	21 Grey Street

It's not exactly a cross-tab, but the output is what you are looking for.

1 create a query
2 create a tab. model (named 'all')
3 within that tab. model you place two tab. models (named 'name' and 'address')
4 in the left (of the two) add: acctid, questiondata (rename to 'questionname') and answerdata (rename to 'answername')
5 in the left (of the two) add a filter questiondata=name
6 in the right (of the two) add: acctid, questiondata (rename to 'questionaddress') and answerdata (rename to 'answeraddress')
7 in the right (of the two) add a filter questiondata=address1
8 in the first tab. model ('all') get the acctid, questionname and answername from the tab. model 'name' and add questionaddress and answeraddress from the tab. model 'address'
9 add a filter to 'all' by dragging the acctid from step 8 to the filtersection and equal it to the acctid fom address. it should look like this [acctid]=[address].[acctid]
10 in the cube items add acctid as a dimension and the other fields as measures.
11 create a list and assign it to your query.
12 Drag from query items the item 'acctid' to the list and after that, place the two other fields (answername & answeraddress) from the query items on the list.
14 now edit the listheaders to see the desired header names (for acctid you can click the text-property and leave it blank and press ok.

Good luck!