Identifying empty fields

[ChrisH1]

Hi

I apolgise in advance if Im not presenting this logically.

I have a report with the following data fields :

SL.Account and Anal_Desc.

eg.

SL Account Code Anal Desc.
010063 Local Govt.

Im pretty sure that there are SL Accounts without a decsription. How would I find these ?

Both field s are from different tables but are linked via left outers. I thought tthe report would therefore show all SL accounts, even those with no descriptions.

Thanks


Im using Crystal version 8

 

[IdoMillet]

Show your Record selection formula. You are probably applying condition to an outer-side field. Change that condition to:
IsNull() or (your condition).

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[ChrisH1]

Record selection as follows

{SLANLHDR.ANALYSIS_HEADER} = "CLIENT TYPE" and
{SLACCTS.COMPANY} = "A"

The report has the following table linkage

SLAACCTS - SLAANL - SLANLCDE - SLANLHDR

{Acct.code} - {Acc.code}
{Anal.code} - {Anal.code}
{Anal.no} - {Anal.no}

Is the problem that {SLANLHDR.ANALYSIS_HEADER} = "CLIENT TYPE" is the last linked table and the decription I want is in the SLANLCDE table (the one before) ?

 

[IanWaterman]

As Ido says change first lineof your selection statement to

(Isnull({SLANLHDR.ANALYSIS_HEADER}) or {SLANLHDR.ANALYSIS_HEADER} = "CLIENT TYPE") and

The brackets are important

Ian

 

[ChrisH1]

Thanks

(Isnull({SLANLHDR.ANALYSIS_HEADER})) did the job in the Select Expert

The only problem now is the Account code data field is now empty.

eg
SL Account Code
010063

 

[IanWaterman]

YOu will need to be more specific. I do not understand the new problem.

Ian

 

[synapsevampire]

Yeah, that's an optimistic approach to resolving LO join issues in Crystal.

Once you place criteria on the child table, you've overridden the LO join with the condition specified, they are conflicting requirements.

When specifying a LO join, you request where field = field or the child table field is null, then you add criteria which states that the child must be a set value, so the database logically decides that you're on crack because it can't be both NULL and have a value.

This is an age old misunderstanding of how a LO join works, and Ido is correct in that you can resolve this using a ISNULL check in the SQL, but a Crystal record selection isn't SQL.

In CR8 you are limited in how you can address this, you can might create a database object to address this.

Since you didn't bother to post your database type, and given your understanding of a LO join, I'll guess that you don't have rights to create database objects and resolve this in SQL within a View or a Stored Procedure. It's true that we might edit the SQL generated by Crystal to do this, but I think it will prove even more problematic to attempt it, it's finicky and you need to understand the SQL of the database very well.

A quick database side solution is to create a View of:

select * from SLANLHDR.ANALYSIS_HEADER where SLANLHDR.ANALYSIS_HEADER = "CLIENT TYPE"

Then left outer join your parent table to that.

So the best solution for you will be either to use conditional suppression for the child table criteria or a subreport for the child table, and link via the field.

I'll demonstrate the first as the second will likely be much slower.

Remove the {SLANLHDR.ANALYSIS_HEADER} = "CLIENT TYPE" from the record selection criteria.

Right click the details and select format section->X2 next to suppress and use:

{SLANLHDR.ANALYSIS_HEADER} <> "CLIENT TYPE"

The problem with this approach is that the non Client Type rows are still in the report, so if you perform aggregates, they will have to be conditional.

An example would be using a Running Totals, and in the evaluate->use a formula you'll nee to specify:

{SLANLHDR.ANALYSIS_HEADER} = "CLIENT TYPE"

-k

 

[ChrisH1]

Many Thanks

The following works fine

Right click the details and select format section->X2 next to suppress and use:

{SLANLHDR.ANALYSIS_HEADER} <> "CLIENT TYPE"

For dabblers like myself the help this forum supplies is invaluable.