Selective calculation

I have a report that looks essentialy like below.  It has gifts given by individuals in either 2010 or 2011.  I want to count all 2011 gifts only if that person also gave in 2010.  The Running Total column below is how I'd like the amounts calculated.  I only need a grand total in report footer.  I've tried several different approaches: Running Total, WhilePrinting Records, Subreport, but nothing seems to work.  Any assistance would be greatly appreciated.  Thanks.      

Name      Gift Year      Amount      Running Total
Joe      2010      $50       $0
Joe      2010      $50       $0
Joe      2011      $100       $100
Mary      2010      $200       $100
Bill      2011      $100       $100
Sue      2010      $25       $100
Sue      2011      $75       $175
Sue      2011      $100       $275
TOTAL            $700       $275
readersample.xlsx
Reader999Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
You should create another input source which selects the ids of people who donated in 2010.

So create a query in the database that does this or else use the Add Command option in CR to create a query.  Such a query must be written in the sql dialect for your database but in principle it would be something like..

Select Distinct Name from tablename where GiftYear = 2010  (it would be better to use a unique id if there is one)

You then do a left join from your current table to this new source , based on Name (or preferably , unique ID if there is one).

You can then test the ID,name field from the new query and if it is not null , and the current record year is 2011 you include the value in the total.  A running totals field would seem to be the easiest route for this.
0
GJParkerCommented:
Here's one way using manual running totals

Create a group on Name and create the following 4 calculations to create your running total

//@Reset - place in GH1 section
Shared NumberVar Year2010 := 0;
Shared NumberVar Year2011 := 0

//@Accum - Place in Details section
Shared NumberVar Year2010;
Shared NumberVar Year2011;

If {Sheet1_.Gift Year} = 2010 Then
    Year2010 := Year2010 + CDbl({Sheet1_.Amount})
Else
    Year2011 := Year2011 + CDbl({Sheet1_.Amount})

//@Running Total - Place in GF1 Section
Shared NumberVar Year2010;
Shared NumberVar Year2011;
Shared NumberVar Total;

If Year2010 <> 0 Then
    Total := Total + Year2011;

Total

//@Display Result - Place in Report footer section
Shared NumberVar Total
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Reader999Author Commented:
Thanks!  Works!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.