Solved

Crystal Reports XI Formula - Else If Statement Ignored

Posted on 2012-03-22
2
904 Views
Last Modified: 2012-06-27
I created a formula to calculate the number of unique records with data points entered into any of 11 fields.  The other criteria is that the data is counted if the date within any of those fields falls in the date range entered per start and end date parameters.  In other words at least 1 of the 11 fields must have a date entered and secondly, fall in the date range entered to qualify for the unique record count.  I also count each of the individual 11 fields based on the date range using Running Totals.

For testing/development purposes, I created formula looking at the first two fields only.

On one row:  Field1 totals to 14 and field2 totals to 17.  My formula is returning 14 and should be returning at least 17 as field 2 met the criteria 17 times.

I have 3 formulas:

Inserted into group header - reset_patient_total
WhilePrintingRecords;
Global NumberVar patient_total :=0;

Inserted into details - unique_patient_total
WhilePrintingRecords;
If (cdate({database.field1}) >= {?Start_Date} AND cdate({database.field1}) <= {?End_Date}) Then
   Global NumberVar patient_total := patient_total + 1
Else If (cdate({database.field2}) >= {?Start_Date} AND cdate({database.field2}) <= {?End_Date}) Then
   Global NumberVar patient_total := patient_total + 1;

In group footer - display_patient_total
WhilePrintingRecords;
Global NumberVar patient_total;
patient_total

A few notes:  I am using cdate as the data is stored that way in the vendor's database.  There are some rows that count field2 a few times in the total, but mostly it is not counted/considered at all.  These two data points may have data in both fields, at times in one but not the other and lastly in neither.  Seems like Else If statement is ignored most of the time.
0
Comment
Question by:Fifer5280
2 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 37755666
Crystal has trouble with fields with NULL values.  Since NULL is the absence of a value it cannot be compared to anything so if your first date field is NULL, Crystal will terminate evaluation of the formula at that point and return some unknown value.

So here is how to handle that

If (NOT (IsNull({database.field1}))  AND (cdate({database.field1}) >= {?Start_Date} AND cdate({database.field1}) <= {?End_Date}) Then
   Global NumberVar patient_total := patient_total + 1
Else If (NOT (IsNull({database.field12})  (cdate({database.field2}) >= {?Start_Date} AND cdate({database.field2}) <= {?End_Date}) Then
   Global NumberVar patient_total := patient_total + 1;


There is another you can deal with it.  If you have lots of fields some versions of Crystal have a dropdown in the formula editor.  The default is EXCEPTION ON NULL, you can change that to DEFALT ON NULL which will provide a default date/value for evaluation.

Crystal also has a global option to change all NULL fields to default.  It is in the FILE --> REPORT OPTIONS.  This works so long as a NULL field doesn't mean something in the report.  In your case I suspect it would be ok.

mlmcc
0
 

Author Closing Comment

by:Fifer5280
ID: 37757896
It worked.  Thank you for the solution and the info regarding Null fields with Crystal.  Much appreciated.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now