Only show those who are NOT enrolled...?

[Oglesbay]

Hello all. I am an educator and am looking to create a report to help me figure out who is NOT enrolled in a class. Right now we have the ability to create reports showing whose is currently enrolled in what class and when.

If I have a list (text file) of all the students who should be enrolled, is there a way for Crystal to compare the two and only show who is not enrolled? I don't know this is possible or not and have not tried anything yet but wanted to get your opinion first. Thanks!

Chad

 

[lbass]

What tables/fields are you working with? Would you be running the report separately for each class?

-LB

 

[Oglesbay]

Like I said, I have not created anything and wanted to see if this was even possible at all. If I was to create this, I would use the employee ID as the field to compare to the list and to who is enrolled. As of now I would only create this for one class (there are 300 people that need to enroll.)

 

[lbass]

And tables? Do you have an employee table and a class attendance table? What kind of database are you using? Do you have the option of using a command as your datasource?

-LB

 

[Oglesbay]

Sorry I didn't give all the information...

Yes, there is an Employee, Event and Class table. The Employee table is pretty self explaintory, the Event table basically tells if the student attended or not and the Class table describes all the class information. All of our information is pulled from our Learning Management System (LMS) database.

What do you mean by using an command as your datasource?

 

[Madawc]

If you link from Employee to Event, and do a left-outer link, you'll get everyone. Then suppress everyone where the Event details are NOT null, using an IsNull test on a field that would always have a value if the record was there.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[jmd0252]

The default linking that goes on, in crystal, when you just link files, is pulling where there are records ie "matches",, like Madawc said you have to change the type of linking you are doing. You can do something as a google search for furthur explanations.

 

[Oglesbay]

Of the 300 people that need to enroll, there are 30 different classes for them to choose from and they have the choice to pick the one that fits their time the best. I don't want to prematurely enroll them in a class because then I would have to move them if they decide somthing different. That is why I was wondering if I had some formula that listed all of the employee ID's and then compared and showed the ones that weren't enrolled? It's starting to sound like this might not be possible?

Madawc, I understand what you are saying but I still have to have a list that says who is supposed to be enrolled (which 300 out of the 2000 employees in our system.)

 

[lbass]

If you go to database->your datasource, do you see "Add command" above the list of tables?

-LB

 

[Oglesbay]

Yes, I do see that. How can I use that? Will that allow me to add my own spreadsheet, text file or database?

 

[lbass]

Are you saying you are NOT just using tables from one database?

-LB

 

[Oglesbay]

There are three tables that I would use in this report; People, Event and Class and in this database there are over 3,000 employees. We are rolling out a new application and only 300 of the 3,000 employees need to attend this class. Right now there is no way in our database to list who is SUPPOSED to go to a class, but only show who is enrolled in a class.

I also have a list of the employee ID's of all 300 students that should be in class and I am asking if there is a way I can either write a formula or something else that will include all of the ID's and simply compare who is enrolled and who isn't and only show those that are not enrolled.

I am not familiar with SQL and can only do basic/intermediate formulas so this just seems out of my reach.

 

[lbass]

Is the list you have in Excel or Word or what?

-LB

 

[Oglesbay]

It is currently in Excel but can be made into a Text file easily.

 

[lbass]

Let me test something...are the Employee IDs a number field or a string?

-LB

 

[lbass]

Also, can you open another report that uses your database and go to database->show SQL query and copy what you see and paste it here--so I can see the punctuation used?

-LB

 

[Oglesbay]

The Employee ID is stored as a string and here is the SQL from a different report detailing who is currently enrolled in a specific class:


SELECT
"class"."xstartdate", "class"."xname", "person"."pname_lfm$",
"class"."xstarttime", "event"."xdate", "event"."xstatus",
"class"."xteacher", "class"."xroom", "person"."deptname"

FROM
("pathlore"."pathlore_data"."event" "event" RIGHT OUTER JOIN
"pathlore"."pathlore_data"."class" "class" ON
"event"."xlocator"="class"."xlocator") LEFT OUTER JOIN
"pathlore"."pathlore_data"."person" "person"

ON
"event"."yuniquekey"="person"."yuniquekey"

WHERE
"class"."xname" LIKE 'Admin-Rx%' AND ("class"."xstartdate">={ts
'2009-03-01 00:00:00'} AND "class"."xstartdate"<{ts '2009-03-27
00:00:01'}) AND ("event"."xstatus"='b' OR "event"."xstatus"='e' OR
"event"."xstatus"='f' OR "event"."xstatus"='m')

 

[lbass]

Okay, I have something fancier in mind, but maybe you should first try the following. Link your Excel list to your Employee table with an equal join (and enforce both by right clicking on the link so you can see link options), and then link the Employee table to the Event table with a left outer join FROM Employee TO Event. THen use a left outer join FROM Event TO Class. Do not add any record selection formula.

Insert a group on {Employee.EmployeeID}. Create a parameter {?ClassID} for the Class ID, and then create a formula {@inclass} in the field explorer:

if isnull({ClassID}) or
{Class.ClassID} <> {?ClassID} then
0 else 1

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

sum({@inclass},{Employee.EmployeeID}) = 0

Suppress the detail section.

-LB

 

[Oglesbay]

Ok, so I tried creating a new report to link my Excel file to the Employee table. However I've run into a snag at the beginning. I made sure all of the ID's (which are just numbers) are stored as text. However, when I open try to link the table it says the data is incompatiable because it has changed the ID in the Excel file back to a number where it is a string in the database. Any way around that? Other than that, I understand everything else you are trying to do but I just can't get it to read the Excel data! Argh.

 

[lbass]

If you add a string label as your first row in the Excel sheet, it might then read the column as a string. See if that does it.

-LB