Solved

Crystal Reports XI Formula - Else If Statement Ignored

Posted on 2012-03-22
2
906 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Reports Sum only working when drilling down 20 53
Delete from table 6 47
Pagination Difference  in crystal report 7 50
SQL Backup skipping a few tables 7 43
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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