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

Count a particular field with few conditions.

Started by hemantsoni, 01 Jun 2012 08:20:17 AM

Previous topic - Next topic

hemantsoni

Hi Gurus,
I have a list report which contains Clientcode, Certno, Policy no,Prodcode,Status etc.
However I want to count the status where status ='T' for every change of ProdCode.

Can anyone Pls tell me how to achieve this?

Thanks a lot in advance!!!

Lynn

You can try to create a query item with an expression that is either T or null depending on the status and set the aggregation to count. Nulls are not counted. Or you could use 1 and 0 and set the aggregation to sum which might achieve the same result.


case [Status] when 'T' then [Status] else null end


or you can try an explicit count function


count(case [Status] when 'T' then [Status] else null end for [Clientcode], [Certno], [Policy no], [ProdCode])


blom0344

Quote from: hemantsoni on 01 Jun 2012 08:20:17 AM
..... for every change of ProdCode.

What do you mean by that? Change in the list as displayed going from top to bottom?

hemantsoni


Gurus,
Thanks for your quick response.
status =T is ok.But what about prodcode??
For every change of prodcode means it should show the count of statuses where status ='T' for each prodcode.

In a SQL Clause it should be grouped by prodcode.

wyconian

Hi

I would use Lynn's suggestion of a case statement returning 1 or 0 where the status is T (call this T_COUNT) then group your query on prodcode and do a total(T_COUNT for prodcode).

That should give you a count of items where the status = T for each change in prodcode.

Good luck :-)