distinct count function in cognos

[bharath_hsbc]

hi, i wish to know the distinct count function in cognos. I use count([subjects]) to count number of subjects. But if I want to count distinct subjects, what is the function i use ? Any help is appreciated. Thanks in Advance.

Regards,
Bharath

[MDXpressor]

distinct count can be applied in 2 ways.

You can set the aggregation property of the data item to Count Distinct.  It should be understood thatd that you would replace the data item with the count using this method.  For instance, if you had a data item called Sales Rep Name and it had 22 distinct occurrences the data delivered would be 22, and not the Sales Rep Name you may be expecting.

Second is to script a data item:
Count(distinct [Data Item] for [Summary Key])

[ms_anna]

MDXPressor,

I have a similar question but it is regarding the accomodation of about 800K categories in a unique calculated column (as a dimension), knowing the limitations of max categories, is there a way i can have all these 800K categories counted through category count measure in transformer. Please give some detailed steps.

distinct count can be applied in 2 ways.

You can set the aggregation property of the data item to Count Distinct.  It should be understood thatd that you would replace the data item with the count using this method.  For instance, if you had a data item called Sales Rep Name and it had 22 distinct occurrences the data delivered would be 22, and not the Sales Rep Name you may be expecting.

Second is to script a data item:
Count(distinct [Data Item] for [Summary Key])

[arunavagupta]

hi,

i have a field called Status that has two values 'FAILURE' and 'SUCCESS'
now that i want to count the number of records with FAILURE
how to go about it?
have only one field in the package called Status
Actually need to calculate FAILURE %
that is number of failure/total status *100%
Can any one help me wth ths?

[TheCognosDave]

Just do it in a SQL statement..

SELECT Status, Count(*)
FROM <YourTableNameHere>
GROUP BY Status

This will return a rowset listing all the possible values of 'Status' (i.e. 'FAILURE' and 'SUCCESS' ), with their respective occurrence count.

Failure % would then be something like this..

([Failure Count] / ([Failure Count]+[Success Count]))*100

GL