• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

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
0
daskas27
Asked:
daskas27
  • 7
  • 7
  • 5
  • +1
2 Solutions
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 7
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now