help please - How to do record count based on a grouping of a field. 1

[ejaycognos]

I have a union of 3 queries each has a record id and field X.
NOw , I need to do record count grouped by X.
In report studio , we can easily get record count = count(distinct record id) but need to group by X.

I am using cognos 8.2 report studio , crosstab report with union of 3 queries.

I think do I need to write a sql for it and link to the union query??if so please let me know how to do it ?

Thanks

 

[blom0344]

count(distinct [ID] for [GROUP X]), but I would expect that this does not work for 3 unions
However, if X is the only non-aggregate, then it will automatically return distinct count of the ID based on X

Ties Blom

 

[ejaycognos]

Thanks blom for the input , the above formula worked perfect when I tried with single query in a crosstab report.

However , when I used in 3 queries and unioned them , it throws error as "error;sqlOpenResult status=-28"

UDA-SQL-0107 A general exception has occured during the operation "fetch".ORA-01722:invalid number ORA-02063;preceding line from SMPRODSBYLINK .."

Here is what I did:
In requirement,I need to group 2 rows and calculate 3 measures , initially I created a grouped list and pivoted to a crosstab report. Then , I pulled in the other 2 measures in the report.Now, as I am converting to crosstab , the measure will not appear in o/p until I change the aggregate function to "Total". (I researched it from the cognos.com document)

So I changed manually the measure from 'Automatic' to 'Total' for all 3 measures. I think this may be a cause for this error???

Is there anyway to create a grouped list report and fake its layout to be as a crosstab?? or any other solution? or is it a limitation in crosstab for multiple measure in unioning 3 queries?


Thanks for the help.


I working on this for past few days but unable to find a solution.

 

[blom0344]

Cognos crosstabs can only work with dataitems that have set an aggregate. Period!
I have created crosstabs based on union with no problems.
However, the measure from a unioned query is stored as a single dataitem, so if you want to display them side by side, then use a second dataitem 'type' that stores a fixed value for each individual query.
Use this dataitem as a column node and you are done..



Ties Blom