row counts on multiple columns

[TripJ]

Apologies in advance, total newb to Cognos Analytics.  Moving to CA from 7.5 and just got a trial installed.  Data Analyst is trying to do a simple report that, in addition to other summary columns, they are trying to do counts of Yes / No values on a column.  i.e. how many rows have YES how many have NO.  We've gotten through creating the report using the data source but how exactly to manipulate the report for this is a struggle.  What is the proper/best approach?  Is there good documentation on CA reporting/dashboards available?  Any suggestion or assistance is appreciated.

[AnalyticsWithJay]

Rows with 'Yes':

Code Select Expand


total
(
     CASE WHEN [YES_NO_COLUMN] = 'Yes'
       THEN 1
       ELSE 0
     END
)


The total() function has an optional 'for' statement, which allows you to aggregate against different column(s) if necessary.

[TripJ]

Thanks for the suggestion, we'll dig into that!

[TripJ]

The 'Total' above is doing exactly what is needed, except...  The rows totaled need to be unique on a column.  Any assistance is greatly appreciated!

total of 'Y' in column yes_no for unique values in column WHOSIT

It seems like the following had a chance of doing this but no...

for distinct [WHOSIT]
total
(
     CASE WHEN [YES_NO_COLUMN] = 'Yes'
       THEN 1
       ELSE 0
     END
)

[DragonLady]

count(distinct  [whosit] for [yes_no_column] , [moredistinction])