Tips and Tricks
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005-2015 : Tips and Tricks
Message Icon Topic: summing only visible fields Post Reply Post New Topic
Author Message
rsj12
Newbie
Newbie
Avatar

Joined: 13 Mar 2012
Location: United States
Online Status: Offline
Posts: 8
Quote rsj12 Replybullet Topic: summing only visible fields
    Posted: 14 Nov 2012 at 7:31am

How do I sum only the visible fields in a column at the end of the report?

My report has only 1 formula field in the Group Footer 1 section called Est Cost

 @Est Cost
if {unifier__weeklyrpt1.CM_GCAMOUNT_CA} > {unifier__weeklyrpt1.CM_POLBESTIMATE_CA}
        then {unifier__weeklyrpt1.CM_GCAMOUNT_CA}
        else {unifier__weeklyrpt1.CM_POLBESTIMATE_CA}
 
this field is conditionally suppressed by using the following formula in the Common tab 'Suppress' option
 
(not(isnull({unifier__weeklyrpt1.CPCO_Status})) and {unifier__weeklyrpt1.CPCO_Status}="Active")
//added below logic per change request from Shani Howard 8/6/2012
OR
(not(isnull({unifier__weeklyrpt1.CPCO_Status})) and {unifier__weeklyrpt1.CPCO_Status}="Incorporated")
//added below logic per change request from Shani Howard 10/23/2012
OR
(not(isnull({unifier__weeklyrpt1.CPCO_Status})) and {unifier__weeklyrpt1.CPCO_Status}="Pending")
OR
(isnull({unifier__weeklyrpt1.CPCO_Auth}) and
 isnull({unifier__weeklyrpt1.CPCO_No}) and
 isnull({unifier__weeklyrpt1.CPCO_Status}) and
 not(isnull({unifier__weeklyrpt1.CO_Auth_Amt})))
 
I want to only sum the non-suppressed 'Est Cost' fields in the report footer.  Right now it is summing all of the occurrences of the 'Est Cost' field.
Thanks a Lot!
rsj
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3666
Quote hilfy Replybullet Posted: 28 Nov 2012 at 4:09am
Create a formula that looks something like this:
 
If
(not(isnull({unifier__weeklyrpt1.CPCO_Status})) and {unifier__weeklyrpt1.CPCO_Status}="Active")
//added below logic per change request from Shani Howard 8/6/2012
OR
(not(isnull({unifier__weeklyrpt1.CPCO_Status})) and {unifier__weeklyrpt1.CPCO_Status}="Incorporated")
//added below logic per change request from Shani Howard 10/23/2012
OR
(not(isnull({unifier__weeklyrpt1.CPCO_Status})) and {unifier__weeklyrpt1.CPCO_Status}="Pending")
OR
(isnull({unifier__weeklyrpt1.CPCO_Auth}) and
isnull({unifier__weeklyrpt1.CPCO_No}) and
isnull({unifier__weeklyrpt1.CPCO_Status}) and
not(isnull({unifier__weeklyrpt1.CO_Auth_Amt}))) then 0
else {@Est Cost}
 
Then sum this formula, which will only have a value if the field is not being suppressed.
 
-Dell
Proviti, Data & Analytics Practice
9-Time SAP BusinessObjects Partner of the Year.
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.