asked on
Crystal Reports Database Links
I have a report that I am using to verify if all the employees in the first Table have been setup in the Second table. I've added both table and set links which then remove access to data that is not in both files. I need to be able to do a comparasion that something like this: Name from Table1 and Account# from Table2
Name Account#
Smith 1231123
Jones 9827468
Ward
This result would represent the Name and Account# are not in Table2 yet.
Can someone help me with this?
Thanks in advance.
Name Account#
Smith 1231123
Jones 9827468
Ward
This result would represent the Name and Account# are not in Table2 yet.
Can someone help me with this?
Thanks in advance.
Crystal Reports
Last Comment
James0628
Can you please post database query you are using and expected result?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Response to James0628:
I want to see the employees in TableA (the one that has all employees), but I want it to show which ones are not in TableB by leaving the Account Number column for TableB blank.
I want to see the employees in TableA (the one that has all employees), but I want it to show which ones are not in TableB by leaving the Account Number column for TableB blank.
As stated you must left join the tables but you can't do any filtering on table b. If you filter on table b Crystal converts the join to an inner join.
You can do this with conditional suppression.
Suppress the details with
Not (IsNull({AccountField})
mlmcc
You can do this with conditional suppression.
Suppress the details with
Not (IsNull({AccountField})
mlmcc
ASKER
Where does this expression go, in the Selection Formula?
ASKER
I was able to get this Left Outer Join done and it works. Its actually an issue that I have had before, but never thought there was anything that could be done about it. Thanks
mlmcc,
By "filtering on table b" do you mean an IsNull test in the record selection, like the one I suggested?
I actually tried that and it works fine for me. "Show SQL query" still shows a LEFT OUTER JOIN, and I get the correct records (ie. only that ones that are _not_ in tableB).
James
By "filtering on table b" do you mean an IsNull test in the record selection, like the one I suggested?
I actually tried that and it works fine for me. "Show SQL query" still shows a LEFT OUTER JOIN, and I get the correct records (ie. only that ones that are _not_ in tableB).
James
nursecore,
Yeah, if you want to see all of the employees, then the main thing is to use a LEFT OUTER JOIN.
One other thing I will mention. Any fields that come from tableB will be null when a matching record is not found. If you're trying to use any of those fields in a formula, that can be a problem. CR does not handle null fields well in formulas. If that's an issue, you need to use IsNull first to check the field, before you do anything else with that field. Another option is to use the "Convert Database NULL Values to Default" option under File > "Report Options". If you use that, then in most formulas you won't need to check for nulls.
James
Yeah, if you want to see all of the employees, then the main thing is to use a LEFT OUTER JOIN.
One other thing I will mention. Any fields that come from tableB will be null when a matching record is not found. If you're trying to use any of those fields in a formula, that can be a problem. CR does not handle null fields well in formulas. If that's an issue, you need to use IsNull first to check the field, before you do anything else with that field. Another option is to use the "Convert Database NULL Values to Default" option under File > "Report Options". If you use that, then in most formulas you won't need to check for nulls.
James
An IsNull test may get around the issue since it can find those. I was more refering to a test like {Field1} = 89
SInce NULL cannot equal 89 Crystal automatically changes the join to inner to limit the record testing.
mlmcc
SInce NULL cannot equal 89 Crystal automatically changes the join to inner to limit the record testing.
mlmcc
I was thinking that it might be something like that. IAC, it didn't really matter in this case, since he actually wanted to see all of the records.
James
James
ASKER
I you'll notice above I have already gotten this don't with an Left Outer Join and accepted a solution above. Thanks anyway.
I know. We were just continuing our own little discussion. Sorry. Didn't mean to confuse you.
James
James