suppressing groups based on summarized totals 2

[schaeff]

I am attempting to write a report that displays just exceptions for payroll time tracking records. I want to suppress all individuals and all the groups and associated detail records beneath them if they don’t qualify as an exception. An exception is anyone who does not have a total of 80 standard hours in a pay period. I have to group payroll records into two categories, 1) those hours that would be considered standard and 2) hours that either OT or adjustments. I am using a formula to categorize the records based on the primary key of the payroll record type (regular pay, training, vacation, sick, etc). The report looks something like this.

Group 1 – Person Name
Group 2- payroll category (All Standard)
3/1/09 8 hrs regular
3/2/09 8 hrs sick
3/3/09 8 hrs regular
3/4/09 8 hrs vacation
Group2 Total Hrs 32
Group 2-payroll category (Other)
3/1/09 1.5 hr Overtime
Group2 Total Hrs 1.5

I created a formula “Group2 Total” to summarize the hours for detail records in each payroll category, Sum(PayHours), and placed it in the group 2 footer. I can suppress the All Standard group, its detail records and the Group 1 name header by adding a formula to the section expert for group1, group2 and details under suppress to test if the Group2 Total = 80. That removes everything but the Other hours, they get orphaned in the report all by themselves.
I tried creating a second formula, TestGroup2Total, to test my summarized total for each group. If TestGroup2Total=80 then 1 else 0 I thought I would be able to add up the values for TestGroup2Total and any individual in the report who had a total greater than zero would not qualify as an exception. However, Crystal will not let me summarize this second formula. Last I tried incorporating the WhilePrintingRecords function into my second formula thinking that I needed to force this calculation to happen after the previous summary calculations. But I didn’t have any success with this. I would appreciate any suggestions.

 

[HowardHammerman]

One way to handle this is to create a sub-report that calculates the number of hours and then, using a shared variable, sends either the number or a boolean back to the main report. You can then create a formula using he shared variable to control the printing of the sections. Make sure you resent the variable when you go from employee to employee.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[dgillz]

Use a group selection formula:

Sum({hours},{employee})<>80

This will only display those employees without 80 hours.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[schaeff]

Thank you for your response, however a group selection formula as you suggest would evaluate all hours. So overtime and adjustments would get counted as contributing to the 80 hour total. I really need to exclude any of the hours from my comparison to an 80 pay period that are not standard.

 

[lbass]

Create a formula like this {@StdHours}:

if {table.payrollcategory} = "All Standard" then
{table.hours}

Then go to report->selection formula->GROUP and enter:

sum({@StdHours},{table.employee}) <> 80

-LB

 

[schaeff]

I want to Thank You lbass. Your soultion was just what I needed! It worked perfectly!