Display data where one field is blank (null?)

[2CsDavid]

Report created with fields:
{Job No.}, {Job description}, {Hours quantity}

If there is no data in {Hours quantity} the other 2 fields are not listed but I want to see them on the report with the {Hours quantity} giving a zero (i.e. 0.00) answer.

Have tried using the Display Null options but do not seem to work - I am obviously doing something wrong.

 

[kchaudhry]

Try creating a formula like this and replacing it with the {Hours quantity} field in the report.

@hours
if isnull({Hours quantity}) then
0 else {Hours quantity}

This should get you your desired resluts.

Kchaudhry

 

[Turkbear]

Hi,
Unless you have combined the 3 fields into one or joined multiple tables using the Job_No field, a blank ( or Null, and they may not be the same thing, depending on your database ) should not prevent the other 2 from showing -

If the data is in multiple files be sure to use a Left-Outer join between them..






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

 

[synapsevampire]

If you don't ahve data, then you don't ahve data, Crystal won't magically make data for you.

If you need help, try posting technical information:

Crystal version
Databse/connectivity used
Example data
Expected output

There may be a way to fake this, but you need to use real technical terms and describe your environment.

-k

 

[2CsDavid]

Nope, none of that has worked.
I am running Crystal v7
Picked up 2 access database tables (call them T1 and T2)
T1 has field 'Job No.', T2 has field 'Hours'. The 2 tables are joined properly.

I want to see a list of all job numbers even if no timecard type data (hours) has been entered in the access database i.e. list all jobs with hours worked - and if no hours worked display '0'

I currently only get all jobs listed where time has been entered.

 

[dgillz]

When you say the 2 tables are joined properly, what makes you say this? Exactly how are they joined? Equal join? Left outer? On what field? What type of connectivity are you using, native or ODBC?

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

http://www.gainfocus.biz

 

[2CsDavid]

ODBC - both tables have Field 'JobID' and have lineds these by left clicking mouse on 'JobID' in Table 1, dragging over to 'JobID' in Table 2. This places and arrow between the two. To date its the only way I have ever known to link tables.

However, I note that at the foot of the links tab where I am performing this, there is an 'options' button which the brings up a window within which there are SQL join types of 'equal', 'left outer' etc etc

Is it these I need to be working with?

 

[dgillz]

Are ALL job IDs in table 1? Or are ALL job IDs in Table 2?

Your from table should have ALL job IDs, even if no time has ever been recorded against it. Otherwise your link is indeed wrong.

Try reversing the links and see what happens.

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

http://www.gainfocus.biz

 

[2CsDavid]

JobID is in both tables and used as the link.
But the Job No. (as distinct from JobID) is the field I am reporting on in Table 1, The hours field is in Table 2