Solved

Selective calculation

Posted on 2012-03-20
3
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 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