Solved

Crystal Reports XI Formula - Else If Statement Ignored

Posted on 2012-03-22
2
903 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
Comment Utility
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
Comment Utility
It worked.  Thank you for the solution and the info regarding Null fields with Crystal.  Much appreciated.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need to learn crystal reports 7 38
Truncate vs Delete 63 87
Problem with SqlConnection 5 109
Pull date to Group header 6 12
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 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

8 Experts available now in Live!

Get 1:1 Help Now