Count with conditional

[snyderbr]

Hello,

I have a database with performance data in it.  I am trying to count the number of rows where a certain critical field is greater than zero, as well as the total number of rows.  I feel if I can get the conditional count working, I will be able to figure out the full number.  I have the following, but it is not working as of yet.



count ([field]  where [field] > 0 for [PRIM KEY 1] and [PRIM KEY 2] and [PRIM KEY 3])

Obviously I am getting an error, but I don't know the fix.

Thank you in advance for the help.

[TheFrenchGuy]

Hello

why don't you try something like :

Code Select Expand

count(filter([PRIM KEY 1],>0) and filter([PRIM KEY 2],>0) and filter([PRIM KEY 3],>0))

not sure about the "and" imbrication ...

[cognos810]

Hello snyderbr,
Are you looking for a report studio expression? If yes, and the field in the question is [Field123] for example, your expression should be
CASE
WHEN [Field123] >0 THEN 1
ELSE 0
END
Finally, set the aggregation property of the data item with the above expression in it to "Total". That will give you a count of records/rows which you want.

-Cognos810