Display zero when no value is present

[mdtimo]

I want to be able to show a date when no row in the database is present for that date.

For example.

The data in the database would look as follows for 1/2/02 through 1/5/02 for employee 612
employee date hours worked
612 1/2/02 4
612 1/3/02 7
612 1/5/02 3
but there are no records in the database for 1/4/02 for this employee. How can I show zero for 1/4/02. In the end I am looking to show only where the worked hours is less than 4 but at this point I only show 1/5/02 and not 1/4/02.

Any ideas.

 

[Roadkill150]

Try grouping on date? I don't think crystal will display a field unless there is corresponding information. So even if a 0 was entered into the database, crystal would bring it up, but if there is no value entered, it will come back blank. The only other thing to check is your table linking and possible join types. if you do a left outer between a date table and the hour table, dates should always show even if there isn't corresponding data (as long as that date is stored in the table). Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com

 

[mdtimo]

I am working with just one table in this case. And there is not only no data for this date but no record at all(it is not a null value but not even present in the database). I want to display a date and zero for dates where there are no records at all in a date range determined by a parameter.

 

[dgillz]

The only way to do this, if the data is not present at all, is to use formulas:

If {datefield} = date(2002,1,4) then {hours.worked} else 0

The sum these formulas. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[mdtimo]

Would that not have to be done for every possible date the user might enter in the parameter?

 

[dgillz]

Yes it would, but if you prompt for an ending date, then only go back for say 7 days, you could write formulas for every combination.

//today
If {datefield} = CurrentDate then {hours.worked} else 0

//yesterday
If {datefield} = Dateadd("d",-1,CurrentDate) then {hours.worked} else 0

//day before yesterday
If {datefield} = Dateadd("d",-2,CurrentDate) then {hours.worked} else 0
Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[chelseatech]

We did a series on missing dates a year ago in Crystal Clear. Download the back issues for a fuller explantion.

The secret was to use the Next function in this formula
If {table.date} +1 <> Next({table.date} then 0

Place this in a conditional section and suppress if
{table.date} +1 <> Next({table.date}

If there can be several days missing it's a bit harder but with a for loop in Cr8, it works nicely.


Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[mdtimo]

I am now running into the problem of an employee who has no records at all in the date range, I want to show all the days for that person. I can't subtract from a date when no date is present.

I have added links to the employee table but don't know where to go from there.