Suppress Group based on condition in Group below main Group 1

[schaeff]

I am working in CR XI writing a report against two tables in Excel 2007. My two tables are data extracted from a payroll system and a scheduling system. The tables are linked on Employee ID. The goal is to add up time card records for vacation, sick and personal leave for each employee and compare them to the accrued leave balance for the employee pulled from the payroll system. If any employee has time card records that exceed their accrued balance they need to show on the report.

My report has two groups 1) Employee Name, 2) Leave type (vac, sick or personal). Hours are totaled for each leave type. Here is sample data.

GRP 1: Doe,John ID 1234 Vac: 200.00 Sick: 45.33 Personal: 16.00
GRP 2: Vacation
6-15-2011 10.00
6-16-2011 10.00
total: 20.00 OK

Sick
6-20-2011 8.00
total: 8.00 OK

Personal
6-28-2011 10.00
6-30-2011 10.00
total: 20.00 SHORT

I can flag each type of leave as being OK or SHORT using the following formula.
If CStr(GroupName(leave_time_cards.HRS-NO) = "3075" then
If #Leave hrs total <= payroll_leave_bal.Vacation then
"OK"
else
"SHORT"
else
If CStr(GroupName(leave_time_cards.HRS-NO) = "3080" then
If #Leave hrs total <= payroll_leave_bal.Sick then
"OK"
else
"SHORT"
else
If CStr(GroupName(leave_time_cards.HRS-NO) = "3078" then
If #Leave hrs total <= payroll_leave_bal.Personal then
"OK"
else
"SHORT"

I am having trouble suppressing the employees who have no exceptions (meaning all of their leave time card records are less than their accured balance so each group flag returns OK). I tried making my flag return a number, 0 for OK or 1 for SHORT and then add them. The idea was to include only those employees who had an exception value >0 and use a Group select, but Crystal did not let me summarize the flag field.

I can suppress the GRP 2 records using the section expert and using a formula, however that leaves GRP 1 records. In addition, when I do hit an employee with an exception I need all of the leave types (vacation, sick, personal) to show with their respective totals so that if there is a shortage in one leave type the payroll clerk will see all the time card records so they can determine if there is sufficient leave banked in another leave type to cover the shortage. When I suppress GRP 2 records based on my flag, I get only the leave type visible that was the problem, not all the leave types that may exist. Any suggestions on how to suppress the entire employee (both GRP 1 and GRP 2 records) who have no exceptions would be appreciated.

 

[lbass]

Please explain why you are using running totals, as this is part of the problem. How are the rts set up?

Also, are the values you show in the group header just detail fields that have been dragged into the group header? Or are they formulas? If so, what is the content?

-LB

 

[schaeff]

I was using running totals that are created through the Field Exployer. I was not sure how to create a summary total here since I have a group(leave type) beneath a group(employee).

The values in the group header are just fields dragged to the header for convenience. They are not formulas, they are part of the data coming from the payroll table. The table values are:

Payroll table fields:
Employee ID
Last Name
First Name
Middle Initial
Vacation bal
Sick bal
Personal bal

Time card table fields are:
Employee ID
Date
Hours
Hrs-No (a code that indicates vac, sick or personal leave)

 

[lbass]

Please explain the rt setup so I can see if they are necessary.

-LB

 

[schaeff]

I used the following fields within the Field Explorer to create the running totals:

Field to Summarize: leave_time_cards.HRS
Type of Summary: sum

Evaluate: For each record (radio button)
Reset: On each change of group (radio button)
Group: GRP#2 Leave_time_cards.HRS-NO

The GRP2 field is a payroll code (number value) that relates to Sick, Vacation, or Personnal leave, so I don't display it in the report. Instead I display the description associated with the code (Vacation, Sick, or Personnal).

 

[lbass]

Create three separate formulas like this:

//{@vac}:
if leave_time_cards.HRS-NO = 3075 then
leave_time_cards.HRS

//{@sick}:
if leave_time_cards.HRS-NO = 3080 then
leave_time_cards.HRS

//{@pers}:
if leave_time_cards.HRS-NO = 3078 then
leave_time_cards.HRS

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

sum({@vac},{table.employeename}) >
{payroll_leave_bal.Vacation} or
sum({@sick},{table.employeename}) >
{payroll_leave_bal.Sick} or
sum({@pers},{table.employeename}) >
{payroll_leave_bal.personal}

This should show all records for any employee who exceeds in at least one of the categories.

-LB

 

[schaeff]

Thank you, that works perfectly! Your time is very much appreciated.