Problems with lookup table and values table - finding unmatched record 3

[craigwoods]

Hi

This problem has been driving me potty for some time now! I have an SQL Server 2005 database and am using CR XI.

I need to produce a report that is printed copy of the information held on the database. The report will show all of the valid options that are available to be selected and then tick off the details that have been selected on the database.

To do this i thought i would use a Left Outer Join between the lookup table and the data table, but all that happens is that only the matching data is returned and not the unmatched data.

For example:

Lookup
Strategic ID, Description
1, Safe
2, Healthy
3, Economic
4, Enjoy
5, Positive

Table:
Project ID, Strategic ID
1, 1
1, 2
1, 3
1, 4
2, 2
2, 3


What I get is

1, 1
1, 2
1, 3
1, 4
2, 2
2, 3

But what i want is
1, 1
1, 2
1, 3
1, 4
null, 5
null, 1
2, 2
2, 3
null, 4
null, 5

There are no selection criteria at the moment.

I think i'm missing something major, but i'm not sure what!!

Any help gratefully appreciated.

Craig

 

[dgillz]

Make sure the arrow in your visual linking expert starts at the lookup table and points towards the project table.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[craigwoods]

Hi dgillz

Thanks for the reply. I've made sure that the arrow is pointing toward the project table, checked the "Left Outer" option. I've also had a play with the enforced links. But for some reason it won't show the unmatched entries.

C

 

[lbass]

I can't test this out thoroughly, but try remove any groups you have, and try sorting on a formula like this:

stringvar y := totext({table.Strategic ID},"0");
stringvar x;
if isnull({table.Project ID}) then
(
if {table.Stragegic ID} = 1 then
x := "1" else
x := x
) else
x := totext({Credit.Customer Credit ID},"000");
x + " " + y

Use your regular fields in the body of the report.

The issue is that you have been sorting or grouping on a field that is not there. There is nothing that attaches the "null" to the particular group, so it doesn't appear in relation to a particular group.

-LB

 

[lbass]

Oops. Left a test field in there. should be:

stringvar y := totext({table.Strategic ID},"0");
stringvar x;
if isnull({table.Project ID}) then
(
if {table.Stragegic ID} = 1 then
x := "1" else
x := x
) else
x := totext({table.Project ID},"0");
x + " " + y

-LB

 

[craigwoods]

Thanks for the formula, the output doesn't seem to change.

I'm still a bit confused as to where i'm going wrong, is it because the table containing the project/ objective ID "Table B" contains multiple rows for each project and that the lookup table, "Table A" services many rows in "Table B".

I also don't seem to be getting any null values through at all, so where I would expect the pattern to be:

I've also tried creating a view in SQL Server, but this doesn't improve things, for some reason the null's aren't appearing.

 

[lbass]

Did you change the join to a left outer by clicking on the join and selecting that? Have you removed any groups? Maybe it would help if you copied your SQL query from database->"show SQL query" into the thread.

-LB

 

[craigwoods]

Hi

Definatley no groups, no sorting and no group selections are in place.

I've managed to change the join to left outer, the link type is = and enforced is "not enforced".

Here's the SQL...

 SELECT "projects_strategic_objectives"."project_id", 
"strategic_objectives"."strategic_objective_id", 
"strategic_objectives"."description", 
"projects_strategic_objectives"."strategic_objective_id"
 FROM   "ims_staffs"."dbo"."strategic_objectives" "strategic_objectives" LEFT OUTER JOIN "ims_staffs"."dbo"."projects_strategic_objectives" 
"projects_strategic_objectives" ON 
"strategic_objectives"."strategic_objective_id" = 
"projects_strategic_objectives"."strategic_objective_id"

projects_strategic_objectives stores the project & objective id's
strategic_objectives stores the lookup values (6 of them)

 

[Turkbear]

Hi,
Be sure you are not applying any criteria ( record selection or group selection) to the left-outer joined table - that forces an equi-join.




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Also, go to file->report options and make sure you don't have "convert nulls to default values" checked.

-LB

 

[craigwoods]

hi

defo no record selections or criteria in place.

i've also tried the same links etc... as a view in SQL Server and this doesn't include the blank rows either.

Perhaps its a driver or SQL issue rather than Crystal?

thanks

craig

 

[lbass]

Please show how the data displays if you sort by project ID and then by Strategic ID. Are the nulls appearing at the beginning of the report?

-LB

 

[craigwoods]

the data is showing only the matched data, the nulls aren't being produced at either the top or bottom of the list

cw

 

[lbass]

Craig, Which fields are you displaying in your detail section? Should be:

{projects_strategic_objectives.project_id}
{strategic_objectives.strategic_objective_id}

And the formula I suggested should be:

stringvar y := totext({strategic_objectives.strategic_objective_id},"0");
stringvar x;
if isnull({projects_strategic_objectives.project_id}) then
(
if {strategic_objectives.strategic_objective_id} = 1 then
x := "1" else
x := x
) else
x := totext({projects_strategic_objectives.project_id},"0");
x + " " + y

I think you need to be referencing fields from both tables.

-LB

 

[craigwoods]

Hi

I've tried the formula in the detail section - is that right?

I've also included two fields - the project id and objective id, one from each table in the detail section.

The null's still aren't appearing.

Thanks for your help

cw

 

[lbass]

Yes. Did you also go to file->report options and check for null conversion? I'm not sure it's relevant to this situation, but it's worth a try.

With no groups or sorting, if you place all fields from both tables in the deail sections, you see no nulls anywhere?

Your SQL looks correct to me, and I don't think the left outer join would appear there if your driver somehow wouldn't allow it.

You also might just try recreating this report from scratch.

-LB

 

[craigwoods]

Hi lbass

I've taken the null conversion off, but it makes no difference. All of the fields are on and i've tried just the id's.

I've also tried re-creating the report from scratch several times.

Its just plain weird, that something that should be so easy is so hard to do!!

 

[lbass]

So With no groups or sorting, if you place all fields from both tables in the detail sections, you see no nulls anywhere?

What do you see? Can you show us?

-LB

 

[lbass]

Have you verified the database (database->verify database)? Also, can you confirm that if you add only the strategies table to the report that all five instances of the ID field appear?

-LB

 

[craigwoods]

Hi lbass,

If i place all of the fields from both tables into the report, it lists all of the matching records, the nulls don't appear.

I'm afraid that I can't attach a screenshot/ output as I don't have access to a web server to share the image.

I've tried to verify the database and all seems ok.

If you add just the strategies table, you get the 5 rows.