[b]Help with Record Selection[/b] 1

[thermalman]

Hi I am using CR XI and an SQL BackEnd.

I have a report that shows All Completed Service Calls within a given Date range, which most of the time gives me the results that I want.However just recently i have noticed that if the Last WorkOrders TimeCompleted is outside of the date Range that I specify then it misses it off the report. for example

Call  WO_No   Time Responded  Time Completed Followup  Comp
31544   1     30/11/06 09:30   30/11/06 11:40   TRUE  FALSE
        2     01/12/06 09:35   01/12/06 09:50   TRUE  FALSE
        3     04/12/06 16:00   04/12/06 10:20   TRUE  FALSE
        4     05/12/06 10:40   05/12/06 12:00   FALSE TRUE

When I run the report at the beginning of January 2007 I only get from WorkOrder No 2 (WO_No) onwards as the 1st WorkOrder falls outside of my Date Range 01/12/06 00:00:01 and 31/12/06 23:59:59
I only want ALL of the call details to appear on the report when the TimeCompleted of last WorkOrder is within the Date Range Stated i.e. WorkOrder is the Last one (Maximum) and Followup = FALSE and CALL COMP = TRUE
but I just cannot get the report to show me these results.
Here is my Selection Criteria from my Actualy report

{WorkOrders.GBR_No} like "PF*" and
{WorkOrders.EngineerCode} like "F*" and
{WorkOrders.TimeCompleted} in {?StartDateTime} to {?EndDateTime} and
{LoggedCalls.CallComplete}

If this is not clear enough please let me know and I will try to explain it more.
Can anybody please advise of How I can accomplish this?

Many Thanks
Thermalman

 

[lbass]

Insert a group on {table.call}. Remove the date criteria from your record selection formula and instead go to report->selection formula->GROUP and enter:

maximum({WorkOrders.TimeCompleted},{table.call}) in {?StartDateTime} to {?EndDateTime} and
{LoggedCalls.CallComplete} = true and
{table.followup} = false

This will pull all calls (and ALL work orders) where the most recent call is in the date range and that most recent call is complete but there is no followup. I'm not sure whether the last two criteria are what you intend.

-LB

 

[thermalman]

Hi LBass, I have tried what you suggested but I now get only the last WorkOrder and nothing before it.

here is how I entered the coding you gave

maximum({WorkOrders.TimeCompleted},{LoggedCalls.GBR_No}) in {?StartDateTime}to {?EndDateTime}and
{LoggedCalls.CallComplete}=true and {WorkOrders.FollowUp}=false

I now only get WO_No 4 instead of 1,2,3,4 whereas it should show the following:-

LoggedCalls.GBR_No = PF30772
WorkOrders.WO_No = 1
WorkOrders.TimeResponded = 04/12/06 11:30
WorkOrders.TimeCompleted = 04/12/06 11:40
WorkOrders.Followup = TRUE

WorkOrders.WO_No = 2
WorkOrders.TimeResponded = 05/12/06 09:35
WorkOrders.TimeCompleted = 05/12/06 09:50
WorkOrders.Followup = TRUE

WorkOrders.WO_No = 3
WorkOrders.TimeResponded = 06/12/06 09:40
WorkOrders.TimeCompleted = 04/12/06 10:20
WorkOrders.Followup = TRUE

WorkOrders.WO_No = 4
WorkOrders.TimeResponded = 07/12/06 11:00
WorkOrders.TimeCompleted = 07/12/06 12:40
WorkOrders.Followup = FALSE

LoggedCalls.CallComplete is now =TRUE

We only tick the LoggedCalls.CallComplete checkbox on our system when the Last WorkOrder has been Completed.All of these should be on the report as they are in the same month of closing. Is it because I am NOT asking for the Maximum WorkOrders.WO_No?

 

[lbass]

Sorry, try changing the group selection formula to:

maximum({WorkOrders.TimeCompleted},{LoggedCalls.GBR_No}) in {?StartDateTime}to {?EndDateTime} and
(
if {WorkOrders.WO_No} = Maximum ({WorkOrders.WO_No},{LoggedCalls.GBR_No}) then
(
{LoggedCalls.CallComplete} = true and
{WorkOrders.FollowUp} = false
)
else true
)

-LB

 

[thermalman]

Hi Lbass, It now shows me all of the records that I said but on the Next call on the report it only shows the the 2nd Workorder which was Completed on 01/12/06. The 1st WorkOrder was Completed on 30/11/06 16:00.

How can I get the report to only show All the WorkOrders when the Last WorkOrders.WO_No's TimeCompleted is within the StartDateTime and the EndDateTime and the Call is Completed and the Last WorkOrders Status is set to Followup = FALSE
Or is it impossible to do?

Regards
Thermalman

 

[lbass]

Please show sample data that demonstrates this situation.

-LB

 

[thermalman]

Hi Lbass, I presume that you want to see Sample date of What I actually want the report to show.

If I enter a Date Range of 01/12/06 00:00:01 and 31/12/06 23.59.59
I would expect to only see All the Workorders where the Last WorkOrder Number was Completed within this range.

LoggedCalls.GBR_No = PF30492
WorkOrders.WO_No = 1
WorkOrders.TimeResponed = 30/11/06 10:00
WorkOrders.TimeCompleted = 30/11/06 11:00
WorkOrders.Followup = TRUE

WorkOrders.WO_No = 2
WorkOrders.TimeResponed = 01/12/06 10:00
WorkOrders.TimeCompleted = 01/12/06 11:00
WorkOrders.Followup = TRUE

WorkOrders.WO_No = 3
WorkOrders.TimeResponed = 02/12/06 10:00
WorkOrders.TimeCompleted = 02/12/06 11:00
WorkOrders.Followup = FALSE

This means that the only reason the First WorkOrder is showing on this Report is the fact that the 3rd WorkOrders.TimeCompleted is within the Date Range and the 3rd WorkOrder has Followup set to FALSE and the checkbox has been ticked to say the call is complete.

Please let me know if this is still unclear of what I am trying to achieve.

Regards
Thermalman

 

[lbass]

No, I get what you want, but I don't understand the way in which you said the formula failed on the NEXT call. Please stay with the same example from post to post. I need to see the whole picture--how it worked with the first call but NOT the next.

-LB

 

[thermalman]

Hi LBass, Sorry for the that but I was using the first details as a clarification of the criteria that I wanted the report to show me. On my original report that I have on my desk it shows ALL of the workorders for Call Reference PF30772 because they are ALL Completed within the date Range specified and the Last WorkOrder is within that date Range.

It appears that I have completley messed it up. Can I start again?

On my Original Coding I get the following results if I put in my Date Range 01/11/06 00:00:01 - 31/11/06 23:59:59 I only get WO_No 1

PF30492
WO_No :1
Time Responded : 22/11/06 16:45
Time Responded : 22/11/06 16:55
Followup = TRUE

If I put in my date Range of 01/12/06 00:00:01 - 31/12/06 23:59:59 I only get WO_No 2

PF30492
WO_No :2
Time Responded : 06/12/06 14:35
Time Responded : 06/12/06 14:50
Followup = TRUE

If I put in my date Range of 01/01/07 00:00:01 - 01/01/07 23:59:59 I only get WO_No 3

PF30492
WO_No :3
Time Responded : 08/01/07 14:50
Time Responded : 08/01/07 15:40
Followup = FALSE

So it is only ever showing me the Call that was Completed within my Date Range.

If I then run the Same Date Ranges using your Last Coding I get the following results

01/11/06 00:00:01 - 31/11/06 23:59:59
WO_No 1 (shows in pulled data) but is NOT shown on the report
01/12/06 00:00:01 - 31/12/06 23:59:59
WO_No 2 (Shows in Pulled data) but is NOT shown on the report
01/01/07 00:00:01 - 01/01/07 23:59:59
WO_No 3 Shows on the report but it is the Only WorkOrder on the report ( I want it to show me ALL of the previous WorkOrders as well)

Sorry about the mix up. If I have completed stuffed it up would it be better if I started another thread?

Many Thanks for your Help so far
Thermalman

 

[lbass]

Please paste the record selection formula and the group selection formula into this thread.

-LB

 

[thermalman]

Hi LBass, I thought that I was supposed to remove the Record Selection and put in a Group Selection.

I think i miss read what you told me to do and removed the record selection and only had the Group selection.

I have now added the record selection without the Date Range in it and it appears to be working.

Thank you very much for your continued support, I now have a report that does exactly what i wanted it to do.