on 10-20-2008 11:05 PM
I have to pull a report that show those who are currently working so we are assuming those with no End Dates are still working. When I put '0' or '0/0/00' for the End Date I get an error stating I need to put in a date.
How do I only include those records that have Null End Dates? Ultimately I need a count of those currently employed. So I'm assuming once my report only shows those records I can add a running total field.
I have Crystal XI.
Thanks
Try entering some default value for null like enter a default date "1/1/1900" which means that the end date is null.
Now in the record selection write the condition like this
if {?EndDateParameter}=date(1900,1,1) then
(isnull(datefield)=true or totext(datefield)="")
else
datefield={?EndDateParameter}
Regards,
Raghavendra
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
In the formula design studio, in the top right, there is a dropdown, by default it says 'exceptions for nulls'. That needs to be changed to 'default values for nulls'. Then, isnull(<date field>) Should make sense. Generally, I try to avoid the global conversion of DB nulls to default values, and do it only in specific formula evaluation.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ryan
I finally found the formula designer and I made the change as stated.
I then used the isNull (date_field) and in trying to figure out where to place this formula field I tried it in the Details section and Header and it just says 'False' in both places. I'm a newbie at crystal so excuse my not so smart moves.
I was hoping this would show me only one record (the latest record) which would be the one with the Null End Date.
Thanks
In the formula for your select statement, you need to ensure that 'default values for nulls' is selected, and then you can use the isnull(<yourdate>) to include records with null dates explicitly. That is, assuming it's stored as a date field in the database.
If not, have a look in the CR help files for default values for nulls, and determine which evaluation you should use to explicitly include them.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello
When you say "you need to ensure that 'default values for nulls' is selected" are you talking about selected in the File > Report Options?
If so, Under Report Options I have selected: "Convert DB NULL Values to Default and Convert other Null Values to Default"
This is a subreport, does that matter?
Also, I did try the isNull(date_field) and I go 0 records displayed.
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.