Bad Select Formula 1

[FunkyBunch]

Hello All,

I am creating a report in CR9 with SQL 2005.

The report is to display all vehicles that have had a status change within a time period. Here is the first part of my selection formula:

The third line of the formula uses 2 isblank fields that check if the parameter has been left blank.({?Divison} = "")

The fifth line of the formula checks to make sure that the vehicle is not an owner operator, because if it is we do not care.

The 7th and 8th lines make sure the vehicle is set to deploy or dispose, which is required.

The 10th and 11th lines just check to make sure the status date is between two parameter dates. Nothing has been changed to the parameters except that they are dates.

I used then true to return all values that matched these restraints.

IF
(
({@UnitIsBlank} and {@DivisionIsBlank})
and
({ALLOCATION_REASONS_FW.DESCRIPTION_FW} <> 'Owner Operator')
and
(({VEHICLES_FW.VEHICLE_STATUS_FW} = 'DEPLOY') or ({VEHICLES_FW.VEHICLE_STATUS_FW} = 'DISPOSE'))
and
(({VEHICLES_FW.STATUS_DATE_FW} >= {?ReportPeriodStart}) and ({VEHICLES_FW.STATUS_DATE_FW} <= {?ReportPeriodEnd})))
Then True


I am not 100% sure it is my formula causing the problem, but if you can see any error throughout the formula please let me know.

Thanks.

 

[dgillz]

Please post your formulas for {@unitisblank} and {@divisionisblank}

Also, are you sure they are not null? Null and blank are two different things. If you open the database in SQL, do you see <null> anywhere?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

What is the exact error message you are getting? Or what is the problem you are encountering if there is no error message?

-LB

 

[FunkyBunch]

The formula for {@UnitisBlank} is just {?Unit} = "" which checks to see if the user left the parameter blank. This field cannot be null as you are only able to select the blank record or an actual unit.

The problem with this formula is it is not returning the correct amount of vehicles. Between August 30 and Sepetember 15 i was supposed to recieve 8 units.

9648 2007-08-31 00:00:00.000 COMPANY_RENTAL DISPOSE EISC
9649 2007-08-31 00:00:00.000 COMPANY_RENTAL DISPOSE EISC
2967 2007-09-01 00:00:00.000 COMPANY_RENTAL DISPOSE EISC
2673 2007-09-01 00:00:00.000 LEASE_OPERATOR DISPOSE EISC
4291 2007-08-31 00:00:00.000 COMPANY_OWNED DEPLOY EISC
4296 2007-09-10 00:00:00.000 COMPANY_OWNED DEPLOY EISC
4297 2007-09-10 00:00:00.000 COMPANY_OWNED DEPLOY EISC
10003 2007-09-13 00:00:00.000 LEASE_OPERATOR DEPLOY EISC

But i instead recieve:
4291 2007-08-31 00:00:00.000 COMPANY_OWNED DEPLOY EISC
4296 2007-09-10 00:00:00.000 COMPANY_OWNED DEPLOY EISC
4297 2007-09-10 00:00:00.000 COMPANY_OWNED DEPLOY EISC

This is the select statement i run in SQL to recieve the 8 records.
select vehicle_id_Fw, status_Date_Fw, owner_type_Fw, vehicle_Status_FW, company_code_FW from vehicles_Fw
where ((vehicle_status_FW = 'DEPLOY') or (Vehicle_status_Fw = 'DISPOSE')) and
((Status_date_fw >= getdate() -20) and (status_Date_fw <= getdate())) and
((Owner_type_fw <> 'Owner_Operator'))

 

[lbass]

What does the Database->Show SQL Query show? It looks like the problem is with the owner type field, as only company owned showed up. Are you sure you don't have a group selection or section suppression that is preventing the desired display?

-LB

 

[FunkyBunch]

I do not have a group selection or section suprresion. but here is the sql query:


SELECT DISTINCT "VEHICLES_FW"."VEHICLE_ID_FW",
"ALLOCATION_REASONS_FW"."DESCRIPTION_FW", "DEPOTS_FW"."DEPOT_NAME_FW",
"VEHICLES_FW"."BASE_LICENSED_STATE_FW",
"USER_DEFINED1_FW"."DESCRIPTION_FW", "VEHICLES_FW"."MODEL_YEAR_FW",
"MANUFACTURERS_FW"."DESCRIPTION_FW", "MODELS_FW"."DESCRIPTION_FW",
"VEHICLES_FW"."INSURED_VALUE_FW", "VEHICLES_FW"."CHASSIS_NUMBER_FW",
"VEHICLES_FW"."SOLD_DATE_FW", "COMPANY_FW"."DESCRIPTION_FW",
"VEHICLES_FW"."TP_COMPANY_NAME_FW", "VEHICLES_FW"."TP_CONTACT_NAME_FW",
"VEHICLES_FW"."TP_CONTACT_NUMBER_FW", "SUPPLIERS_FW"."COMPANY_FW",
"USER_DEFINED20_FW"."SERIAL_NUMBER_FW",
"USER_DEFINED20_FW"."DESCRIPTION_FW", "USER_DEFINED20_FW"."MODEL_YEAR_FW",
"VEHICLES_FW"."STATUS_DATE_FW", "VEHICLES_FW"."VEHICLE_STATUS_FW",
"VEHICLES_FW"."ARCHIVE_STATUS_FW"

FROM {oj ((((((("Test_EvereadyFleetwaveSQL"."dbo"."MODELS_FW"
"MODELS_FW" LEFT OUTER JOIN
("Test_EvereadyFleetwaveSQL"."dbo"."MANUFACTURERS_FW" "MANUFACTURERS_FW"
LEFT OUTER JOIN "Test_EvereadyFleetwaveSQL"."dbo"."VEHICLES_FW"
"VEHICLES_FW" ON "MANUFACTURERS_FW"."CODE_FW"="VEHICLES_FW"."MAKE_CODE_FW")
ON "MODELS_FW"."MODEL_CODE_FW"="VEHICLES_FW"."MODEL_CODE_FW") LEFT OUTER
JOIN "Test_EvereadyFleetwaveSQL"."dbo"."USER_DEFINED20_FW"
"USER_DEFINED20_FW" ON
"VEHICLES_FW"."VEHICLE_ID_FW"="USER_DEFINED20_FW"."VEHICLE_ID_FW") LEFT
OUTER JOIN "Test_EvereadyFleetwaveSQL"."dbo"."ALLOCATION_REASONS_FW"
"ALLOCATION_REASONS_FW" ON
"VEHICLES_FW"."OWNER_TYPE_FW"="ALLOCATION_REASONS_FW"."CODE_FW") LEFT OUTER
JOIN "Test_EvereadyFleetwaveSQL"."dbo"."USER_DEFINED1_FW"
"USER_DEFINED1_FW" ON
"VEHICLES_FW"."INSURANCE_TYPE_FW"="USER_DEFINED1_FW"."CODE_FW") LEFT OUTER
JOIN "Test_EvereadyFleetwaveSQL"."dbo"."DEPOTS_FW" "DEPOTS_FW" ON
"VEHICLES_FW"."DEPOT_ID_FW"="DEPOTS_FW"."DEPOT_ID_FW") FULL OUTER JOIN
"Test_EvereadyFleetwaveSQL"."dbo"."SUPPLIERS_FW" "SUPPLIERS_FW" ON
"VEHICLES_FW"."FINANCE_SUPPLIER_FW"="SUPPLIERS_FW"."SUPPLIER_FW") LEFT
OUTER JOIN "Test_EvereadyFleetwaveSQL"."dbo"."CUSTOMERS_FW" "CUSTOMERS_FW"
ON "VEHICLES_FW"."CUSTOMER_FW"="CUSTOMERS_FW"."CUSTOMER_FW") LEFT OUTER
JOIN "Test_EvereadyFleetwaveSQL"."dbo"."COMPANY_FW" "COMPANY_FW" ON
"CUSTOMERS_FW"."COMPANY_CODE_FW"="COMPANY_FW"."COMPANY_CODE_FW"}

WHERE "ALLOCATION_REASONS_FW"."DESCRIPTION_FW"<>N'Owner Operator' AND
("VEHICLES_FW"."STATUS_DATE_FW">={ts '2007-08-31 00:00:00'} AND
"VEHICLES_FW"."STATUS_DATE_FW"<{ts '2007-09-15 00:00:00'}) AND
("VEHICLES_FW"."VEHICLE_STATUS_FW"=N'DEPLOY' OR
"VEHICLES_FW"."VEHICLE_STATUS_FW"=N'DISPOSE')

ORDER BY "COMPANY_FW"."DESCRIPTION_FW"

 

[FunkyBunch]

I have found the problem, it was a join with the models_fw table. I had the left outer join from models to vehicles when it should have been vehicles to models.

Thanks a ton for your help!