Skip to Content
0

Missing records

Apr 20 at 12:00 AM

95

avatar image
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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

10 Answers

Ian Waterman Apr 20 at 08:08 AM
0

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

Ian

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 21 at 07:50 PM
0

That option is already unchecked

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 22 at 06:15 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Apr 23 at 08:22 AM
0

Hi Luke,

What is the record selection formula you've used?

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

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 23 at 08:14 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 23 at 11:27 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Ian Waterman Apr 24 at 09:41 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 24 at 09:37 PM
0

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`

Share
10 |10000 characters needed characters left characters exceeded
Ian Waterman Apr 25 at 07:27 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 25 at 08:52 AM
0

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.


screenshot.jpg (56.7 kB)
Show 9 Share
10 |10000 characters needed characters left characters exceeded

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

0
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.

0

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


0
Former Member
Ian Waterman

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.

0

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

0
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.

0

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

0
Former Member
Ian Waterman

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

0
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.

0