Missing records

[Former Member]

Hello all,

I'm currently running a report where i get the totals of fields based on a date range, however it seems to be stopping at a certain point and not loading the data after it.
I've had a look and the data seems to be consistent, the only way i can explain it is that job number 1234 and 1235 have identical data but there are no records displayed after job number 1234, any ideas what could be causing that?

[Former Member]

Sorry Ian i may have confused things there, you are indeed correct but this is not where my problem lies (i must have copied the wrong selection formula) that formula does work while the brackets are in place but still doesn't return all records.

What i have done is re-create a basic report to isolate my problem and i think the attached screenshot will help explain my problem.

[Former Member]

I assume Completed column is represented by field

`jobsheet1`.`Job_JobCompleted

As table jobsheet1 is on a inner join then the filed must occasionally by null instead of 0

Change your select filter to be

WHERE (

`timesheet1`.`Time_Date`<{ts '2018-04-23 08:26:55'}
AND `jobsheet1`.`Job_JobCompleted`=0

)

OR `jobsheet1`.`Job_JobCompleted` is null

OR `jobsheet1`.`Job_JobCompleted`=0

Ian

PS still not sure how your filter is working as the Or condition is over riding the first

[Former Member]

Is there a way to stop it returning null values as it would seem that some of the nulled values are jobs that are complete and shouldn't be in the final report??

The main goal of this report is to display all job costs that are uncompleted up until a certain date. i thought it was working OK until i realized that there are some missing, what your saying makes total sense but i just cannot figure out why its returning null values.

[Former Member]

In select expert just add to top of criteria

`jobsheet1`.`Job_JobCompleted` is not null

resulting SQL should look like

WHERE `jobsheet1`.`Job_JobCompleted` is not null

AND `timesheet1`.`Time_Date`<{ts '2018-04-23 08:26:55'}
AND `jobsheet1`.`Job_JobCompleted`=0

[Former Member]

But what about the results that are returning as null?
I need to find the reason why there are records being returned as null, if i exclude the "nulled" jobs from the report then the report will be incorrect, this cannot happen.

I did try using isnull but if a job is set to complete but the report is returning it as a nulled value then its getting picked up in the report - again this cannot happen.

[Former Member]

YOu need to investigate why database has nulls. That is an application issue and nothing to do with Crystal

If you want to see Nulls as zero then replace Job_JobCompleted field with a formula

@jobCompleted

If isnull({`jobsheet1`.`Job_JobCompleted`}) then 0 else {`jobsheet1`.`Job_JobCompleted`}

Ian

[Former Member]

The database doesn't contain ANY null values, which is leading me to believe it's a crystal report problem.

Within the database itself I can change all JobComplete vaules to 1s or to 0s and I will still see missing records in the crystal report.

[Former Member]

Can you run the query outside of Crystal. That might show where things are going wrong.

Crystal only shows what you tell it too. The problem will lie with your joins or data.

How is report grouped or is it a simple listing.

Check formatting of the jobcompleted field and make sure there is no conditional suppression.

Try building report from scratch adding one table at a time and displaying sample data. then adding filters one by one until problem occurs. That will then tell you what is causing issue.

Ian

[Former Member]

I haven't ran the query outside of CR, i will do that!
Thank you for your time and patience, it is greatly appreciated.

[Former Member]

Ian, you were indeed correct, i ran the sql query outside of CR and i am getting the same problem so its probably not a CR issue.
Again thank you for your assistance, you've been a great help

[Former Member]

I think you problem lies here

WHERE (`timesheet1`.`Time_Date`<{ts '2018-04-23 08:26:55'}
AND `jobsheet1`.`Job_JobCompleted`=0

OR `jobsheet1`.`Job_JobCompleted`=0)

Using OR without brackets can cause problem try

WHERE (`timesheet1`.`Time_Date`<{ts '2018-04-23 08:26:55'}
AND (`jobsheet1`.`Job_JobCompleted`=0 OR `jobsheet1`.`Job_JobCompleted`=0)

)

Ian

[Former Member]

I'm a bit puzzled by this, i'm convinced its a joining issue but the report is displaying some records and not others, what i mean by this is this...
job# hours amount complete

1 2 20 1

2 2 10

3 5 40 0

which is then not being reported when i run the proper report asking only for non complete jobs.

The original SQL is below

SELECT `customer1`.`Cust_Name`, `jobsheet1`.`Job_ID`, `jobsheet1`.`Job_StartDate`, `timesheet1`.`Time_Hours`, `supplierinvoice1`.`SpInv_Amount`, `timesheet1`.`Time_Date`, `supplierinvoice1`.`SpInv_Date`, `jobsheet1`.`Job_JobCompleted`, `timesheet1`.`Time_BillRate`, `jobsheet1`.`Job_Description`
 FROM   {oj ((`DATABASE`.`customer` `customer1` INNER JOIN `DATABASE`.`jobsheet` `jobsheet1` ON `customer1`.`Cust_ID`=`jobsheet1`.`Cust_ID`) LEFT OUTER JOIN `DATABASE`.`supplierinvoice` `supplierinvoice1` ON `jobsheet1`.`Job_ID`=`supplierinvoice1`.`Job_ID`) LEFT OUTER JOIN `downgarage_Software`.`timesheet` `timesheet1` ON (`jobsheet1`.`Eng_ID`=`timesheet1`.`Eng_ID`) AND (`jobsheet1`.`Job_ID`=`timesheet1`.`Job_ID`)}
 WHERE  (`timesheet1`.`Time_Date`<{ts '2018-04-23 08:26:55'} AND `jobsheet1`.`Job_JobCompleted`=0 OR `jobsheet1`.`Job_JobCompleted`=0)
 ORDER BY `jobsheet1`.`Job_ID`

[Former Member]

Can you show SQL

Are you left joining to the table jobsheet1

If yes then your filter/joins could be resulting in a Null for the field Job_JobCompleted

Ian

[Former Member]

UPDATE:

On the new crystal report I created for testing I added the job complete field to display the status for each record, for some reason i have some records that do not have a 1 (complete) or 0 (not complete), this would lead me to believe that its a data issue however when i inspect the database the missing records actually have a 0 in the cell

[Former Member]

OK, so I've tried various selection formulas to rule out why this is happening, my original formula is this

({timesheet1.Time_Date} <={?timDateUJC} and {jobsheet1.Job_JobCompleted} = 0) 
or 
({supplierinvoice1.SpInv_Date} <= {?supDateUJC} and {jobsheet1.Job_JobCompleted} = 0)

This only displays uncompleted jobs and I have grouped the results by the Job number.
I have created a fresh report with some basic fields, no groups and a simple formula - ({timesheet1.Time_Date} <={?timDateUJC} - this works fine on its own and displays the record, when I change the formula to ({timesheet1.Time_Date} <={?timDateUJC} and {jobsheet1.Job_JobCompleted} = 0) the record isn't displayed.
I have tried changing links but nothing seems to be working

[abhilash_kumar]

Hi Luke,

What is the record selection formula you've used?

Do you have a group that is not "Job#"?

-Abhilash

[Former Member]

Ok so i re-created my report and took it back to basics and it's even more puzzling.
i have a job table and a timesheet table, some jobs have timesheets and some do not, i have used an outter join to display the jobs even when a timesheet doesn't exist. Jobs 1234 and 1235 both have a time sheet with the same amount of hours, in the raw data everything is as it should be, when i run the report 1234 displays fine but 1235 does not.
Can anyone think of a reason why one works and the other does not??
Thanks in advance,
Luke

[Former Member]

That option is already unchecked

[Former Member]

Check in Report Options or General options and make sure Select Distinct records is not checked

Ian