Record Selection and Null Values

[martintd]

Apologies for the length of the post, but I don't know how else to describe the problem.

I am using CR8.5

I am writing reports to extract data from a learning management system with the following structure
Table 1 Employee table
Emp_PK
Emp_LName
etc

Table 2 Class Roster table
Emp_PK
Evt_PK
RegistrationDate
etc

Table 3 Class Table
Evt_PK
Evt_Name
etc

Is there a way that I can filter the employee table and the Class table, but still show all returns from the employee table that meet the employee filter, even if there is not entry in the Class roster table for the filtered Class

The LMS has about 9000 employee records and about 5000 class records at present, so I would like to be able to filter employees down to an organisation (which is a different table link to Employee table) and show the status of training for all those employees against a chosen class. Status of people not in the Class Roster table would be "Not Registered" for example.

Thanks

Tony Martin
tony.martin@comalco.riotinto.com.au

 

[lbass]

Hi Tony,

I would try the following:
First create the table links as follows:

Employee Table -> Organization Table //equal join
Employee Table -> Class Roster Table -> Class Table //both should be left joins

Since you mention filtering, you might want to create a parameter based on organization: {Organization.Name} = {?Organization}

To deal with nulls in the class roster table, create a formula {@registration date} and place in details section with {Employee.EmpPK} and {Employee.Name}:
if isnull({classroster.registrationdate}) then Date(1899,09,09) else {classroster.registrationdate}

Use conditional field suppression on {@registration date}:
{@registration date} = Date(1899,09,09)

Create a second formula {@registered}:
if {@registration date} = Date(1899,09,09) then "Not Registered" else ""

Create a details b section and place {@registered} below the {@registration date} field. In the section editor, for details a, choose "underlay following section."

There's probably a smarter, simpler way of entering "Not registered" maybe using totext or some other conversion operator, but I can't remember what works with dates, and am not where I can test this right now...

Ordinarily, placing any kind of selection on a table that is linked by a left join from another table will make the left join act like an equal join. Since the above deals with nulls in the Class Roster Table, you could try creating a class parameter: {ClassTable.Evt_Name} = {?Class Name}

Otherwise you could eliminate any selects on that table and just group by {ClassTable.Evt_Name}

-LB

 

[lbass]

Hi again,

You should be able to use the following for {@registration date}:

if isnull({classroster.registrationdate}) then "Not Registered" else totext({classroster.registrationdate},"MM/dd/yyyy&quot

You can then skip the conditional suppression and second formula I suggested earlier and which was unnecessarily complicated...

-LB

 

[martintd]

Thanks lbass, but I really need to have some way of restricting the classes also, as there are over 5000 of them.

Any further ideas, maybe using subreports or something would be great.

My LMS uses a custom application for people to read the crystal reports, and this allows them to filter on all fields from any table included in the report. Hence people will try using the class table for filtering, and my report will bomb.

Any further help would be appreciated.

Regards

Tony

 

[lbass]

Hi Tony,

I tried this and it works, and I think it's what you're looking for--it allows the return of all employees at a selected location and provides the registration status for each for a selected class:
1-Create the links as I described earlier.

2-Create two parameters:
{?Organization} //based on the organization table linked to your employee table and//
{?Evt_PK} //if this is the class ID number

(Or you could instead use {?Evt_Name} and substitute the appropriate fields from your Class table in the following formula.)

3-Create the following formula {@registration date}:
if (isnull({ClassRoster.Evt_PK}) or {ClassRoster.Evt_PK} <> {?Evt_PK}) then &quot;Not Registered&quot; else
totext({ClassRoster.RegistrationDate},&quot;MM/dd/yyyy&quot

4-Add detail fields from Employee Table (Emp_PK, Name, etc.) and {@registration date}

5-Add select statement: {Organization.NameorID} = {?Organization}

Please respond if you have any problems with this or just to let me know that it worked! Thanks.

-LB

 

[martintd]

Thanks LB,

This is really close, but the following results are being returned.

Employee has 99 records in Class Roster table.
Report returns 99 records, all saying not registered (as my selected class in the parameter was not one of the ones they have done.)

How can I display a record that says not registered for the class I chose in the parameter?

Thanks

Tony

 

[lbass]

Hi Tony,

By &quot;Employee has 99 records in Class Roster table,&quot; I'm assuming you mean you now have a list of 99 employees for a specific organization and that none of them are enrolled in the specific class you chose, and now you want the report to identify the class referred to.

You can either place the parameter field {?Evt_PK} (or {?Evt_Name} if you decided to use that as your parameter) directly into the page header or into the detail section.

Is that what you were looking for?

-LB

 

[lbass]

Tony,

After an exchange with Ken Hamady in another thread, I think my last approach above will not give you exactly what you're looking for. If an employee participates in multiple courses, you would get results as follows for a specific course, e.g., if you select ClassA = {?Class}, then:

Employee1 Not registered //but registered in ClassB
Employee1 Not registered //but registered in ClassC
Employee1 3/18/03 //registered in ClassA
Employee2 Not registered //null-no Class registration
Employee3 3/19/03 //only registered in ClassA

A solution is to:

1-Group on Emp_PK, and create a formula {@classvalue}:

if (isnull({ClassRoster.Evt_PK}) or {ClassRoster.Evt_PK} <> {?Evt_PK}) then 0 else 1

2-Place this in the detail section and then suppress the detail section.

3-Create a second formula @registration and place in group header:

if sum({@coursevalue},{Emp_PK}) = 0 then &quot;Not Registered&quot; else &quot;Registered&quot;

4-Create a third formula {@date}, place in group header, and also sort on {@date} descending:

if (isnull({ClassRoster.Evt_PK}) or {ClassRoster.Evt_PK} <> {?Evt_PK}) then Date(1899, 09, 09) else
{ClassRoster.RegistrationDate}

5-Conditionally suppress {@date} with formula:

@date = Date(1899, 09, 09)

Your results should then look like:

Employee1 Registered 3/18/03
Employee2 Not Registered
Employee3 Registered 3/19/03

I'm not where I can test this right now, but wanted to attempt to correct my earlier post right away. Sorry for the insufficient advice earlier.

-LB