# Crystal Reports - Multipe Operators

Hi Experts,

This time I'm trying to perform an If Then statement with multiple operators.  I think my logic is fine except for the syntax.

Basically in the details sections, I have the donation type and the date of the donation.

I would like Crystal to show how many consecutive years someone has donated starting with the year the report is being ran. 2008 works out fine and then I'll have to tweak this report for 2009.

For example:
J Smith has donated in 20080411, 20070311, and 20060303
R Smith has donated 20080711, and 20070421

I was thinking that I could put the following in the details section:

If {donation_date} = 20080101 to 20081231 and 20070101 to 20081231 and 20060101 to 20061231
then "3 Year Donor"
else
If {donation_date} = 20080101 to 20081231 and 20070101 to 20081231
then "2 Year Donor"

* Most likely I'll do this for about 15 years

I haven't figured out this portion out yet...

What ever the output is, have it print out 3 Year Donor, or 2 Year Donor under GF2.

Thanks again for any assistance.
###### 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:
Or you could put a group in for client and then a group for year. Then just use a formula to count the number of years in the year group.

For example

Shared NumberVar YearGroupCounter;

YearGroupCounter := YearGroupCounter + 1

Then another formula to display the counter in the client footer

Shared NumberVar YearGroupCounter;

YearGroupCounter

Then one last formula in the client footer (AFTER you display it -- make another footer)  to zero the variable:

Shared NumberVar YearGroupCounter;

YearGroupCounter : = 1

1. You never have to physically name the years. The group counter will always track the years for you. So you will never have to update this report as years pass.
2. You aren't limited in the number of years you can check. They could have 2,4,5, 15 or eve 20 years. This approach will catch them all.

Another way to do it (if you have db access is to write a view that counts the years.

For example:

Select
<clientname field>,
count(datepart(YYYY, <datefield>) [YearsContributing]
from
<yourtable>
group by
<clientname field>

Then just report on client name field.

Hope that helps,
Dave

0
Commented:
Sorry, that last formula should read:

Shared NumberVar YearGroupCounter;

YearGroupCounter : = 0 (not 1)
0
Commented:
What data are you working with?

Do you have separate records for each year for a person?

mlmcc
0
Author Commented:
Hi Dave and mlmcc,

It's the same report as I was working on before.  I am now trying to take the data from the details section (date IE: 20080101) and determine how many consecutive years someone has donated since 2008.

My details sections looks like the following:
GF1 Donor ID
GF2 Donation Type
Details: Donor ID Donation Type Date of Donation
GF1
GF2 Donation Type, Donation Type, Donor ID, and hopefully how many consecutive years they donated.

All fields except for GF2 are suppressed.

-byd2k
0
Author Commented:
GS2 Correction:
Donation Type, Donation Type Sum,Donor ID, etc..
0
Commented:
It seems you have a separate record for each year.

Are looking to see how many years in a row the person made the same donation type? or just that he made a donation?

mlmcc
0
Author Commented:
It would be how many years in a row irregardless of donation type.  The criteria would start from 2008 to 198x.

-byd2k
0
Author Commented:
That would be how many years in a row he donated something regardless of donation type. (As long as he donated something.)

-byd2k
0
Commented:
The problem I see is getting the data into the correct order.  With the DONATTION TYPE group you will see
Type 1  2008
2006
2002

Type 2   2005

Type 3   2007
2006

Obviously we want to see 4.  It can be done but it will require some code.

I'll see what I can come up with.

mlmcc
0
Author Commented:
I was able to perform this in another method, however it's ugly.  I have two fields, one to determine if there was a donation, and the other used as a formula to state whether or not it was consecutive.

This is an example of the one formula:
GF 1 -Donor ID
GF1 - I have an entry for 2008 Donation, 2007 Donation, 2006 Donation, etc...
Goal is to show how many consecutive years someone donated.

if NthLargest (1, {@2008 Donation}, {Table Record}) = "2008"
and
NthLargest (1, {@2007 Donation}, {Table Record}) = "2007"
and
NthLargest (1, {@2006 Donation}, {Table Record}) = "2006"
then "3 Year" else "No Go"

The problem is, I have to rely on Excel to sort out the dates.  Not what I want, however it works so that I see:

2008, 2007, No Go, No Go, etc...

The other formula:
@2008 Donation ='s
if {donation_date} in 20080101 to 20081231 then "2008" else "0"
@2007...
@2006...

-byd2k
0
Commented:
Do you want me to pursue another solution?

mlmcc
0
Author Commented:
I'm fine with my solution, unless you know of a more efficient way.  I feel like I was able to resolve it, however it just didn't seem right. :)

Thanks again for your assistance and thanks to as well Dave.

0
Commented:
I'll give it a try

Is your data kind of like

Name   DonationType  Year Amount

mlcc
0
Commented:
First of all, when you posted the report layout, you didn't label the sections properly.  You had:

GF1
GF2
Details
GF1
GF2

That should be:

GH1
GH2
Details
GF2
GF1

Not trying to be picky.  Having the sections mislabeled can only lead to confusion.

Now, as for your problem.  One way to do this is using an array.  Each element in the array is a flag for a different year (1 is for 2008, 2 is for 2007, etc.).  If you get a date in that year, you set that element in the array.  At the end, you count how many consecutive flags (elements) are set to see how many consecutive years there were.

Put the following formula in GH1 to initialize the array with each new donor ID.

// init_years
Shared NumberVar Array years;

// I'm starting the array with 20 elements.  You can change that if you like.
Redim years [20];

// Only output an empty string
""

The "" at the end is so that the formula doesn't actually produce any output on the report.

Put the following formula in the details section:

// update_years
Shared NumberVar Array years;
Local NumberVar diff;

//  Put the difference between the years in a variable, just so that we
// don't have to keep doing the calculation over and over.
//  I use the current year as the baseline.  You could replace that with
// something else if you like (2008, the year from a parameter, etc.).

diff := Year (CurrentDate) - Year ({SALES_CREDIT_NOTES.CREDIT_DATE}) + 1;

// If the difference exceeds the size of the array, increase it, up to a
// point.  I put a cap on the size (currently 50).  If the number of years
// gets that high (maybe because the date field is invalid), it's just
// ignored.  You can change or remove that cap if you want, but I
// think some kind of limit is a good idea, just in case.

if diff < 50 and diff > UBound (years) then
(
Redim Preserve years [ diff ];
// I put an empty string here because without it, CR complains that there
// is no else for the if statement
""
);

// If the date field has a year greater than the current year (maybe the
// year was mistyped), the difference would be negative, which would
// give us a subscript error, so ignore those entries.

if diff > 0 and diff < 50 then
years [ diff ] := 1;

// Only output an empty string
""

Put the following formula in the GF1 section (donor ID footer):

//output_years
Shared NumberVar Array years;
Local NumberVar i;

i := 1;

while i <= UBound (years) and years [ i ] = 1
do
i := i + 1;

if i - 1 > 0 then
CStr (i - 1, "#") + " Year Donor"
else
"No Donation This Year"

I've tested those formulas here and they seem to work.

Oh, and FWIW, the formula in your original message:

If {donation_date} = 20080101 to 20081231 and 20070101 to 20071231 and
20060101 to 20061231 then "3 Year Donor"

could never work, because it's trying to compare a single value ({donation_date}) against three different date ranges.  It could never be in all three at the same time.  Conceptually, you want to know if the dates for a donor, as a group, include dates in all three ranges, but the test would be seeing one value at a time.

James
0
Commented:
Obviously in the second formula you'd replace {SALES_CREDIT_NOTES.CREDIT_DATE} with donation date, or whatever your date field is.  I forgot to mention that.

James
0
Commented:
PAQed with points refunded (250)

Computer101