Crystal Report Totals

Keri Eadie

I am trying to evaluate the total given to an appeal and a certain package by year and by donor.  I need the report grouped by their 2018 activity and reponse and then by donor.  Some donors will have multiple gifts in one year.  To try reduce the number of formulas needed I exported an appeal per year (so I have exported 5 different appeals).  Then I wrote formula's for each year to only capture the packages I need for example:

IF {CnApls_4.CnApls_4_Package} = "Splash - Live Auction Donation"

Then {CnApls_4.CnApls_4_Total_Given}

else IF {CnApls_4.CnApls_4_Package} = "Splash - Silent Auction Donation"

Then {CnApls_4.CnApls_4_Total_Given}

else 0


In the details it will duplicate the totals if they have other packages.  If I sum the group for the records that truly have 2 appeals it calculates correctly.  But for the records that duplicated the details it adds each of the duplcated lines.  For example JB has 2 gifts $1 + $5000 = $5001.  HG has 1 gift (that should be included) of $6800 but the details displays it four times so the sum is $27,200.  So I thought I would use running totals.

Feild to summarize (appeal total given - based on my forumla above)

Evaluate on change of field (I selected the Appeal ID KEY)

Reset on group name

JB calculates correctly.  DG calculates incorrectly as $27,200


If I change it to evaluate on change of group (I selected name) JB only calculates $1 but HG calculates correctly.


Help?

Chester Bammel

If I am understanding this correctly, I think you need a separate formula for each donation type.  Then sum each donation type.  Then get the total of each summary for the grand total.

 

Keri Eadie

I have a seperate formula for each appeal looking at the packages and a seperate running total.  For each seperate year the appeal could have say 6 different packages.  I only care about 2 of them.  Most donors only have 1.  JB has both of the packages that I am interested in.  DG has 1 that I am interested in and 3 that I am not.  

Chester Bammel

Do they have donations for the packages that you are not​ interested in?  If they would not and the value is zero, then it would not matter.   5000 + 0 + 0 + 250 + 0

I hope I'm not missing something.  Whatever you do, it must be a formula and total that will be applicable for everyone.  Your individual formulas must be something like this - not grouped together:

Formula Sp_LAD:      IF {CnApls_4.CnApls_4_Package} = "Splash - Live Auction Donation"

Then {CnApls_4.CnApls_4_Total_Given} else 0;


Formula Sp_SAD:      IF {CnApls_4.CnApls_4_Package} = "Splash - Silent Auction Donation"

Then {CnApls_4.CnApls_4_Total_Given} else 0


​Do this for each donation type, then add them all together.  Sorry, if I'm missing what you are trying to do.

Keri Eadie

Chester Bammel:

Do they have donations for the packages that you are not​ interested in?  If they would not and the value is zero, then it would not matter.   5000 + 0 + 0 + 250 + 0

I hope I'm not missing something.  Whatever you do, it must be a formula and total that will be applicable for everyone.  Your individual formulas must be something like this - not grouped together:

Formula Sp_LAD:      IF {CnApls_4.CnApls_4_Package} = "Splash - Live Auction Donation"

Then {CnApls_4.CnApls_4_Total_Given} else 0;


Formula Sp_SAD:      IF {CnApls_4.CnApls_4_Package} = "Splash - Silent Auction Donation"

Then {CnApls_4.CnApls_4_Total_Given} else 0


​Do this for each donation type, then add them all together.  Sorry, if I'm missing what you are trying to do.

the packages I am not interested in do have amounts.  Just curious why the formula can't be grouped together? 

Chester Bammel

Okay, I think it is finally sinking in.  Sorry, sometimes it takes a bit to get your head wrapped around it.  So, the real question I think is why are you getting multiple detail lines for each formula?  Why would one person have 1 line, another have 2 and another 4?  There is something there that is causing the repeats.  There should only be a single detail line with each of the totals on it.

 

Keri Eadie

I can suppress the duplication of the details - but then I have to use running totals otherwise the sum will still calculate the hidden duplicated fields.  I don't understand why the running totals will only calculate correctly if there is only 1 package that I actually want to see for the donor?


I could create a group for each appeal  - but then the formatting would be wrong as I need it to be in 1 row for each donor.  Unless there is a trick to make it display correctly?


Its a complicated report and I appreciate your quick response and trying to wrap your head around it and what my problem is!