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

Replicating Count(*) in Cognos

Started by Arsenal, 01 Oct 2012 05:59:44 PM

Previous topic - Next topic

Arsenal

Hey All,

supposing in sql server you had a simple sql snippet:

count(*) Number
,columnfromtable,columnfromtable2
from table1 join table blah blah
where orderdate>reportdate

It's the bolded part which stumps me ..i can do a count queryitem1 for queryitem2 in a report but don't know how to translate the bolded part above in Cognos "lingo"

Help will be much appreciated.

tjohnson3050

Create the expression in a detail filter, and Cognos will put that expression in the where clause in the generated SQL.

Arsenal

Thanks.
That's what I had tried earlier along with a count queryitem1 for query item2 but I don't think I was getting the correct data. I think I either need some sort of a neutral count (not count queryitem1 for queryitem2) along with the detail filter or figure out how to achieve the count within a data item.

tjohnson3050

You don't need to use the count function in a data item unless you are setting the aggregation level for the count at a different level than the whole query.  Just drag over the data item you want to count, then set the aggregation property to either count or count distinct depending on which kind of count you want to achive.

Arsenal

Think that's working. Values are looking like I would expect it to look.

Thanks a lot!