Need help with isnull formula 1

[KkCool]

I think I need an isnull formula, but maybe not. The point of this report is to pinpoint Consultants that have not turned in their timesheets for the week. I created a simple report with Crystal 8.5,ODBC,SQL 2000. I linked the consultant table with the worklog table with a left outer join. Thought that was going to solve my problems and show me all consultants, their hours(even if there aren't any) in between a date range. I wa sthen goign to use the highlight expert to grab all those who did not have hours. Can someone please help? I have been driving myself crazy with this for 3 days.

 

[lbass]

The date range is the problem. Remove it from your record selection formula and insert a group on {consultant.emplID}. Then create a formula {@inrange}:

if isnull({worklog.emplID}) or
not({worklog.date} in {?daterange}) then 0 else 1

Then go to the section expert->details->color tab->background->x+2 and enter:

if sum({@inrange},{consultant.emplID}) = 0 then crYellow else crNoColor

This will highlight each detail line for a consultant with no hours. If you have multiple records per consultant with work hours, you could insert summaries and then use the above conditional color formula on the group header or footer.

Another option would be to display only those consultants with no time sheets, instead of highlighting them, which you could do by using a GROUP selection formula:

sum({@inrange},{consultant.emplID}) = 0

-LB

 

[KkCool]

I seem to be having difficulty with this part {?daterange}. I need it to be in between {?StartDate} and {?EndDate}. I took the parameters off, but I still need to be able to enter different dates at all times. Your thoughts?

 

[lbass]

Try:

if isnull({worklog.emplID}) or
not(
{worklog.date} >= {?startdate} and
{worklog.date} <= {?enddate}
) then
0 else 1

-LB

 

[KkCool]

Now it is actually running without returning any errors. However it is returning and highlighting dates from 2002 and so on. I only want dates between {?StartDate}and{?EndDate} and where {worklog.date} isnull. Your thoughts?

 

[lbass]

You have to include dates outside of the parameter period in order to force consultants with no dates to appear on the report. You can do something like suppress the date field (but not the entire record) if it falls outside of the parameter period. Go to format field->common->suppress->x+2 and enter:

not(
{worklog.date} >= {?startdate} and
{worklog.date} <= {?enddate}
)

Then also format the consultant's name to "Suppress if duplicated".

If you know that all consultants who you expect to see on your list have worked at some point since January 1, 2004, for example, you could use a record selection formula like:

isnull({worklog.emplID}) or
{worklog.date} >= date(2004,1,1)

Then follow my earlier suggestions.

-LB