Running totals not working like I want
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22886
Printed Date: 27 Apr 2024 at 10:52am
Topic: Running totals not working like I want
Posted By: khallock78
Subject: Running totals not working like I want
Date Posted: 01 Sep 2020 at 3:33am
I havent worked in Crystal in years but I have a report that I have been asked to help out on now. The data is for gift donations. Its grouped by Gift ID, so each Gift has all of the non-repeating fields in the Group section (Gift ID, Gift Date, Gift Amount, etc...) and then in the Details section it lists who gave the gift (this can be multiple people). So this one dataset I have has 4 unique gifts, but one of those gifts has 2 "donors".
My problem is in the running totals which is in the Report Footer. Some of the fields there are correct. Like "# of gifts" since it is set to take a Distinct Count of Gift ID, and also the Sum of Gifts is correct. But then the person that wrote the report tried to break it down to count the gifts by Payment Method. So in this dataset 2 of the gifts were paid by Personal Check, and 2 were paid by Business Check. But one of the Business Check gifts has 2 donors, and that field for "# of business checks" is wrong because it is counting each donor as a separate gift. Also it is doubling the amount of that gift when it adds up the Total $$ for Business Checks.
Seems like an easy fix, but I cant seem to find the problem, so here I am asking for help! Thank you!
|
Replies:
Posted By: DBlank
Date Posted: 01 Sep 2020 at 4:25am
|
you will want to set the RT the evaluates on change of a unique gift_id field as a sum. You will need a different RT for each level you want to show the SUM at. this will determine when you reset it. For a group you would reset the value at that group. For the entire report you would reset it never. RTs need to be displayed in details or footers.
|
Posted By: khallock78
Date Posted: 10 Dec 2020 at 5:40am
I am displaying all of the RT in the Report Footer.
Right now I have to overall total Count and total Sum working.
I also have the Running Totals working for each Payment Method. I ended up doing a Distinct Count of the Gift ID (one of my groupings) evaluating on a formula (PaymentMethod="business check" for example) and resetting is set to Never.
I am still having the problem with the sums though. If there is more than 1 "detail" under my Gift ID group, then it ends up counting that gift amount more than once.
I feel like maybe I need to evaluate on a formula (payment method = "Business Check") AND evaluate on when the Gift ID changes. But I dont quite know how to do that.
|
Posted By: DBlank
Date Posted: 11 Dec 2020 at 3:42am
couple of ways. probably the easiest would be to create a formula filed and set the value for your sum to 0 if the paymentmethod is not Business check
//DistinctSumValue
if table.paymentmethod='Business Check' then table.amountfield else 0
then use a RT on that formula field set as a SUM and to evaluate on change of unique giftID field.
|
|