Report Design: Null values

Print Page | Close Window

Null values

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15328
Printed Date: 29 Apr 2024 at 1:16am

Topic: Null values

Posted By: zanoknee
Subject: Null values
Date Posted: 10 Jan 2012 at 7:46am

The report searching for DOCTOR.OTHERNO that is null works if I search dates before 2008 but does not work for dates in 2011 and 2012. My formula is:

isNull ({DOCTOR.OTHERNO})or
{DOCTOR.OTHERNO} like "N*" and
{@Fill Date} = {?Date Range} and
{@Qty} > 0.00 and
{TRANS.DEPT} = {?Select Pharmacy}

I have tried putting this in as a select expert as well as a selection formula however it pulls the incorrect dates if the date field is after 2008.

Any help would be greatly appreciated.

Replies:

Posted By: DBlank
Date Posted: 10 Jan 2012 at 7:57am

not exactly sure what you want to search to be but likely you want parenth around your or portion

(isNull ({DOCTOR.OTHERNO})

or
{DOCTOR.OTHERNO} like "N*")

and
{@Fill Date} = {?Date Range} and
{@Qty} > 0.00 and
{TRANS.DEPT} = {?Select Pharmacy}

Posted By: zanoknee
Date Posted: 10 Jan 2012 at 8:11am

When I put the parenthesis in it returns no results and wipes my data. I am trying to search for doctors who have a null other number or an other number that starts with N

Posted By: DBlank
Date Posted: 10 Jan 2012 at 8:13am

can you explain how you want the filter to work?

Posted By: zanoknee
Date Posted: 10 Jan 2012 at 8:15am

I want the Doctor other number to show if it is null or starts with N for a specified filled on date range for a specific department. I want the medication qty to be greater than 0

Posted By: DBlank
Date Posted: 10 Jan 2012 at 8:28am

for debugging you can remove each condition and rerun the report to see which is the one not acting as expected.

My guess is the date is the problem based on the way it is labaled. It is odd to see a date "range" set to use an = condition.

I think your original criteria would have returned all of the NULL doc rows or any rows where ALL of the AND conditions were met.

isNull ({DOCTOR.OTHERNO}) or
{DOCTOR.OTHERNO} like "N*" and
{@Fill Date} = {?Date Range} and
{@Qty} > 0.00 and
{TRANS.DEPT} = {?Select Pharmacy}

Posted By: zanoknee
Date Posted: 10 Jan 2012 at 8:33am

I agree I have never written a date range with an = before a co worker started this report and gave it to me to finish. I normally write mine off of parameters. Do you have any suggestions on how to change the formula to go off of a begin date and end date parameter?

Posted By: DBlank
Date Posted: 10 Jan 2012 at 8:38am

You will have to look at the ?Date Range param and see how it is set up in the report.

my guess is that your coworker set up a date parameter that has 'allow range values' set to TRUE.

if that is the case try this...

(isNull ({DOCTOR.OTHERNO})

or
{DOCTOR.OTHERNO} like "N*")

and
{@Fill Date} in minimum({?Date Range}) to Maximum({?Date Range})

and
{@Qty} > 0.00 and
{TRANS.DEPT} = {?Select Pharmacy}

Posted By: zanoknee
Date Posted: 10 Jan 2012 at 8:44am

Still no data :(

Posted By: DBlank
Date Posted: 10 Jan 2012 at 8:53am

I recommend debugging it by alterning your statement to run each portion by itself and see if each by itself doe what you expect/want. Run each color line and see which item or items are causing you issue. Then you can spen time on the correct "problem" rather than assuming you understand the problem and that assumption being wrong.

(isNull ({DOCTOR.OTHERNO})

or
{DOCTOR.OTHERNO} like "N*")

//and
{@Fill Date} in minimum({?Date Range}) to Maximum({?Date Range})

//and
{@Qty} > 0.00

//and
{TRANS.DEPT} = {?Select Pharmacy}

Which one(s) did not give you the correct result for that condition?

Posted By: zanoknee
Date Posted: 10 Jan 2012 at 11:35am

It seems like the big issue is the {@Fill Date} in minimum({?Date Range}) to Maximum({?Date Range})

this is the one that wipes the data

Posted By: DBlank
Date Posted: 10 Jan 2012 at 11:37am

what is the FIll Date formula?

What is the set up for the ?Date Range parameter?

Posted By: zanoknee
Date Posted: 10 Jan 2012 at 1:05pm

Fill Date Formula:

NumberVar fldYear; // ^^--Replace me!
NumberVar fldMonth;
NumberVar fldDay;
NumberVar fldDate := {TRANS.DATEFLD};
// Break down the date into logical parts
fldYear := (
If fldDate >999999 Then
Truncate(fldDate / 10000)
Else
1900 + Truncate(fldDate / 10000)
);
fldMonth := Truncate(Remainder(fldDate, 10000) / 100);
fldDay := Remainder(fldDate, 100);
// Make into Date type
If (fldMonth In 1 To 12) And (fldDay In 1 to 31) Then
Date(fldYear, fldMonth, fldDay)
Else
Date(0,0,0)

The report does have a date range parameter

Posted By: DBlank
Date Posted: 11 Jan 2012 at 3:54am

to continue debugging

The formula looks valid but i do not know the data that it is altering. It appears your 'date' is stored as a numeric value.

1. if you place both the Fill Date formula and the trans.datefld on the detail section next to each other does the formula actually give you the date you expect to see from the original field?

2. Create a formula Minimum({?date range}) and place it in the report header to make sure the the value from the param is coming as expected.

3. Create a formula Maximum({?date range}) and place it in the report header to make sure the the value from the param is coming as expected.

4. Create another formual field {@Fill Date} in minimum({?Date Range}) to Maximum({?Date Range}) and place it in the detail section. It should return TRUE and FALSE depedning on if the row is int he date range or not.

Which one of these, if any, are not acting as expected?

Posted By: zanoknee
Date Posted: 11 Jan 2012 at 5:09am

None are returning the expected results

Posted By: DBlank
Date Posted: 11 Jan 2012 at 5:13am

what is some numeric date value sample data?

Posted By: zanoknee
Date Posted: 11 Jan 2012 at 6:30am

01-04-2012 the report is ran daily

Posted By: DBlank
Date Posted: 11 Jan 2012 at 6:40am

Sorry I am confused.

01-04-2012 appears to be either a date field or a text field but not numeric. The Fill Date inicdates it as numeric?

Shouldn't the raw data from the {TRANS.DATEFLD} field look like the below?

19990115

20110401

20101204

If you place the {TRANS.DATEFLD} and the FIll date side by side it should be something like this...

19990115 1/15/1999

20110401 4/1/2011

20101204 12/4/2010

What are you getting?

Posted By: DBlank
Date Posted: 11 Jan 2012 at 6:43am

also whan you say it is run daily, that indicates to me that there may be a predefined date range that it is running for. Is that true and if so what are the rules for that? For example; always this weeks data, or month to dat, or last 24 3 days, etc.

Posted By: zanoknee
Date Posted: 11 Jan 2012 at 7:33am

i am getting date values rturned but they are not the right dates if I set my parameters as 9-24-2011 through 9-26-2011 I am still getting data for other dates such as 12-01-2011. When I say that it runs daily I mean someone goes in and enters the parameters daily.

Posted By: DBlank
Date Posted: 11 Jan 2012 at 7:37am

and what about this portion...?

If you place the {TRANS.DATEFLD} and the FIll date side by side it should be something like this...

19990115 1/15/1999

20110401 4/1/2011

20101204 12/4/2010

What are you getting?

Posted By: DBlank
Date Posted: 11 Jan 2012 at 8:07am

does the select criteria work if you just use

{@Fill Date} in date(2011,9,24) to date(2011,9,26)

Print Page | Close Window