Database linking to show field values not in another field

[diegoC11]

I need some help with this report:

Background:

There are electronic documents we have in our company that list each training a person receives. I've created a report for this already, it shows the different people trained on a particular subject and it's grouped by that subject. Good. But I also need to show all the people that haven't been trained on a subject and display that information in a separate report.

Example: If Person A, Person B and Person C work at the company and we want to show who's learned English and Spanish, the first report would look like this:

English
Person A
Person B

Spanish
Person B
Person C

and the second Report would look like this, showing the people not in the first one:

English
Person C

Spanish
Person A

For the first report, I am using a view that brings in all the subjects and people submitted in these electronic documents and I group them within the report. I also have a table with a field of Employees at the company, let's call it {table.employees} if that is important or can be linked in some way.

Can somebody point me in the right direction with this second report? Not sure if this is a database linking that can fix this as the real important part is to show nontrained personnel underneath the appropriate groupings.

Thanks in advance,
Diego

 

[lbass]

Can you show the query that is being used to generate the first report?

-LB

 

[Madawc]

What's the data structure? if each person / subject combination has its own record, do something like

if {subject} = "Spanish" then 1
else 0

Group by person, and do summary total of @CountSpanish. Suppress the detail lines and group footer. Use Report > Selection Formulas > Group to suppress the group header if @CountSpanish is greater than 0.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

Save your first report under another name. Suppress the detail section and place the following formula in the detail section:

whileprintingrecords;
shared stringvar array x;
numbervar i;
numbervar j := distinctcount({table.employee},{table.course});
if not({table.employee} in x) then (
i := i + 1;
if i <= j then (
redim preserve x[j];
x := {table.employee}
);

In the course group header, add this formula and suppress it:

whileprintingrecords;
shared stringvar array x := "";
numbervar i := 0;
numbervar j := 0;

Then add a subreport in the course group footer that uses the table of employees, with that field in the detail section. Do not link the subreport. In the section expert of the subreport, add this formula:

whileprintingrecords;
shared stringvar array x;
local numbervar i;
local numbervar j := ubound(x);
booleanvar k := false;
for i := 1 to j do(
if {table.employee} = x then
k := true
);
k

Suppress all other sections except the detail section in the subreport.

-LB

 

[diegoC11]

Wow LB, thanks so much for that solution. It worked like a charm! Madawc, thanks for your help as well.

Now to look up how it works. Thanks again, Cheers!