Unexpected results from report - Can't find problem with selection criteria

[bfollowell]

I am having trouble getting a report to return the correct results. I am developing reports against Maximo, a computerized maintenance management system my company uses. I’ve used Access as a front-end reporting db for years but after a recent upgrade to the main maintenance db and some more recent modifications, my front-end db have become more and more unreliable. Many of my colleagues know and readily use Crystal Reports 8 but know nothing whatsoever about Access. My manager wants me to convert all of my reports to Crystal Reports. I didn’t think this was going to be too hard but my very first, very simple report will not return the correct results. Some of it is probably because of the crazy way corporate has setup our db but part of it is something inherent to Crystal as this particular report still runs fine under Access. I’m not really sure what the issue is. Let me explain.

I’m not certain how the database is setup but it is my understanding that the db lies on a server somewhere at corporate and all the different companies spread across various states all use the same db. I can’t see the other companies’ data from within the application itself but I can see their data from the reporting db using an outside reporting tool like Crystal or Access. The particular problem I’m asking about concerns a table called EQUIPMENT. The key field is called EQNUM. The problem is this is no longer a unique field. Several different company’s all have equipment with the same EQNUM. I can easily find hundreds of examples. I knew something was up when corporate started emphasizing of the importance of including the newly added SITEID and ORGID fields in all of our reports. These two new fields appear in every single table in the db. The problem I run into stems from null values being returned for these ID fields. Let me further explain.

I have the WORKORDER table linked to the EQUIPMENT table with a left outer join. Not all workorders are assigned to equipment. Some are assigned to locations and therefore would not have an EQNUM field to link the tables with. I want to see all workorders and be able to include details from the equipment table on those workorders that are attached to equipment and return nulls from those that aren’t, hence the left outer join.

This wasn’t an issue until I had to modify my report to include the SITEID and ORGID fields from both tables to exclude the other companies’ data. Now the issue I run into is that my report won’t include data from a workorder record that isn’t attached to a piece of equipment. I got around this in Access by adding the following line to my selection criteria:
(MAXIMO_EQUIPMENT.SITEID="TMMI" Or MAXIMO_EQUIPMENT.SITEID Is Null) and (MAXIMO_EQUIPMENT.ORGID="TMMI" Or MAXIMO_EQUIPMENT.ORGID Is Null).

This worked fine. Everything worked perfectly. All the expected results were returned.

I converted the report to Crystal but it does not return the same results. Crystal refuses to return any results that do not have equipment attached to the workorder and I cannot understand why. Here is my selection statement from Crystal:

{WORKORDER.SUPERVISOR} like "FAC*1" and
{WORKORDER.TARGSTARTDATE} in Date (2007, 10, 01) to Date (2007, 10, 31) and
Not IsNull ({WORKORDER.PMNUM}) and
({WORKORDER.LOCATION} like "IU*" or UpperCase ({WORKORDER.DESCRIPTION}) like "IU*") and
{WORKORDER.ISTASK} = "N" and
{WORKORDER.ORGID} = "T02ORG" and
{WORKORDER.SITEID} = "TMMI" and
({EQUIPMENT.ORGID}="T02ORG" or IsNull ({EQUIPMENT.ORGID})) and
({EQUIPMENT.SITEID}="TMMI" or IsNull ({EQUIPMENT.SITEID}))

I know this issue isn't within any of the other fields you see but I didn't metnion. The only thing I can think of is something about the IsNull statement on those two fields at the end but I know doing this works fine in Access. Is this something I can't do in Crystal? Does anyone see any glaring, blatant mistakes? Is there some other way to do what I'm trying to do? Did I give enough background information?

Any assistance anyone might be able to provide would be GREATLY appreciated as I am almost to the point of pulling out hair and would prefer to keep what I have. Thanks in advance for any help any of you might be able to provide.

Sincerely,
Byron Followell
Skilled Group Leader - Plant Engineering - Facilities
Toyota Motor Manufacturing, Indiana, Inc.

 

[Mile-O]

Regarding all the usage of the IsNull in your selection criteria, have you changed the way Crystal interprets Null values yet? From Exceptions For Nulls to Default Values For Nulls?