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.
Rows with 'Yes':
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.
Thanks for the suggestion, we'll dig into that!
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
)
count(distinct [whosit] for [yes_no_column] , [moredistinction])