crystal reports-count

Question

Hai Friends

this is my query

SELECT
    COUNT(CASE ISNULL(GAM_STATUS, ' ') 
    WHEN '1' THEN '1' 
   END) + COUNT(CASE ISNULL(GAM_STATUS, ' ') 
    WHEN '2' THEN '2' 
   END) + COUNT(CASE ISNULL(GAM_STATUS, ' ') 
    WHEN '3' THEN '3' 
   END) ACTIVE_REC,
    COUNT(CASE ISNULL(GAM_STATUS, ' ') 
    WHEN '5' THEN '5' 
   END) DELETED,
    COUNT(CASE ISNULL(GAM_STATUS, ' ') 
    WHEN '4' THEN '4' 
   END) SOLD
 FROM  GLAS_ASSET_MASTER_T 
 WHERE  GAM_COMP_CODE  = '1' and gam_dept_code between '01' and '03'

output is

active_rec  deleted  sold
50             20    25

same should come in the crystal reports how can i count the records in the crystal reports according to the conditions.

Answer 1

A common method is something like the following :

Formula name : @Active_Rec
Formula text : If {GLAS_ASSET_MASTER_T.GAM_STATUS} IN [1,2,3] Then 1 Else 0

Formula name : @Deleted_Rec
Formula text : If {GLAS_ASSET_MASTER_T.GAM_STATUS} = 5 Then 1 Else 0

Formula name : @SoldRec
Formula text : If {GLAS_ASSET_MASTER_T.GAM_STATUS} = 4 Then 1 Else 0

Place those formula in the report's Details section, and add Summary Fields for them to your Report Footer.

And of course your record selection formula would be

{GLAS_ASSET_MASTER_T.GAM_COMP_CODE}  = '1' and {GLAS_ASSET_MASTER_T.gam_dept_code} between '01' and '03'