[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 854
  • Last Modified:

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.
0
byd2k
Asked:
byd2k
  • 6
  • 5
  • 2
  • +2
1 Solution
 
dro_lawCommented:
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

This has two advantages over your approach:

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
 
dro_lawCommented:
Sorry, that last formula should read:

Shared NumberVar YearGroupCounter;

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

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

mlmcc
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
byd2kAuthor 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
 
byd2kAuthor Commented:
GS2 Correction:
Donation Type, Donation Type Sum,Donor ID, etc..
0
 
mlmccCommented:
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
 
byd2kAuthor Commented:
It would be how many years in a row irregardless of donation type.  The criteria would start from 2008 to 198x.

-byd2k
0
 
byd2kAuthor 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
 
mlmccCommented:
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
 
byd2kAuthor 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
 
mlmccCommented:
Do you want me to pursue another solution?

mlmcc
0
 
byd2kAuthor 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
 
mlmccCommented:
I'll give it a try

Is your data kind of like

Name   DonationType  Year Amount

mlcc
0
 
James0628Commented:
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
 
James0628Commented:
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
 
Computer101Commented:
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now