If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

distinct count function in cognos

Started by bharath_hsbc, 16 Jul 2007 11:58:38 AM

Previous topic - Next topic

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])
No, a proof is a proof. What kind of a proof? It's a proof. A proof is a proof, and when you have a good proof, it's because it's proven.

-Jean Chretien

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.


Quote from: MDXpressor on 20 Jul 2007 06:16:24 PM
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  ;)