Crystal Reports Version 10: Copying the functionality of a Left Join? 1

[TheElephantMan]

Good afternoon, all.

I am brand new to Crystal Reports and thus far seem to be getting along pretty decently with it. I have run into a little quandary, though, with a simple group report.

I have two access tables, one with a list of names and corresponding initials; and another with a list of initials and technical skills that the person has:

Table 1:
Name |Initials
Gabe Logan |GL
Tycho Brahe |TB
Scott Kurtz |SK
Ken Masters |KM
Rick Springfield|RS

Table 2:
Skill | Initials
Java | GL
Java | TB
Java | SK
Access| KM
Access| GL
Oracle| TB

I have a simple report with a single grouping that gives me the list of people who have a specific skill. The problem is that I'd also like the report to show people who do not have any skills. I know that with straight SQL I could use a left join and those who are not in Table 2 would show up as NULL, but I cannot seem to figure out how to move that notion into Crystal's Group functionality.

I certainly don't want anyone to write the report for me, but a gentle prod in the right direction would be very helpful.

Thanks in advance.

TEM

 

[synapsevampire]

Grouping has nothing to do with joining. How you connect to the Access database does matter, but you didn't post that (native or ODBC?).

Try going into the data explorer and selecting the join and selecting options and changing it to a left outer.

You can also go into Access and create a Query to expose the data using a Left Outer, and then use that Query from Crystal (make sure that you select Options in Crystal and turn on Views to see the Queries). This is how I would do it as it buys you a layer of insulation in case the underlying database structure changes, and it keeps the database work on the database.

-k

 

[TheElephantMan]

Thanks for the response, synapse, but that's not quite what I'm trying to achieve. Though I now have the data coming in directly from the left join, the employees who do not have a skill are grouped under a blank label:

(Blank)
RS
Java
GL
TB
SK

I would like that blank label to read "None", without changing the structure of the database.

Thanks in advance, again.

 

[synapsevampire]

OK, gotcha.

Create a formula (Insert->Field Object->Right click formulas and select new) which has something like:

If isnull({table.skill})
or
{table.skill} = "" then
"None"
else
{table.skill}

Group on that formula.

-k