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.
Create the expression in a detail filter, and Cognos will put that expression in the where clause in the generated SQL.
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.
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.
Think that's working. Values are looking like I would expect it to look.
Thanks a lot!