Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

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
0
Reader999
Asked:
Reader999
1 Solution
 
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
 
Reader999Author Commented:
Thanks!  Works!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now