Solved

Selective calculation

Posted on 2012-03-20
3
265 Views
Last Modified: 2012-08-13
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
Comment
Question by:Reader999
3 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37743242
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
 
LVL 19

Accepted Solution

by:
GJParker earned 500 total points
ID: 37743263
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
 

Author Closing Comment

by:Reader999
ID: 37743479
Thanks!  Works!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question