Solved

# Crystal Reports - Multipe Operators

Posted on 2008-11-10
844 Views
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.
0
Question by:byd2k

LVL 3

Expert Comment

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

LVL 3

Expert Comment

Sorry, that last formula should read:

Shared NumberVar YearGroupCounter;

YearGroupCounter : = 0 (not 1)
0

LVL 100

Expert Comment

What data are you working with?

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

mlmcc
0

Author Comment

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 Comment

GS2 Correction:
Donation Type, Donation Type Sum,Donor ID, etc..
0

LVL 100

Expert Comment

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 Comment

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

-byd2k
0

Author Comment

That would be how many years in a row he donated something regardless of donation type. (As long as he donated something.)

-byd2k
0

LVL 100

Expert Comment

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 Comment

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

LVL 100

Expert Comment

Do you want me to pursue another solution?

mlmcc
0

Author Comment

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

LVL 100

Expert Comment

I'll give it a try

Is your data kind of like

Name   DonationType  Year Amount

mlcc
0

LVL 34

Expert Comment

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

LVL 34

Expert Comment

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

LVL 1

Accepted Solution

PAQed with points refunded (250)

Computer101
0

## Featured Post

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…