Sum of Conditional Formula

[hbarbs]

I am using Crystal 8.5 along with data from Blackbaud's Raiser's Edge. I am very new to Crystal and just realized that when I try to sum my conditional formula's it says they cannot be created. I need to get a sum/grand total on each of the following conditional formulas to put in the report footer. All three formulas are columns in my group header

(1) Sum ({CnGf_1.CnGf_1_Amount}, {CnAttrCat_1.CnAttrCat_1_Description}) / 365 * DateDiff("d",{?Start Date} ,{?End Date})

(2) Sum ({CnGf_2.CnGf_2_Amount}, {CnAttrCat_1.CnAttrCat_1_Description})

(3) if isnull({CnGf_2.CnGf_2_Amount}) then {CnGf_1.CnGf_1_Amount} else Sum ({CnGf_1.CnGf_1_Amount}, {CnAttrCat_1.CnAttrCat_1_Description})- Sum ({CnGf_2.CnGf_2_Amount}, {CnAttrCat_1.CnAttrCat_1_Description})

Any suggestions on how to create a sum/grand total for each of those would be greatly appreciated. I am stumped.

 

[lbass]

You can use variables, as in:

//{@accum} to be placed in the group header and then suppressed:
whileprintingrecords;
numbervar sumformula1 := sumformula1 + {@formula1};
numbervar sumformula2 := sumformula2 + {@formula2};
numbervar sumformula3 := sumformula3 + {@formula3};

Then create three formulas for the report footer:

//{@displayformula1}:
whileprintingrecords;
numbervar sumformula1;

//{@displayformula2}:
whileprintingrecords;
numbervar sumformula2;

//{@displayformula3}:
whileprintingrecords;
numbervar sumformula3;

-LB

 

[hbarbs]

Thanks lbass for the reply!!! I feel like I definitely am doing something wrong. I put the following formula in the group header and suppressed.

//{@accum} numbervar sumformula1 := sumformula1 + {@Commitment to Date};numbervar sumformula2 := sumformula2 + Sum ({CnGf_2.CnGf_2_Amount}, {CnAttrCat_1.CnAttrCat_1_Description}); numbervar sumformula3 := sumformula3 + {@Variance};

Then I created the following three formulas:
//{@displayformula1}:whileprintingrecords;numbervar sumformula1

//{@displayformula2}:whileprintingrecords;numbervar sumformula2

//{@displayformula3}:whileprintingrecords;numbervar sumformula3

I am getting no results...just blank space? I am wondering if I put the top formula you gave me in correctly?

 

[lbass]

//{@accum}:
whileprintingrecords;
numbervar sumformula1 := sumformula1 + {@Commitment to Date};
numbervar sumformula2 := sumformula2 + Sum ({CnGf_2.CnGf_2_Amount}, {CnAttrCat_1.CnAttrCat_1_Description});
numbervar sumformula3 := sumformula3 + {@Variance};

Add the "whileprintingrecords" to the first formula. If you literally placed the display formulas as shown in your post, they won't show up because you've left in the formula names which were commented out and then you didn't add any returns, so the formula was commented out, too. Make the formulas read like this:

whileprintingrecords;
numbervar sumformula1

It's a good idea to put the returns in anyway for ease of reading.

-LB

 

[hbarbs]

You are really going to kill me now...so sorry. I pasted the top formula in exactly as is and am getting the "A Number is Required Here" error when I check it? The cursor moves between the = and sumformula1.

 

[lbass]

If your amounts are currencies, change "numbervar" to "currencyvar" in all formulas.

-LB

 

[hbarbs]

You are a life saver!!! The first two formulas worked...the third formula is not totaling. It is pulling something - it looks like it is only summing the portion of the formula after "else"

if isnull({CnGf_2.CnGf_2_Amount}) then {CnGf_1.CnGf_1_Amount} else Sum ({CnGf_1.CnGf_1_Amount}, {CnAttrCat_1.CnAttrCat_1_Description})- Sum ({CnGf_2.CnGf_2_Amount}, {CnAttrCat_1.CnAttrCat_1_Description})

Any thoughts on this?

Thank you again for all your help...it is much appreciated!

 

[lbass]

I'm guessing this formula is not returning the correct value at the group level then either. What is this formula supposed to do? As written, if the CHGf2amt was null in the last record of the group, it would pick up the last value of cngf1amt in the group, otherwise it would do the calculation. What do you intend here?

-LB

 

[hbarbs]

Hi. I actually ended up rethinking and altering the export out of Raiser's Edge and I ended up with the correct info. The formula works great on the other two conditional formulas which is wonderful. Thanks again for all your help!!!