Record Selection problem

[lholodnak]

I'm using crystal 10, with oracle and I'm trying to do the following record selection in my reports:

{@wst to service area} in ["BASOM", "NORTH", "SOUTH"] and
{SERVICE_REQUEST.WTR_SWR_IND} = "W" and
{WORK_ORDER_WORK_CODE.WORK_CD} <> "HP" and
{SERVICE_REQUEST.SRVC_RQST_GENERATION_DT} in {?Start reporting date} to
CurrentDate or
{WORK_ORDER.INITIAL_ASSGN_DT} in [{?Start reporting date} to currentdate] and
{REPORTED_PROBLEM.PROB_CD} in ["1161", "1161A", "1161S"] and
{WORK_ORDER.FAC_TYP_CD} = "WSVC" and
{@Service Area} <> "Not in an identified Council District" and
{SERVICE_REQUEST.SRVC_AREA_CD} <> "9"

Everything was working great, until I added the work order initial assign dt line, now for some reason the record selections below that line are not being read, so now I'm getting all problem codes, fac typ, service areas, etc. Does anyone know why? Thanks, Lisa

 

[synapsevampire]

Try:

(
{SERVICE_REQUEST.SRVC_RQST_GENERATION_DT} in {?Start reporting date} to CurrentDate
)
and
(
{@wst to service area} in ["BASOM", "NORTH", "SOUTH"]
)
and
(
{SERVICE_REQUEST.WTR_SWR_IND} = "W"
)
and
(
{WORK_ORDER_WORK_CODE.WORK_CD} <> "HP"
)
and
(
{REPORTED_PROBLEM.PROB_CD} in ["1161", "1161A", "1161S"]
)
and
(
{WORK_ORDER.FAC_TYP_CD} = "WSVC"
)
and
(
{@Service Area} <> "Not in an identified Council District"
)
and
(
{SERVICE_REQUEST.SRVC_AREA_CD} <> "9"
)

I wasn't certain of the requirements based on the record selection formula.

Using parentheticals to clearly differentiate what the constraints are should help.

It may be that you'll need to use the following to get Crystal to pass the SQL to the database:

(
{SERVICE_REQUEST.SRVC_RQST_GENERATION_DT} in {?Start reporting date} to CurrentDate
or
{WORK_ORDER.INITIAL_ASSGN_DT} in {?Start reporting date} to currentdate
)
and
(
{@wst to service area} in ["BASOM", "NORTH", "SOUTH"]
)
and
(
{SERVICE_REQUEST.WTR_SWR_IND} = "W"
)
and
(
{WORK_ORDER_WORK_CODE.WORK_CD} <> "HP"
)
and
(
{REPORTED_PROBLEM.PROB_CD} in ["1161", "1161A", "1161S"]
)
and
(
{WORK_ORDER.FAC_TYP_CD} = "WSVC"
)
and
(
{@Service Area} <> "Not in an identified Council District"
)
and
(
{SERVICE_REQUEST.SRVC_AREA_CD} <> "9"
)
OR
(
{WORK_ORDER.INITIAL_ASSGN_DT} in {?Start reporting date} to currentdate
)
and
(
{@wst to service area} in ["BASOM", "NORTH", "SOUTH"]
)
and
(
{SERVICE_REQUEST.WTR_SWR_IND} = "W"
)
and
(
{WORK_ORDER_WORK_CODE.WORK_CD} <> "HP"
)
and
(
{REPORTED_PROBLEM.PROB_CD} in ["1161", "1161A", "1161S"]
)
and
(
{WORK_ORDER.FAC_TYP_CD} = "WSVC"
)
and
(
{@Service Area} <> "Not in an identified Council District"
)
and
(
{SERVICE_REQUEST.SRVC_AREA_CD} <> "9"
)

Check the Database->Show SQL Query to make sure that everything is being passed correctly.

-k

 

[lholodnak]

I tried what you suggested and I still don't get the records narrowed down. I looked at the sql statement and it looks correct. Tried rebuilding the record select and everything works find, until I add the work order initial assign date. Any other thoughts...L

 

[dgillz]

Do you have nulls in that date field?

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

http://www.trianglepartners.com

 

[synapsevampire]

Try posting specifics.

If you have time, please show example data, and what you expect to be returned.

Stating it isn't what you want doesn't really help.

-k

 

[lholodnak]

Sorry I didn't explain myself better. Yes there are nulls in the date field. In the data base we have unique service requests numbers, but each service request can have many work orders tied to it. So the service request has a generated date and each work order has an initial assign date. I'm trying to select all service requests that fit into the selected date parameter, along with all work orders that has an initial assign date that meets the same date parameter. The reason for using the work initial assign date is because we can have service requests that sit open for many months, days, weeks, etc, without a work order tied to it, then when a crew gets to the sr, then a work order assign date would be assigned in the database. Sorry, but this is very hard to explain, the database is old and was not constructed with reporting in mind. The report should show the following: For a report that had the date parameters of 04/01/2004 to 05/01/2005
sr 455788, wo 1, sr gen date 2004/04/01, wo 1 initial gen date 2004/05/30 would show up on the report.
If I used the date parameter of 05/29/2004 to 06/15/2004, I would still be able to pick up sr 455788, even though the sr gen date was before the parameter. Thanks, L

 

[synapsevampire]

Why would the example of:

For a report that had the date parameters of 04/01/2004 to 05/01/2005
sr 455788, wo 1, sr gen date 2004/04/01, wo 1 initial gen date 2004/05/30 would show up on the report.

If I used the date parameter of 05/29/2004 to 06/15/2004, I would still be able to pick up sr 455788

be correct? The example data shows the {WORK_ORDER.INITIAL_ASSGN_DT} is not in the 05/29/2004 to 06/15/2004 range, it should not show up based on everything you've described.

-k