Avatar of dkollmann
dkollmann asked on

Crystal Reports XI How do I Sum a group field that has a calculation.for different criteria subtotals

I am trying to create a report that shows overtime based on hours over 40 per week
I can create the formula that shows the overtime by employee for a week that is because I am only pulling in one week worth of records and grouping by employee.

I want to sum the overtime based on a Criteria of the employee example:

Criteria /Employee / Hours / OT hours - Group footer

Summary Section:

Criteria:
Admin / Hours/ OT
Direct Labor / Hours/OT


DB Reporting ToolsCrystal Reports

Avatar of undefined
Last Comment
dkollmann

8/22/2022 - Mon
analliah

If you create your formula and place in the detail section of the employee. You can create a running total to sum your formula

On the evaluate part - click on the x-2 and place your criteria on when to evaluate the sum function
Mike McCracken

How are you getting the overtime hours?

mlmcc
ASKER
dkollmann

I cannot place that in the detail section because it is totaling from the details section

I am using this formula
Sum ({LaborHed.PayHours}, {EmpBasic.Name})> 40

I attached a screen shot of the design

The shortCharacter is the criteria and mytotal is the calc field in the group of each employee

Doc3.doc
Your help has saved me hundreds of hours of internet surfing.
fblack61
Mike McCracken

Can you upload the report?

mlmcc
ASKER
dkollmann

Hi mlmc,

I have attached a similiar question another user asked on how to sum a formula in crystal the difference is his calculation is being done at the details section I have to wait for my calculation to be done at the group section because I need a summary before I can subtract from the total of 40 hours. Like I said before this works fine for the first group employees but I want subtotals for additional groups.


EExchange.pdf
Mike McCracken

Try this

Add a formula to the report header
WhilePrintingRecords;
Global NumberVar TotalOTHours := 0;
''
Where you calculate the overtime hours
WhilePrintingRecords;
Global NumberVar TotalOTHours;
Local NumberVar OTHours;
Local NumberVar TotalHours := Sum ({LaborHed.PayHours}, {EmpBasic.Name});
If  TotalHours > 40 then
    OTHours := TotalHours - 40
Else
    OTHours := 0;

TotalOTHours := TotalOTHours + OTHours ;
OTHours

mlmcc

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
dkollmann

Hi mlmcc

I tried the formula you provided it will calculate for each employee but when I try to sum up a category
it only takes the record of the last employee and does not sum up all employees in that category.

I am looking for a subtotal of the category.

Thank You
Mike McCracken

You need to add a formula to display the total

WhilePrintingRecords;
Global NumberVar TotalOTHours;
TotalOTHours

mlmcc
ASKER
dkollmann

Hi mmlc,

Thank you again for taking the time to help me
I have attached a layout In excel how I have my crystal report layed out.

I am not sure it is evaluating the total at the proper time.
I am getting 0.00 for the subtotal
Payroll-overtime-excel-display.xls
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
dkollmann

I attached a preview of the report so you can see what it is doing when I try to subtotal a grouped formula field

Crystal-Report-Example1.pdf
Mike McCracken

You can attach the report itself.  Just make a copy and change the extension to .TXT

mlmcc
ASKER CERTIFIED SOLUTION
dkollmann

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question