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.
Hello
why don't you try something like :
count(filter([PRIM KEY 1],>0) and filter([PRIM KEY 2],>0) and filter([PRIM KEY 3],>0))
not sure about the "and" imbrication ...
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