# 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
###### Who is Participating?

x
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.

Commented:
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.
Commented:
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

Experts Exchange Solution brought to you by