Report Assistance

[rlporter]

I have two tables:
Employee
Employee_stc

The Employee table holds the names of the employees and the Employee_stc the training records. There are several hundred training classes. Not all employees have had all the classes. I need to run a report based on the following criteria:

ClassName
ClassDate

This will give a list of employees that have not had a named class since a given date. I have been fighting this for two days. Any help would be greatly appreciated.

rlporter

 

[lbass]

Create a left join from the employee table to the employee_stc table, linking {employee.name} to {employee_stc.name}. Do not limit records in your record selection formula unless the fields are from the employee table, so that you don't cancel the left join.

Create a discrete string parameter {?ClassName} and a discrete date parameter {?date}. Create a group on {employee.name} and then create this formula {@classafterdate} for the details section:

if isnull({employee_stc.classname}) or
{employee_stc.classname} <> {?classname}) or
{employee_stc.classdate}) < {?date} then 0 else 1

Then go to report->edit selection formula->GROUP and enter:

sum({classafterdate},{employee.name}) = 0

This will return only those employees who did not have the selected class since the selected date. I wonder about the logic though--are you sure you want to include in your list those employees who had the selected class prior to the selected date period?

-LB

 

[rlporter]

Yes, I need to be able to have a date. Some of the training classes have to be repeated every 2 or 3 years. Thanks for your help, but a minor problem.

In this formula, there are several Paraenthesis that don'seem to be closed. I put a *** next to the ones in question.

if isnull({employee_stc.classname}) or
{employee_stc.classname} <> {?classname}***) or
{employee_stc.classdate}***) < ###{?date} then 0 else 1

Also next to the ###{?date}, I get an error that states:

A string is required here.

Again thanks for your help.

 

[lbass]

Sorry about the sloppiness. The formula should be:

if isnull({employee_stc.classname}) or
{employee_stc.classname} <> {?classname} or
date({employee_stc.classdate}) < {?date} then 0 else 1

This assumes that {employee_stc.classdate} is a string and converts it to a date.

Make sure the parameter is set to date also.

-LB

 

[rlporter]

Under Field Explorer, I created the following:

Parameter Fields
* ClassName
* Date

Formula Fields
* ClassAfterDate

if isnull({Employee_stc.Class}) or
{Employee_stc.Class} <> {?classname} or
date({Employee_stc.STCDate}) < {?date} then 0 else 1

Under Group Selection Formulas I have
Sum ({@ClassAfterDate},{Employee.EmployeeID}) = 0.00

I have created some sample tables with only 20 employees and training records on only a few of them. This way I hope to be able to tell when the report shows the correct information. As of now the report shows all 20 employees, even though some of them have had the listed training. Obviously I am doing something wrong, I just can't figure out what.

Again, thanks for your help. I am just starting with Crystal Reports and am way over my head. BTW, I am using version 9 if that makes a difference.

 

[lbass]

This really should work, so try putting the formula in the details section along with the {Employee.EmployeeID},{Employee_stc.Class}, {Employee_stc.STCDate} so you can see where it isn't working (if it isn't). Are you sure that when an employee had the class it was since the specified date? After the group select, there should be no &quot;1&quot;s in the formula column. If you see a record that you think should have a &quot;1&quot; because it contains the class and the class occurred after the {?date}, then check whether this could be a data entry quirk--is the classname entered in multiple ways so that the parameter isn't recognizing it?

Try reporting back with your results.

-LB