How do I convert this SQL statement to an equivalent cognos query expression?

Question

I need to recreate a table of summary caculations. In SQL, I can calculated each number individually using either a count statement or a sum case statement. For example:

select count(*) from 'myTable' where somevalue = 'x' and somevalue = 'y'

I have a table in cognos report studio. I am trying to insert query calculations into the table. My query expressions looks something like

count (if ([source].[table].[member] = 'blah' and [source].[table].[differentmember] = 'foo') then (1) else (0))

No matter how I arrange the if statement in my query expression, I get the same (wrong) number. I think I am going about it wrong. I would really appreciate some help with this problem.

I would also really appreciate it if someone could point me to some good, concise documentation for cognos query syntax. Thus far my attempts to self help have been difficult, because there is an entire array of jargon being used that doesn't yet mean anything to me. Thank you.

Are you sure you want count and not sum? Or alternatively, return null instead of 0 when you don't want to count the row? — Luaan
Luaan is correct. Your IF statement either kicks out a 1 or a 0. The count aggregate will count each row regardless of the contents. The only exception is null values which aren't counted. You can simply change the count to a sum, or you can change the 0 to null. — Johnsonium

user3538411 user3538411 · Accepted Answer · 2015-03-20T16:50:13

Thank you luaan and Johnsonium. The code below works great.

sum (if ([source].[table].[member] = 'blah' and [source].[table].[differentmember] = 'foo') then (1) else (0))

How do I convert this SQL statement to an equivalent cognos query expression?

1 Answers