Help with Formula

Hi
I have a report with hours shown see attached
What I would like is a formula which where the WO Group is "Grouped" to divide the hour figure by the number in that group which may vary from 2 line to many, the criteria for the group is the task number and the next due date
Where the WO Group is "Separate" the hours figure remains as is

Gordon
Sample-report-data.docx
Gordon HughesDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
You want to divide Hours by the number of records in the group (ie. calculate the average)?

 Is the Hours column a field (ie. that same figure is repeated in each record), or a group total, or something else?

 If Hours is a group total, you can right-click on the Hours field on the report (the field from your data, not the group total), select Insert > Summary, change the summary type to Average, and change the "location" to be for the group.

 Or, you can calculate the group average in a formula using

Average ({Hours field}, {group field})
  --or--
Sum ({Hours field}, {group field}) / Count ({Hours field}, {group field})


 If the Hours column is a field from a single record, you can use a formula like the following:

{Hours field} / Count ({Hours field}, {group field})


 If the Hours field could ever be null, you may need to use the CR report option to convert null values to default values (otherwise, the group count or total may not be correct).

 James
Gordon HughesDirectorAuthor Commented:
Hi James
Good to hear from you.
The issue is not the summaries.
There are two types of the hours calculation, will try to explain
On each record as per my attachment, it is either a sperate job, defined as seperate in the wo group, the hour figure for this is correct. Where the wo group field says group, there are several jobs with the same task number and date due, the report duplicates the hours for each task, this is wrong, what i need is to be able to divide the hours shown by the number of jobs with the same task number and same date, so if the hour shown was 10 hours and there were 10 jobs it wants to show 1 hour per job
Hope this makes sense.
Gordon
James0628Commented:
Are CLOCK-HOUR-6M and 18/03/2019 the task number and date that you're talking about?  I'm guessing that those are the fields that you're talking about, but you said in the document that you posted that there were 15 lines in that "group", and I see 16 lines with that task number and date.  Also, your document shows two lines for COMPACTER-12M and 20/03/2019, but it says that they are "Separate".  If those are the fields that you're talking about, it seems like those two lines should say "Grouped".

 It might help if you could post the report (with saved data, if possible).  Right now I'm just making a lot of guesses about what your data actually looks like, and how your report is structured.  For example, are the lines in the document that you posted detail lines, or group totals?  Are there other groups or sorts in the report?  Task Number appears to be the first column on the report, but the report doesn't seem to be sorted by Task Number.  And so on.

 James
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Gordon HughesDirectorAuthor Commented:
Hi James

Report attached

Gordon
Work-Orders-Projections-showing-Craf.rpt
Mike McCrackenSenior ConsultantCommented:
Check the report I modified.  I added a group on the tasknumber then used the count of the tasknumber to calculate the corrected hours for grouped tasks

If you need to calculate a new summary you will need to do a manual calculation or use a running total
Work-Orders-Projections-showing-Craf.rpt
James0628Commented:
mlmcc got to this before I did, so I'll wait until you've had a chance to check his report.  If that doesn't have what you're looking for, post back and I'll take a look at your report.

 FWIW, I will say this ...

 mlmcc said that he added a group on task number, and it sounded to me like your "group" was on both the task number and date.  But, again, I'm just guessing.

 James
Gordon HughesDirectorAuthor Commented:
Hi Guys
mimcc has got the individual hours working spot on.
The only issue now is how to do this that mimcc refers to
"If you need to calculate a new summary you will need to do a manual calculation or use a running total"

Like to old version I need a total shown at Group Header #1and a grand total in the report footer

Gordon
Mike McCrackenSenior ConsultantCommented:
YOu need the total to reflect the total of the new calculated field rather than the total of the original field, right?

If so it is done with 3 formulas
For summary at the group 1 footer the basic idea is

Add to the group 1 header
Name - InitTotals
WhilePrintingRecords;
Global NumberVar TotalHours;
TotalHours := 0;
''

Open in new window


In the detail section

Name - CalcTotals
WhilePrintingRecords;
Global NumberVar TotalHours;
TotalHours := TotalHours + {@CorrectedHours};
''

Open in new window


IN the Group 1 footer
Name - DispTotals
WhilePrintingRecords;
Global NumberVar TotalHours;
TotalHours 

Open in new window


If you need the grand total then you need another variable declared in the report header
The calculation formula changes to also calculate the grand total
A display formula is needed in the report footer

mlmcc
Gordon HughesDirectorAuthor Commented:
Mimcc
Will add these and yes i need a grand total in the report footer
Gordon
James0628Commented:
To add to what mlmcc said ...

 If you need a total for the zCorrectedHours formula, the problem is that that formula uses a summary function (Count), and CR won't do a summary on a summary, so you can't just have CR give you a total for that formula.  You have to calculate the total manually, using a variable (as in the formulas that mlmcc posted), or you might be able to use a running total.

 Either way (formulas and variable, or running total), you won't be able to show that total in the group header, because the total is calculated as the records are read/processed, so you won't have a total until you get to the last record in the group.  That total will have to be in the group footer.

 If you really need to see that total in the group header, one option would be to use a subreport.  You could put a subreport in the group header that would read the records for the current WOTYPE (and the appropriate dates, etc.) and calculate the total.  The subreport would just output the total at the end, and suppress everything else.

 The basic downside to that approach is that your report ends up reading everything twice (once in the main report and again in the subreport).  If the report is not reading that much data, then that may not be a problem.  But if it's reading a lot of data, that's a lot of extra work.

 James
Gordon HughesDirectorAuthor Commented:
mimcc/James

I have added the formula as per mimcc, see attached

The section totals look great,
Any other suggestions to get the grand total, am sure you guys can sort it for me!!
Gordon
Task-Projections-showing-Craf.rpt
Mike McCrackenSenior ConsultantCommented:
I added the additional formulas and made a change to be more efficient and less prone to errors

mlmcc
Task-Projections-showing-Craf-rev2.rpt

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gordon HughesDirectorAuthor Commented:
Excellent support as normal
Great stuff you guys

Gordon
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.