Exception Report

[jsmith19]

I am trying to build an exception report that shows the pieces of equipment from the master list that have not been coded in the time card table.

Table 1: Equipment master
Table 2: Time Card

They both have an equipment ID on them.

Any help would be appreciated.

Jeff

 

[bdreed35]

Create a blank report.
In the database expert, navigate to your datasorce and add the Equiment Master and Time Card tables.
Click OK and should be brought to the Link tab of the expert.
Clear any of the links that are there already.
Drag the Equipment ID field from the Equipment Master to the Equipment ID field in the Time Card table so that a link is created between them.
Right click on the link (the line between the two) and choose Link Options.
Change the join type form inner to left outer.
Click OK to close that window and OK again to close the Database Expert.
Go to the Report menu and choose Selection Formulas, Record.
In the editor, add the following line:

IsNull({TimeCard.EquipmentID})

Save and Close the editor.
Add the fields to the report as ususal and run it.

~Brian

 

[jsmith19]

bdreed35,

I followed your steps and I am getting records that are on both tables.

I added the equipment ID from the Equipment master table to my report to view.

Any suggestions?

 

[bdreed35]

Can you post the SQL that is being passed to your database?

Database, Show SQL Query

~Brian

 

[jsmith19]

SELECT
EC_EQUIPMENT_MASTER."Equipment_Code", EC_EQUIPMENT_MASTER."Location",
PR_PRE_TIME_CARD."Equipment_Code"
FROM
{ oj "EC_EQUIPMENT_MASTER" EC_EQUIPMENT_MASTER LEFT OUTER JOIN "PR_PRE_TIME_CARD" PR_PRE_TIME_CARD ON
EC_EQUIPMENT_MASTER."Equipment_Code" = PR_PRE_TIME_CARD."Equipment_Code"}
WHERE
PR_PRE_TIME_CARD."Equipment_Code" IS NULL AND
EC_EQUIPMENT_MASTER."Location" = '0401'
ORDER BY
PR_PRE_TIME_CARD."Equipment_Code" ASC

 

[bdreed35]

This probably won't change anything but change the sort order to use Equipment_Code from the EC_EQUIPMENT_MASTER table rather than the PR_PRE_TIME_CARD table.

If you add PR_PRE_TIME_CARD.Equipment_Code to the details section, do you see both blanks and valid codes?

~Brian

 

[jsmith19]

I took off the sort and no change.

I added the equipment ID from the Time card and they are all blank.

Jeff

 

[bdreed35]

If they are all blank than that would imply that the left outer join is working properly. I wouldn't think that you would have NULL values in the Equipment Code field in the Time Card table but is you do, that could explain why yo are getting more records than you expect to get.

What version of CR and what datasbase are you using? We can approach this differently if you have CR or 10, or if you have the ability to create a view or stored procedure on your databse.

~Brian

 

[jsmith19]

One thing I have found out, is on my time card I have payroll records and equipment records. On my report I am getting the payroll records with the null value. I have fixed this my using another condition that removes the payroll records.

I am going to play around with it some more. I think I understand the logic now.

Thanks for all of your help.

If I get stuck again, I will post another question.

Jeff

 

[jsmith19]

brian,

I have made my report very basic to solve my problem. I have no conditions. It seems like my left outer join is not working. I get all of my equipment IDs from the eqiupment master table but none from the time card. I have not yet put the null is a condition so I could debug the report. I should have 931 records from the master table and 193 equal records from the time card.

Any sugestions?

SELECT
EC_EQUIPMENT_MASTER."Equipment_Code",
PR_PRE_TIME_CARD."Equipment_Code"
FROM
{ oj "EC_EQUIPMENT_MASTER" EC_EQUIPMENT_MASTER LEFT OUTER JOIN "PR_PRE_TIME_CARD" PR_PRE_TIME_CARD ON
EC_EQUIPMENT_MASTER."Equipment_Code" = PR_PRE_TIME_CARD."Equipment_Code"}

Jeff