If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Group by clause not working in Report Studio

Started by hemantsoni, 25 Jan 2013 04:01:24 AM

Previous topic - Next topic

hemantsoni


Hi,
I have a list with four columns a,b,c and d.

Column a,b are coming from table 1
Column c and d are coming from table 4

I am putting total agg function in column d.
I have also grouped columns a,b and c.
auto group and summarize is set to yes.

SQL should be

select

a,
b,
c,
sum(d)


from

<tables and joining other tables)
group by
a,
b,
c

But when I try to see the native SQL of cognos
somehow it is only getting grouped by on c.

SQL is:

select

c,
sum(d)

from
table4

group by
c


Column names
a and b are not appearing in select clauses.

Although these column a,b and c are grouped and Relationship between table 1 and 4 are defined in Framework manager.

Kindly help as I am new to Cognos.



blom0344

You need to take a step back. With the list simply set aggregate to none for the non-measures and for the measure to total. Normally this is all you need to do to get the SQL you expect!  By the way, the modelling should provide the correct default setting. A measure should be defined with an aggregate set (in the model)