HFTHdfries
MIS
I am creating a report showing all the donors who donated last year, excluding those who have donated this year. How could I design a formula to display and count these donors?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Exclude based on Date Range
- Thread starter HFTHdfries
- Start date
- Status
basil3legs
Programmer
Is the data in 2 seperate tables for the 2 years? If so, and assuming the table for this year onlt contains this years donors, and you can set up an index on the donors then you can link th 2 tables by this and only put data from last years table in it. You would then format the sections and suppress it with a statement such as
not(isnull({thisyearstable.donor}))
This will then only show the donors who donated last year and not this.
If this doesn't help, let us know how the data is stored. If the donors are in this years table even if they haven't donated, you would do the suppress when their donated value is 0
i.e. something like
thisyearstable.value = 0
If all the data is in one table with one row per table, this is easy and I'm sure you wouldn't be asking (or adapt the above)!
not(isnull({thisyearstable.donor}))
This will then only show the donors who donated last year and not this.
If this doesn't help, let us know how the data is stored. If the donors are in this years table even if they haven't donated, you would do the suppress when their donated value is 0
i.e. something like
thisyearstable.value = 0
If all the data is in one table with one row per table, this is easy and I'm sure you wouldn't be asking (or adapt the above)!
synapsevampire
Programmer
This should be done on the database side using SQL, though you cn get there within CR.
Try something like this in the Record Selection criteria (use Report->Edit Selection Formula->Record:
Not knowing if you have a datetime field or a date field, I'll express it using datetime, and you can then change it to a DATE() function and omit time if appropriate.
{MyDonors.DonationDate} >= DateTime ("1/1/2001 12:00:01am"
Now group by the Donor and suppress the Group Header and Detail bands by right clicking them and selecting Suppress.
Right click the Group Footer on the left and select Format Section
Click the X-2 next to the Suppress and place something like this:
maximum({MyDonors.DonationDate}) >= datetime(("1/1/2002 12:00:00am")
Place all of the fields you wish to display in the Group Footer
This will be inefficient as Crystal will pull in all of the records for 01/02, and then filter out the 02's. Unfortunately Crystal doesn't support nested queries, so this is the best solution that comes to mind.
-k kai@informeddatadecisions.com
Try something like this in the Record Selection criteria (use Report->Edit Selection Formula->Record:
Not knowing if you have a datetime field or a date field, I'll express it using datetime, and you can then change it to a DATE() function and omit time if appropriate.
{MyDonors.DonationDate} >= DateTime ("1/1/2001 12:00:01am"
Now group by the Donor and suppress the Group Header and Detail bands by right clicking them and selecting Suppress.
Right click the Group Footer on the left and select Format Section
Click the X-2 next to the Suppress and place something like this:
maximum({MyDonors.DonationDate}) >= datetime(("1/1/2002 12:00:00am"
)Place all of the fields you wish to display in the Group Footer
This will be inefficient as Crystal will pull in all of the records for 01/02, and then filter out the 02's. Unfortunately Crystal doesn't support nested queries, so this is the best solution that comes to mind.
-k kai@informeddatadecisions.com
- Thread starter
- #4
HFTHdfries
MIS
There is not two separate tables for the two years, but the suggestion from synapsevampire worked. Thanks!
- Status
Similar threads
- Locked
- Question
- Locked
- Question