Crystal Reports Return Blank Records

Hello,
      I have a simple Crystal Reports 9 report that has two linked tables. One is an employee table with the other being a labor table. I want to see a specific set of employees labor for a given labor date. The report works fine for this. However, if an employee in this set does not record any labor I still want to see that employee on the report. In the past I have always just used a "left outer join" but it does not seem to be working this time. Any ideas?
      I have included a copy of the report.

Thank you
LVL 1
daskas27Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

daskas27Author Commented:
0
Pawan KumarDatabase ExpertCommented:
Add a where clause in your SQL.

Where labourTable.yourKeyColumn IS NOT NULL.

Open in new window


and it will work fine.
0
daskas27Author Commented:
I don't understand.
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Pawan KumarDatabase ExpertCommented:
can you share your SQL query ? Will change in there.
0
daskas27Author Commented:
SELECT `tcsempl`.`prempl`, `tcslabor`.`lwo`, `tcslabor`.`lrel`, `tcslabor`.`lseq`, `tcslabor`.`lproccd`, `tcslabor`.`lmctr`, `tcslabor`.`ltype`, `tcslabor`.`lhrs`, `tcslabor`.`ldte`
 FROM   `tcslabor` `tcslabor` LEFT OUTER JOIN `tcsempl` `tcsempl` ON `tcslabor`.`lempl`=`tcsempl`.`prempl`
 WHERE  `tcslabor`.`ldte`={d '2017-11-13'} AND (`tcsempl`.`prempl`='1358KEMAZ' OR `tcsempl`.`prempl`='138 JNIGA' OR `tcsempl`.`prempl`='163 BNIGA' OR `tcsempl`.`prempl`='215 LNIIG' OR `tcsempl`.`prempl`='254SYASDA' OR `tcsempl`.`prempl`='320 HYASD' OR `tcsempl`.`prempl`='510 FNIIG' OR `tcsempl`.`prempl`='516 RMATS' OR `tcsempl`.`prempl`='517 ROBOD' OR `tcsempl`.`prempl`='519 JIMA' OR `tcsempl`.`prempl`='701 WFN1' OR `tcsempl`.`prempl`='702 WFN2' OR `tcsempl`.`prempl`='703 WFN3')
 ORDER BY `tcsempl`.`prempl`
0
Pawan KumarDatabase ExpertCommented:
Please try this ..

changed table sequence..

SELECT `tcsempl`.`prempl`, `tcslabor`.`lwo`, `tcslabor`.`lrel`, `tcslabor`.`lseq`, `tcslabor`.`lproccd`, `tcslabor`.`lmctr`, `tcslabor`.`ltype`, `tcslabor`.`lhrs`, `tcslabor`.`ldte`
 FROM   `tcsempl` `tcsempl` LEFT OUTER JOIN `tcslabor` `tcslabor` ON `tcslabor`.`lempl`=`tcsempl`.`prempl`
 WHERE  `tcslabor`.`ldte`={d '2017-11-13'} AND (`tcsempl`.`prempl`='1358KEMAZ' OR `tcsempl`.`prempl`='138 JNIGA'
	 OR `tcsempl`.`prempl`='163 BNIGA' OR `tcsempl`.`prempl`='215 LNIIG' OR `tcsempl`.`prempl`='254SYASDA'
	  OR `tcsempl`.`prempl`='320 HYASD' OR `tcsempl`.`prempl`='510 FNIIG' OR `tcsempl`.`prempl`='516 RMATS'
	   OR `tcsempl`.`prempl`='517 ROBOD' OR `tcsempl`.`prempl`='519 JIMA' OR `tcsempl`.`prempl`='701 WFN1'
	   OR `tcsempl`.`prempl`='702 WFN2' OR `tcsempl`.`prempl`='703 WFN3')
ORDER BY `tcsempl`.`prempl`

Open in new window

0
daskas27Author Commented:
I am not familiar with using SQL queries. In Crystal Reports I have the option to "Show SQL Query" . That is where I got the query from but that is just for reference. I cannot edit or modify it in this window.
0
Raghavendra HullurSoftware DeveloperCommented:
H,
which table will not have labor details? Try reversing the link or a right outer join.
0
Pawan KumarDatabase ExpertCommented:
You can get the option to edit also ..or just copy this and paste it in that window.
0
Pawan KumarDatabase ExpertCommented:
@Raghavendra - i already did that but he is saying the query is not editable.
0
Raghavendra HullurSoftware DeveloperCommented:
He has selected the tables and not using command object. So, he won't be able to include the query or modify the query.
We need to check the join built by Crystal and try reversing it.
0
Pawan KumarDatabase ExpertCommented:
@Author - This table ( `tcsempl` `tcsempl` ) should be on the LEFT hand side.
0
Raghavendra HullurSoftware DeveloperCommented:
Well, again he won't be able to put the table on left or right. The steps to change the link direction are:

Click Database --> Database Expert --> Navigate to Links tab --> Right click on the link built between 2 tables and select Link Options --> Change join type to Right Outer join and check.
0
daskas27Author Commented:
Please see the attached files. I did as suggested but with no results. It is only returning the employees that recorded labor for the specified day.
Untitled.jpg
un.pdf
0
Raghavendra HullurSoftware DeveloperCommented:
Can you try commenting the record selection and try adding the conditions one by one? Also share the details of couple of employee who are not having labor recorded. Are you sure the join condition you used satisfy the requirement you are looking for?
Try also reversing the link from lempl to preempl table
0
daskas27Author Commented:
I have. Upon entry in the details section of any condition from the labor table the report stops returning all employees.
0
Raghavendra HullurSoftware DeveloperCommented:
If the join condition is correct, then I think reversing the link should work. But check the conditions used under Record Selection.
0
mlmccCommented:
The problem is you are filtering from the joined table.  SInce you are filtering based on both tables reversing the join won't solve the issue.

When you filter based on a field in the joined table, Crystal treats the join as INNER since NULL cannot equal anything and tests involving a NULL field fail with indeterminant results.

You probably need to use a command object on this.
Can you build a view in the database?

Try this.

Create a NEW report
For the data source choose USE A COMMAND
Use this as the command
SELECT   tcs.prempl, tcslabor.lwo, tcslabor.lrel, tcslabor.lseq, tcslabor.lproccd, tcslabor.lmctr, tcslabor.ltype, tcslabor.lhrs, tcslabor.ldte
  FROM   tcslabor tcslabor LEFT OUTER JOIN 
               (SELECT tcsempl.prempl FROM tcsempl tcsempl WHERE tcsempl.prempl='1358KEMAZ' OR tcsempl.prempl='138 JNIGA' OR 
                 tcsempl.prempl='163 BNIGA' OR tcsempl.prempl='215 LNIIG' OR tcsempl.prempl='254SYASDA' OR tcsempl.prempl='320 HYASD' OR 
                 tcsempl.prempl='510 FNIIG' OR                  tcsempl.prempl='516 RMATS' OR tcsempl.prempl='517 ROBOD' OR
                tcsempl.prempl='519 JIMA' OR tcsempl.prempl='701 WFN1' OR tcsempl.prempl='702 WFN2' OR tcsempl.prempl='703 WFN3') TSC
WHERE  tcslabor.ldte={d '2017-11-13'}  ORDER BY tcs.prempl 

Open in new window


mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
daskas27Author Commented:
can I get all the employees if I use just the labor table?
0
mlmccCommented:
If they are all there you can.  Assuming the records meet other criteria

mlmcc
0
Pawan KumarDatabase ExpertCommented:
@Author -

This table ( `tcsempl` `tcsempl` ) should present LEFT hand side. So when you will use left join all the records from the left table will come in the output irrespective of the join condition.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.