Report not grouping data correctly

FWIW, I don't get any saved data with the report.  That may be because I'm using an older version of CR, but it doesn't say anything about discarding saved data.  There's just nothing there when I open the report.  So I can't see the items on page 2 that you mentioned.

 It seems that you need to change something in the Tasks formula in the subreport, but I don't know exactly what.  In addition to accumulating the total that you're referring to (I think), that formula also builds the task list.  Does the task list show "extra" entries?

 If it does, then you've presumably got "duplicate" detail records, and the Tasks formula needs to be changed so that it only includes those records once (in the totals and in the task list).

 If the task list is OK, then the problem is presumably that the hour figure is for a group of records, not the individual records.

 From your description, it sounds like the latter situation, but I wanted to make sure.

 Either way, the next question would be, how do you identify the "duplicate" records, or define the "group"?  I guess the records in question are for a work order, so how do you define a work order?

 Assuming that the task list is OK, and the problem is that the hour figure is for a group of records, then if you have something like a work order number, you could sort by that and then use Previous (or Next) to check the work order number in the previous (or next) record, and only add the hours once for each work order number (if MULTITASK is "Y").

 James

I have a later version and don't get any message or see data.

mlmcc

I looked through the report and all the formulas.

I don't see where you are using the MultiTask value to control how hours are calculated.

Perhaps if we had the data in the report we could see it.

mlmcc

I don't see where you are using the MultiTask value to control how hours are calculated.

I think that's basically what he's asking.  He's not using it now, because he doesn't know how/where to use it.  If you look at the list of fields in the subreport, TASK.MULTITASK isn't used at all.

 James

Hi mimcc and James

I will try and send the report with data
mimcc had this report working at some point, but not sure how or what is wrong
I know it is something in the sub report but it is beyond me!!

Gordon

Hi

Trying again
have also included a PDF with the data if you cannot see it through CR

Gordon
Work-Projection-V3-3.rpt
work-projection-v3-3.pdf

Data is there.  Can you put the multitask field onto the report in the detail section.  SInce it isn't there, there is no data for it in the report and when we try to use it, it wants to connect to the database.

mlmcc

I see the issue, but not sure how to deal with it.

DO you need the hours shown for each piece of equipment or could the hours be shown only for the first equipment line?

mlmcc

Hi mimcc
Ok
Where the task is set to "Release as a seperate work Order" TASK.MULTITASK =N then the hours should be shown for each piece of equipment
Where the task is set to "Release as a single work Order" TASK.MULTITASK =Y then the hours should be for the task irrespective of how many piece of equipment

So:-
If the TASK.MULTITASK is set to NO and the task say is set to 2 craft hours and the task contains 6 pieces of equipment . then the total craft hours would be 6 times 2 hours = 12 hours (each piece of equipment should show 2 hours

If the TASK.MULTITASK is set to Y and the task say is set to 2 craft hours and the task contains 6 pieces of equipment , then the total craft hours is Hours (if it is easier to show the total hours (2) could be divided by the number of equipment (6) therefore each piece of equipment would be (2/6) 0.33 hours

Hope this makes some sense

Gordon

CHeck this report.  Since the MultiTask filed is not in the report my formulas require a database read to test.

If they don't show the correct numbers post the new report with data and I'll look into the issue.

mlmcc
Work-Projection-V3-3.rpt

Hi mimcc

Great job so far as normal
I think the craft hours are OK, just need some presentation changes to make it easier to view
Have attached the report you last sent me with data
Have also attempted to change the presentation as shown on the (2) version
Would like some changes as per the word document, which I hope makes sense

Gordon
Work-Projection-V3-3--1-.rpt
work-projection-v3-3--1-.pdf
Work-Projection-V3-3--2-.rpt
work-projection-v3-3--2-.pdf
Work-Projection-Reports.docx

I think I can help with expanding the text boxes (as noted in your Word doc).

 First, I went into the field format and added borders around the fields, just so I could see the actual field size, in comparison to the text in the field.  You may not need that, but I found it very helpful.

 To make the lines longer, you can edit the appropriate Format* formula (eg. FormatFullLocation) and change the value in the LineLength variable.  That controls how many characters the formulas put in each line.  The trick is knowing how many characters will actually fit in the field.  As long as you're using a proportional font for those report fields, you can't know the exact limit (it will depend on the characters in the field).  You just have to make an approximation.  But the following should help you with that.

 You can temporarily add a sort of "ruler" line to the field, so you can see how many characters fit.  For example, edit @FormatFullLocation and right before the For loop, it has

CurrentFullLocation := '';

 Just add another line, between that line and the For loop, like this:

CurrentFullLocation := '1234567890123456789012345678901234567890123456789012345678901234567890';

 That puts that string of numbers at the beginning of the field, before the locations.

 Then when you view the report, you can count how many characters fit on the first line (each "0" in the string is another 10 characters).  I'm getting 61 here.  You can set LineLength in that formula to something below that.  Maybe around 50?

 When you're done, just comment out that line that you added, to remove the numbers.  You could delete the line, but if you change the field width on the report, you might want to use those numbers again, so I'd leave the line and just comment it out.

 For a wider field (like for the task descriptions), you may need to add more numbers to the string.


 Another potential issue is that the lists sometimes (Always?) have one or more extra carriage returns at the end of them, which adds a blank line under the field.  This is easy to see if you turn on the bottom border in the field format.  If you don't want that blank line, the extra carriage returns could be removed.

 James

Hi mimcc and James

I have amended the length of the fields by changing the field length in the Formula*
This seems to have tidied it up a bit, but for some reason I get large blank gaps
See page 2 under April 2015 and page 9 in the middle
Also would like to show if the Multitask is Yes or No at the  Group Header 2 on the sub report

Any suggestions
Have included the rpt file and a pdf file

Gordon
Work-Projection-V3-3--3-.rpt
work-projection-v3-3--3-.pdf

It can be difficult to tell what's what in a subreport (like where those blank lines are coming from), because when you view the report, clicking on anything in the subreport just selects the entire subreport, not the individual field or section.  One simple trick to identify the source of those blank areas is to set a different bg color for each section in the subreport.  Go into the Section Expert for different sections in the subreport, select the Color tab, check the "Background Color" box and pick a color.

 Doing that shows that the empty space at the top of page 2 is GH2.  You've got several of those in a row that aren't displaying anything.  Whether or not they _should_ be blank, I have no idea.  I don't understand your report and data well enough to answer that.

 If they're supposed to be blank, then maybe you just need to check the "Suppress Blank Section" option for GH2.

 If they're not supposed to be blank, then, obviously, you have to figure out why they are blank.  That does bring up something that I'd wondered about before.

 The formulas in GH2, like @DisplayTasks, include EvaluateAfter ({@Tasks}) .  But @Tasks is in a detail section, so I don't know what, if anything, EvaluateAfter means in that context.  I don't think it's possible for a formula in a group header to be evaluated after a formula in a detail section, because the group header is presumably produced before the detail section.  Those EvaluateAfter functions seem meaningless to me.  But I could be wrong.


 As for Multitask, the obvious answer would be to put the field in GH2, but, since that is so obvious, I'm guessing it's not that simple.  But, once again, I don't understand your report and data well enough to give you a better answer than that.

 James

Hi James
I have ticked the "Suppress Blank Section" in all the subreport sections
I have added a formula to show Multitask information
@Multitask = if {TASK.MULTITASK} = 'Y' then 'Release as Single Work Order' else
if {TASK.MULTITASK} = 'N' then 'Release as Seperate Work Orders'
I have put borders around the fields in Group Header #2 on the sub report
As can be seen from the attached it duplicates information (example page 1 bottom) incorrectly
Also there is duplication of the task on some pages as can be seen on page 1 first task

Not sure what is going on

Gordon
Work-Projection-V3-3--4-.rpt
work-projection-v3-3--4-.pdf

I really don't know about the Multitask thing.  You're displaying things in the group header that come from variables, which are filled in the detail sections.  And it _seems_ like you've got different values in MULTITASK in different detail records in the same group.  If so, which value should be used in the group header?

 As for the duplicated task numbers and descriptions in GH2, I know why that's happening (at least in some cases), and maybe how to fix it.

 The Tasks formula has a "While NextDue ..." loop at the end that adds some values to variables once for each time period in your range.  That includes the task number and description.  So, if there are multiple time periods for a task, and maybe depending on MULTITASK, you get the task number and description repeated for each time period.  That might be correct if you were displaying the task number and description beside those other values, but it doesn't make sense when they're displayed separately, in the group header.

 In the Tasks formula, find the "While NextDue <= {?MonthEnd} do" line towards the end.  The first commands in the loop set TaskList and TaskDescriptions.  Try moving those lines to right above the "While" line, so they're out of the loop.

 James

mlmcc,

 Since you're "back", I've got to ask:

 How is it supposed to work, having the group header display variables that are filled in a formula in a detail section?  That doesn't make much sense to me.

 Given what's just come up about the task numbers and descriptions being repeated, maybe all of the variables were originally meant to be displayed in the same detail section?


 Oh, yeah.  Something else I'd thought about mentioning earlier, and just never remembered:

 I can't edit the Tasks formula "directly" in CR 10.  If I right-click on the formula in the Field Explorer, there is no Edit option.  But if I edit another formula and then select Tasks, it seems fine.  It's probably not significant, but I don't remember ever seeing anything like that before.

 James

Hi mimcc
Welcome back
Ok looking at your latest version I can see there are tasks shown with no equipment under them
Example page 1 QS/E/BUR/52W/01
Can these be suppressed? to remove the clutter

Have included the PDF verion of this

Gordon

Hi

I am trying to find out why there is a big difference in hours between the projection report through the application and the CR report
Example the app tell me for May the total hours are 302.50
The CR tells me the total hours are 2363.57

The reason for the CR is that the app report does not give enough details

Gordon

Hi James/mimcc

Ok have found the issue with the totals
Having checked the details with the app report, the content is Ok (except it would be nice to suppress the unwanted tasks) but the totals are not correct

Gordon

I may have a fix for the totals.  In @Tasks, when you add hours to the HoursList string, if MULTITASK = "Y", you use NUMOFPEOPLE * ESTLABORHOURS / (distinctcount of EQNUM).  If MULTITASK <> "Y", you use NUMOFPEOPLE * ESTLABORHOURS.

 But right after that, when you update the variables, like TotalMonthHours, you don't check MULTITASK and just use NUMOFPEOPLE * ESTLABORHOURS.  I'm guessing that that is throwing off your totals (by multiplying the hours for multitask items).

 Here's a modified version of the Tasks formula from the last report that mlmcc posted.  I could post the whole report, but I don't know what changes you might have made to the report, so I'm only posting the modified formula.  Basically, I added a task_count variable, which is set based on MULTITASK, and is then used to divide the hours, both when they're added to the HoursList string, and when they're added to the variables.

EvaluateAfter({@FormatTaskDescription});
Local StringVar DateUnit;
Local DateVar NextDue;
Local NumberVar LinesRequired;
Local StringVar strCR;
Local NumberVar Index;
Local NumberVar task_count;

Global StringVar TaskList;
Global StringVar TaskDescriptions;
Global StringVar DateList;
Global StringVar ResourceList;
Global StringVar EquipmentList;
Global StringVar EquipmentDescriptions;
Global StringVar FullLocationList;
Global StringVar HoursList;

Shared NumberVar TotalMonthHours;
Shared NumberVar GrandTotalHours;
Global NumberVar TotalCraftHours;

Global StringVar CurrentTaskDescription;
Global NumberVar CurrentTaskDescriptionNumLines;
Global StringVar CurrentEquipmentDescription;
Global NumberVar CurrentEquipmentDescriptionNumLines;
Global StringVar CurrentFullLocation;
Global NumberVar CurrentFullLocationNumLines;

TaskList := "";
TaskDescriptions := "";
DateList := "";
ResourceList := "";
EquipmentList := "";
EquipmentDescriptions := "";
FullLocationList := "";
HoursList := "";


If Not (IsNull({TASKEQ.NEXTDUEDATE})) then
(
   NextDue := Date({TASKEQ.NEXTDUEDATE});
   If {TASKEQ.DATEUNIT} = 'D' then
     DateUnit := 'd'
   Else If {TASKEQ.DATEUNIT} = 'W' then
     DateUnit := 'ww'
   Else If {TASKEQ.DATEUNIT} = 'M' then
     DateUnit := 'm'
   Else If {TASKEQ.DATEUNIT} = 'Y' then
     DateUnit := 'yyyy'
   Else
     DateUnit := 'd';

   While NextDue < {?MonthStart} do
     NextDue := Date(DateAdd(DateUnit,{TASKEQ.NUMOFDATE}, NextDue));

   LinesRequired := CurrentTaskDescriptionNumLines;
   If CurrentEquipmentDescriptionNumLines > LinesRequired then
      LinesRequired := CurrentEquipmentDescriptionNumLines;
   If CurrentFullLocationNumLines > LinesRequired  then
      LinesRequired := CurrentFullLocationNumLines;

   For Index := 1 to LinesRequired do
      strCR := strCR & chr(13);

   For Index := CurrentTaskDescriptionNumLines to LinesRequired do
        CurrentTaskDescription := CurrentTaskDescription & chr(13);
   For Index := CurrentEquipmentDescriptionNumLines to LinesRequired do
        CurrentEquipmentDescription := CurrentEquipmentDescription & chr(13);
   For Index := CurrentFullLocationNumLines to LinesRequired do
        CurrentFullLocation := CurrentFullLocation & chr(13);

   TaskList := TaskList & {TASK.TASKNUM};
   TaskDescriptions := TaskDescriptions & CurrentTaskDescription;
   While NextDue <= {?MonthEnd} do
   (
     DateList := DateList & CStr(NextDue,"dd-MMM-yy") & strCR;
     ResourceList := ResourceList & {TSKCRAFT.CRAFT} & strCR;
     EquipmentList := EquipmentList & {TASKEQ.EQNUM} & strCR;
     EquipmentDescriptions := EquipmentDescriptions & CurrentEquipmentDescription;
     FullLocationList := FullLocationList & CurrentFullLocation;

     If {TASK.MULTITASK} = 'Y' then
         task_count := DistinctCount ({EQUIP.EQNUM}, {TASK.TASKNUM})
     Else
         task_count := 1;

     HoursList := HoursList & CStr({TSKCRAFT.NUMOFPEOPLE} * {TSKCRAFT.ESTLABORHOURS} / task_count, 2) & strCR;

     TotalMonthHours := TotalMonthHours + {TSKCRAFT.NUMOFPEOPLE} * {TSKCRAFT.ESTLABORHOURS} / task_count;
     GrandTotalHours := GrandTotalHours + {TSKCRAFT.NUMOFPEOPLE} * {TSKCRAFT.ESTLABORHOURS} / task_count;
     TotalCraftHours := TotalCraftHours + {TSKCRAFT.NUMOFPEOPLE} * {TSKCRAFT.ESTLABORHOURS} / task_count;
     NextDue := Date(DateAdd(DateUnit,{TASKEQ.NUMOFDATE}, NextDue));
    );
);
'';
//LinesRequired

Select allOpen in new window

 FWIW, it's possible that you don't need to check MULTITASK when setting task_count.  If anything that is not a multitask will only have one EQNUM, you could just set task_count to the distinct count of EQNUM, without checking to see if MULTITASK = "Y".

 James

Hi James

Have updated the report with the new TASKS formula
The subtotals look wrong, which also means the grand total is wrong
Ie page 1 CONTRACTOR hours subtotal is 8 (should be 4)
Page 26 GENERAL OPERATOR subtotal shows 20 (guess it should be 12)

The report still shows tasks with no equipment linked to them, makes the report confusing
Have attached latest report together with PDF files, 1 of which shows the summary figures

Gordon
Work-Projection-V3-3--4-rev3.rpt
work-projection-v3-3--4-rev3-summary.pdf
work-projection-v3-3--4-rev3.pdf

I'll look at it tonight.  Don't have a machine with Crystal readily available.

I have tried to modify the total formulas but the numbers got even worse.  Will try to look into it in more detail tonight.

mlmcc

mimcc

Would be appreciated

Gordon

OK, I figured out another problem with the totals.

 The DisplayTasks and DisplayTaskDescriptions formulas in GH2 both include EvaluateAfter ({@Tasks}).  Tasks is in the detail section, so it's evaluated with each record.  The problem is that including EvaluateAfter ({@Tasks}) in the formulas in GH2 apparently forces Tasks to be evaluated an extra time for GH2, so the hours from the first record in the group are added to the totals twice.  (On the plus side, at least for me -- I'd been wondering how the variables in the group header were getting values from a formula in the detail section, and I guess now I know :-)

 The good news is that since you're grouping by TASKNUM and displaying the task # and description in the group header, I don't think you need the TaskList and TaskDescriptions variables.  I think the idea of the variables was to create a list of values, to be displayed beside the other values that are repeated for each "period" in the selected date range.  But if you're just displaying the task # and description in the group header, it doesn't seem like you need those variables at all.

 The upshot is:
 Try removing DisplayTasks and DisplayTaskDescriptions from GH2, and replace them with TASK.TASKNUM and TASK.DESCRIPTION .  When I do that, I get a total of 4 hours for Contractor.

 FYI, I can't check any of the other totals, because I can't get past page 2 of the main report.  I don't know why.  I've been having this problem all along (it didn't just start with this latest version of the report).  When I open the preview, I get a data source connection prompt.  I Cancel that and get the first page.  When I go to the second page, same thing.  But then if I try to go to the third page, or skip to the last page, I get the data source connection prompt twice, and then I end up back at the first page.  It's very odd.  :-)


 As for "tasks with no equipment", can you explain what "no equipment" means?  How do you identify those tasks?  Is there a specific field that would be blank/zero/null, or a table (eg. TASKEQ or EQUIP) that won't have matching records, or ...?

 James

 PS:
 For the record, I finally figured out why I couldn't edit the Tasks formula "directly" (as mentioned in an earlier post).  The field was set to read-only.  That's just one of those features I never used, so it hadn't occurred to me, until I realized that I also couldn't resize, etc. the field.  It does seem a little odd that setting the field to read-only prevents you from editing the formula, but whatever.  One more mystery solved.  :-)

Hi James
Amended as you said, I think the subtotals and the grand total look good now

In terms of the "tasks with no equipment",  the table that would not be shown would be the link to the TASKEQ table, fields EQNUM, LOCATION, SUBLOCATION1 , SUBLOCATION2 , SUBLOCATION3 DATEUNIT, NUMOFDATE

Have attached latest report and PDF file

Gordon
work-projection-v3-3--4-rev4.pdf
Work-Projection-V3-3--4-rev4.rpt

I'm still not sure about the "no equipment" thing.  Are you saying those tasks would not have any matching records in TASKEQ?  I tried excluding the tasks that had no matching TASKEQ record, and it didn't seem to change anything on the first page.

 Can you give us some specific examples of tasks on the first couple of pages in your report that should not be there?

 James

Hi James
I think I can see where the confusion is
The equipment is only shown on the report where a due date is within the period
So there would be equipment shown in the TASKEQ table
What I would like suppressed is where there is no equipment schedule shown under the task

Have attached a word doc with a screen shot, if it helps

Gordon
Example-List-of-Tasks-that-should-be-sup

Hi James

That seems to have worked, will validate later today

Thanks
Gordon

Guys

Well done, working Ok
I would like to get a variation on this report to be able to see tasks and schedules within a specified period, rather than monthly
I will close this post and create a new one

Gordon

A challenge but had the top experts on the case
Thanks

You're welcome.  Glad I could help.

 James