Cognos CrossTab Report

[ShanChatterjee]

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

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 !

 

[krobb2005]

In your query you have two dimensions, AcctID is one Dimension and QuestionData is the other dimension. AnswerData is your fact. Then in the crosstab you assign the dimension AcctID to your row, QuestionData to the column, and then place the AnswerData in the measure. It makes no difference whether the data is text or numbers.

 

[ShanChatterjee]

Hi Krobb2005,

Thanks for the reply, however we tried and it doesnot seem to work in Cognos ReportNet. It works in Access and other sorts of Reports.

Would you try the same example yourself. We get the following Report on execution in ReportNet:


Name Address1
100006 #Error! #Error!
100007 #Error! #Error!

We think all aggregate functions in Cognos Reportnet works only with Numerics...

Thanks a Lot !!!!

 

[griffindm]

Did you try using text-friendly aggregates MIN or MAX?

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[ShanChatterjee]

hi griffindm,

Thanks for the reply, but YES i tried all Grouping functions available, including Min, Max. They seem to work Okay with MS-Access and SQL Server, however dont seem to work in Cognos reportNet. ( they all give error )

I am really in the trouble now. I have to make this Report at the earliest !

Sigh !!!!


Anyone help me please !!!

thanks......

 

[ShanChatterjee]

Any one has a solution ?

Thanks a lot

 

[Draoued]

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.

 

[Draoued]

any feeback on this one ShanChatterjee ?

 

[ShanChatterjee]

Hi Draoued,

Thanks for the reply,However the number of columns in the Final report will vary ( meaning that there will be various entries in the QuestionData Field - which we do not know beforehand) And this QuestionData field will eventually turn into Columns in the final report.

The only way I think is to CrossTab the report, where the "Measure" part will be the AnswerData(see table at the top). However since we would have to use a Aggregate Function in "Measure" column, this is not working in Cognos, because all the Aggregate Functions works only with Numeric data. ( and we have Text Data for the Measure )

If I make the same report in MS-Access, it works perfectly, because the Max,Min functions work with Text Data.

Did I explain you correctly ?