Group Sum not working

YOu are using a group selection formula and it is suppressing numerous records. All completed records requested before the start date are suppressed.

The report is pulling all the WOs for a given Site.  Summaries work on all records and ignore suppression and group selection formulas.

For the BD group, Start Backlog should only count those records that are NOT complete and were requested before the start date.  YOu are counting all WOs that were requested before the start date.

I can think of 2 solutions
1.  Change the selection so it only pulls the NEW WOs (Requested in the time period) or WOs that aren't complete and requested before the start date.

{WO.SITEID} = {?Site} AND
(isnull ({WO.COMPLETIONDATE})  
   OR
  {WO.REQUESTDATE} > {?Start Date})

Select allOpen in new window

2.  CHange you counting formulas to skip the closed records

if {WO.REQUESTDATE} < {?Start Date}  
       AND isnull ({WO.COMPLETIONDATE})  Then 
        1 
else 
        0

Select allOpen in new window

mlmcc

Hi mimcc
Have changed some of the counting formulae but the totals still do not reflect what they shoul
Any ideas

Gordon
WO-Stats.rpt

You have data from after the date range so you need to account for that in the counting
Received WO

if {WO.REQUESTDATE} > {?Start Date}  and {WO.REQUESTDATE} < {?End Date} 
       AND isnull ({WO.COMPLETIONDATE})  Then 
        1 
else 
        0

Select allOpen in new window

Hi mimcc

Sorry to be a pain
Have altered the Receiving WO and I think that is ok
But the completed backlog and completed Received does not appear to be correct
Appreciate your help
Gordon
WO-Stats.rpt

Which WO should be counted?

What is the criteria for Completed Received?
You have Complete in the timeframe AND Completion date IS NULL which obviously can't happen.
Should it be
    Received in the time frame and Completed Date IS NOT NULL?



Same for completed backlog
You have Completed before the Start Date and Received before the End Date and Completion Date is NULL again that cannot happen.
Isn't it really
      Requested before the timeframe and completed in the timeframe?

mlmcc

Hi mimcc
I really do appreciate your support

What is wrong with this
if {WO.COMPLETIONDATE} < {?Start Date}  and {WO.REQUESTDATE} < {?End Date}
       AND isnotnull({WO.COMPLETIONDATE}) Then
        1
else
        0

Gordon

It is NOT IsNull(), also you want it to be > Start Date

if {WO.COMPLETIONDATE} > {?Start Date}  and {WO.REQUESTDATE} < {?End Date} 
       AND NOT isnull({WO.COMPLETIONDATE}) Then 
        1 
else 
        0

Select allOpen in new window

Hi mimcc

This report is a nightmare
Just also found that there are some closed wo with no completion date
if you look at page 2 WO no 0105919 as an example the wo was closed long before the start dat (do not want to show these)
Also still having an issue with the column Completed backlog (it should read 0

Gordon
WO-Stats.rpt

First of all, if you're going to check a field for nulls in a formula, you should do that before you do anything else with that field in that formula.  Unless something has changed, when CR encounters a null field in a formula (except in an IsNull function), it just stops evaluating the formula at that point, so you won't get the correct result.  So you need to check for nulls first (and have your If statement constructed so that CR won't evaluate the other references to that field).  For example

if {WO.COMPLETIONDATE} < {?Start Date}  and {WO.COMPLETIONDATE} < {?End Date} 
       AND not isnull ({WO.COMPLETIONDATE})  Then 
       1
else 
       0

Select allOpen in new window

should be

if not isnull ({WO.COMPLETIONDATE}) and
 {WO.COMPLETIONDATE} < {?Start Date}  and {WO.COMPLETIONDATE} < {?End Date} Then
       1
else 
       0

Select allOpen in new window

If the IsNull test is true, Not IsNull will be false, and CR will stop evaluating the tests at that point.

 Also, as mlmcc mentioned, it seems like {WO.COMPLETIONDATE} < {?Start Date} should probably be {WO.COMPLETIONDATE} > {?Start Date}.

 Or, assuming that you actually do want < {?Start Date}, then checking for < {?End Date} is unnecessary.  Assuming that the start date will always be before the end date, then if a field is before the start date, it will also be before the end date, so < {?End Date} is unnecessary.

 Having said all of that, the best answer would be if you could change the record selection so that the report only included the records that you're interested in (as mlmcc mentioned in his first post).

 Can you explain exactly which records you want to include in the report, as a whole, and in the different counts?  Which date fields in which ranges, and so on?

 James

Welcome James
Now have the 2 best experts on the case, good stuff

I will try to explain
The records I am trying to show are WOs that are open at or the before the Start date, this would be records where the close date is after the start date and the completion date is before the start date or maybe blank. Togeter with wo where the request date falls between the start and end date
 The backlog count is the sum that fall before the start date
The received sum is the count of the requests between the start and end dates
The Completed backlog is the sum of the backlog that then have a completed date within the start and end dates
The completed received is the sum of work orders completed that have a request date within the start ad end dates


Hope this makes sense
Gordon

... this would be records where the close date is after the start date and the completion date is before the start date or maybe blank. Togeter with wo where the request date falls between the start and end date

Are those two separate sets of conditions?  For example, if REQUESTDATE is between {?Start Date} and {?End Date}, you want to see the record, regardless of the values in CLOSEDATE and COMPLETIONDATE.

 Or are those really all one set of conditions, so you only want to see a record if CLOSEDATE > {?Start Date}, and COMPLETIONDATE is null or < {?Start Date}, and REQUESTDATE is between {?Start Date} and {?End Date} ?

 I took it to be two separate sets of conditions.

 I'm attaching a copy of your report, with some changes.

 I've changed the record selection formula based on my comments above, and removed the group selection formula (hopefully the new record selection formula will handle things, and you don't need the group selection anymore).

 I also changed your {@Completed Backlog} and {@Completed Recieved} formulas.  I left the old code in those two formulas (commented out), so that you can see what I changed.

 {@Start Backlog} and {@Recieved WO} look OK to me.  In case you're wondering, those two formulas check for IsNull ({WO.COMPLETIONDATE}) last, but that's OK in those two cases, because the other tests are on other fields.  The first (and only) reference to COMPLETIONDATE in those two formulas is the IsNull test, so it's fine if the IsNull test is at the end.


 On a side note:
 When you're comparing the dates to {?Start Date} or {?End Date}, it seems like you always use < or >.  You probably want to use <= and/or >= in some places.  For example, when you use

{WO.COMPLETIONDATE} > {?Start Date}  and {WO.COMPLETIONDATE} < {?End Date}

 you probably want

{WO.COMPLETIONDATE} >= {?Start Date} and {WO.COMPLETIONDATE} <= {?End Date}

 You might want to review the formulas, including the record selection formula, and see if some of those tests should be changed.

 James
WO-Stats_J.rpt

Hi James

The record selection is still not correct
I have corrected some of the column figures and they now look correct
I think the issue now lies with the record selection, which I have tried to change, without success

The requirement is to show
Records where the close date is after the start date
The completion date could be null or after the start date
The request date could be before the start date or between the start and end date

Hope this makes sense
Gordon
Have attached your report updated
WO-Stats_J.rpt

First of all, just to make sure, you only want the records that meet all of those conditions, right?  You're testing 3 different fields, so you could be looking for any records where fieldA is this, or fieldB is this, or fieldC is this.  I assume that you're looking for the records where all 3 fields meet your conditions (fieldA is this and fieldB is this and fieldC is this), but I wanted to make sure.

 You said "request date could be before the start date or between the start and end date", but in your record selection, you have

{WO.REQUESTDATE} >= {?Start Date} and
{WO.REQUESTDATE} <= {?End Date}

 If you really want to see the records where REQUESTDATE is before the start date, drop {WO.REQUESTDATE} >= {?Start Date} from the record selection.  You just need to check for {WO.REQUESTDATE} <= {?End Date}.

 Also, since it seems that COMPLETIONDATE can be null, can CLOSEDATE or REQUESTDATE ever be null?  Just thought I'd check.  If either of them could be null, you should check for nulls in any formula that references that field.

 James

Hi James /Mimcc
The record selection is not right yet
The current record selection is only giving records where there is a completion date, somehow it need something like or isnull {WO.COMPLETIONDATE} added

Also the current completed Received should only be 1 if the completion date fall between the start and end dates.


Not sure about the completed backlog as the record selection is not correct, but again this should only be were the record request date is before the start date and the completion date is between the start and end dates

Have attached the file again
WO-Stats_JV1.rpt

The current record selection is only giving records where there is a completion date, somehow it need something like or isnull {WO.COMPLETIONDATE} added

The record selection formula in the report that you posted already has that.  The following is copied from that formula:

(isnull ({WO.COMPLETIONDATE}) or {WO.COMPLETIONDATE} > {?Start Date}) and

 So, the report is trying to include null Completion dates.  If you look at Database > "Show SQL Query", you should see the query that CR is sending to the db.  You should see something like COMPLETIONDATE IS NULL in the Where.  That will just confirm that the report is trying to include nulls.

 Are you sure that COMPLETIONDATE is actually null, and not some other value?

 Are you sure that the site and date range that you selected include some records where COMPLETIONDATE is null?  You might try a different site, or expanding the date range, and see if any nulls show up.

 Or, to come at this from a different direction, you could temporarily remove all of the date checks from the record selection formula, so that you get all of the records for the specified site, and then look for null Completion dates on the report.

 If you don't see any and you're sure that there should be some for that site, then you need to figure out why there aren't any (maybe they're not really null).

 If there are null Completion dates, you can check the Close and Request dates on those records and see if they fall in the date ranges that you've been using.

Also the current completed Received should only be 1 if the completion date fall between the start and end dates.

In an earlier post (a few days ago), you said "The completed received is the sum of work orders completed that have a request date within the start ad end dates", so I changed {@Completed Recieved} to check REQUESTDATE, instead of COMPLETIONDATE.  If you actually want to check Completion date there, just replace the two WO.REQUESTDATE references in that formula with WO.COMPLETIONDATE.  In that case, your old formula was mostly correct, except for the fact that the IsNull test should be first.

 James

Hi James

Have attached 2 reports

The WO-Stats_JV1 only shows the records that contain a completion date


The WO Stats report show records where the completion date is null or has a value, the issue with this one is that it also shows records where the completion date is null but the closedate is before the start date
, if I could remove the records where the closedate is before the start date I think this would work??
WO-Stats.rpt
WO-Stats_JV1.rpt

Try changing the group selection to

{WO.SITEID} = {?Site} AND
(
    (
     isnull ({WO.COMPLETIONDATE})  and
     Not IsNull({WO.CLOSEDATE})
    )  
OR
    {WO.REQUESTDATE} > {?Start Date}
)

Select allOpen in new window

mlmcc

Hi Mimcc

Have changed the group selection as per your last post
But it still shows wos that have been closed before the start date, see page 2
Where the close date is 01/01/1900 means that the WO is open and should be on the report
Where the Close date is a valid date ie 14/9/2001 these are not to be shown as the date is before the start date
Hope this makes sense

Gordon
WO-Stats_JV1.rpt

Try

{WO.SITEID} = {?Site} AND
(
    (
     isnull ({WO.COMPLETIONDATE})  and
     IsNull({WO.CLOSEDATE})
    )  
OR
    {WO.REQUESTDATE} > {?Start Date}
)

Select allOpen in new window

Hi Mimcc
Afraid it still does not work
Have WOs beyond the end date which should not be included
Am also missing WOs with a requestdate before the start date where the completion date is null or the completion date is greater than the start date or the close date is greater than the start date
Attached report
WO-Stats_JV1.rpt

Why are you using group selection instead of record selection?

 And if you're going to use group selection, you would normally reference some kind of group "summary".  For example, if you were showing the YTD sales for customers (so the record selection included all of the sales this year), but you only wanted to see the customers that had some sales in the first half of this year, the group selection might be something like

Minimum ({Invoice date}, {Customer group field}) < Date (2018, 07, 01)

 But in the WO-Stats.rpt report that you posted yesterday (I haven't looked at the others), you're just checking individual fields (WO.SITEID, WO.COMPLETIONDATE, and WO.REQUESTDATE).  I really don't know what CR will do with those in a group selection.  One guess would be that it's looking at one record (maybe the first or last) in a group, but since you have two groups, and don't specify a group in the formula, I really don't know what it's looking at.  Maybe it effectively ends up doing a record selection (since you don't reference a group, it looks at each record as a "group").  Or not.  I don't know.

 If nothing else, you can remove WO.SITEID from the group selection, since you're checking it in the record selection.  For the record, the group selection would probably be a terrible place to check the site.  If you checked it there, and not in the record selection, then CR would presumably read every site and then suppress all of the other sites (groups).  You don't have that problem, because you are checking it in the record selection.  But there's no need to check it in the group selection too.

 James

Hi
I have moved the formula to the record selection, still has the comments I made

Afraid it still does not work
Have WOs beyond the end date which should not be included
Am also missing WOs with a requestdate before the start date where the completion date is null or the completion date is greater than the start date or the close date is greater than the start date
Attached report


Gordon
WO-Stats_JV1.rpt

Try this one

{WO.SITEID} = {?Site} and
(
    (
      {WO.REQUESTDATE} < {?Start Date} AND 
        (
        isnull ({WO.COMPLETIONDATE})  OR
        IsNull({WO.CLOSEDATE})  OR
        {WO.COMPLETIONDATE} > {?Start Date} OR
        {WO.CLOSEDATE} > {?Start Date}
        )
    )  
OR
    (
    {WO.REQUESTDATE} > {?Start Date} AND 
    {WO.REQUESTDATE} <= {?End Date} 
    )
)

Select allOpen in new window

mlmcc

Hi Mimcc

OK added that formula but it is still showing some WOs that have a requestdate before the start date but they also have a close date or completion date before the start date
Look at page 2 wo
0119718 completion date is null but close date is before the start date
, same with the next 5 records

The issue here is that there is no completion date

Gordon
WO-Stats_JV1.rpt

The way that your record selection formula is written, if REQUESTDATE is before the start date, you include the record if

COMPLETIONDATE is null
   or
CLOSEDATE is null
   or
COMPLETIONDATE is after the start date
   or
CLOSEDATE is after the start date.

 So, for example, if COMPLETIONDATE is null, it doesn't matter what's in CLOSEDATE.


 Try the record selection formula below.

{WO.SITEID} = {?Site} and
(
    (
    {WO.REQUESTDATE} < {?Start Date} AND 
    (IsNull ({WO.COMPLETIONDATE}) OR {WO.COMPLETIONDATE} > {?Start Date}) AND
    (IsNull({WO.CLOSEDATE}) OR {WO.CLOSEDATE} > {?Start Date})
    )  
OR
    (
    {WO.REQUESTDATE} > {?Start Date} AND 
    {WO.REQUESTDATE} <= {?End Date} 
    )
)

Select allOpen in new window

 I reorganized the COMPLETIONDATE and CLOSEDATE tests so that if REQUESTDATE is before the start date, you include the record if

COMPLETIONDATE is null or after the start date
   and
CLOSEDATE is null or after the start date.

 So, using my earlier example, if COMPLETIONDATE is null, the record won't be included if CLOSEDATE is before the start date.  CLOSEDATE has to be null or after the start date.

 James

Hi James
Nearly there the report is missing requests that are before the start date where the completion date is null
Report attached and word doc with the missing WO they are all open and would have aa close date of 01/01/1900

Gordon
WO-Stats_JV1.rpt
Missing-WOs.docx

Using James basic idea since the close dates can be 1/1/1900

COMPLETIONDATE is null or after the start date
   and
CLOSEDATE is null or after the start date OR CLOSEDATE = Date(1900,1,1).

Yeah, if the problem is simply that a missing close date could be null or 1/1/1900, then try adding 1/1/1900 to the checks.

{WO.SITEID} = {?Site} and
(
    (
    {WO.REQUESTDATE} < {?Start Date} AND 
    (IsNull ({WO.COMPLETIONDATE}) OR {WO.COMPLETIONDATE} > {?Start Date}) AND
    (IsNull({WO.CLOSEDATE}) OR {WO.CLOSEDATE} = Date (1900, 1, 1) OR {WO.CLOSEDATE} > {?Start Date})
    )  
OR
    (
    {WO.REQUESTDATE} > {?Start Date} AND 
    {WO.REQUESTDATE} <= {?End Date} 
    )
)

Select allOpen in new window

 James

James/Mimcc

OK I think the record selection now looks ok
On to some of the other colums
The starting backlog now is not correct
the existing forula is
if {WO.REQUESTDATE} < {?Start Date}  
       AND isnull ({WO.COMPLETIONDATE})  Then
        1
else
        0
but the top 2 records are not shown as backlog, think this is because the completion date is not null, but is after the start date

Gordon
WO-Stats_JV1.rpt

James/Mimcc

I think we are there I modified the completed backlog and completed received formula and it seem to work
I really do appreciate help from both of you
Have attached the lasted version, see what you think

Gordon
WO-Stats_JV1.rpt

James/Mimcc
Ok Now the data looks ok I would like to create a cross tab showing the totals
Should I close this question and open another?

Gordon

I think that would be appropriate

mlmcc

Hi James/Mimcc

Hopefully I have shared the point equally

Thanks for your excellent support

Gordon

Glad to hear that we finally got it sorted out.  :-)

 James