How can I subtotal on grouped fields?

[carynbo]

My report displays the number of people in 4 employee types, for 3 years. My question is: How can I display subtotals for TOTAL APPLICANTS and TOTAL EMPLOYEES (below)?

My report output and structure follows:

GH1...
Company Name
2000 2001 2002
GF2...
(1) Applicant Female 1 1 2
(2) Applicant Male 0 2 1
TOTAL APPLICANTS 1 3 3

(3) Employee Female 3 5 4
(4) Employee Male 3 3 5
TOTAL EMPLOYEES 6 8 9
GF1...
TOTAL APPLICANTS &
EMPLOYEES 7 11 12

The input file contains the headcount of 4 employee types by company for multiple years.

The report:
Group1 = Company
Group2 = Employee Type

Running Totals for each year display column values (GF2):
SUMMARY: Sum Headcount
EVALUATE: Year=2000
RESET: on change of field EMPLOYEE TYPE

Running Totals for each year gives the company total (GF1):
SUMMARY: Sum Headcount
EVALUATE: Year=2000
RESET: on change of field COMPANY NAME

I am under instructions to use a regular report, rather than a crosstab, to enhance formatting.

I've tried splitting GF2 and working with a formula that SUMS the headcount where EMPTYPE=1 or 2, for instance, but am getting nowhere.

Any direction would be immensely appreciated!

Thank you,

Caryn Giananti

 

[lbass]

Try displaying everything in the Group 1 Footer, using text boxes to display the column and row labels. Then adapt your employee type running totals so that the evaluation formula reads:

{Year} = 2000 and {Employee.Type} = 1//Repeat for the other three types and by year. Reset on change of {Employee.Type}.

For the subtotals, you can change the formula to read:

{Year} = 2000 and {Employee.Type} in [1,2]// or in [3,4]. Repeat for each year and reset on change of Group 1 (CompanyName). Or, you can create a formula which adds the component running totals together, e.g., {@subtotal2000Applicants}:

{#2000EmployeeType1}+{#2000EmployeeType2}

-LB

 

[carynbo]

LB,

Thank you very much for your reply. This is SO close but I'm not getting something.

Group1 groups by Company
Group2 groups by Employee Type

While the SubTotal Running Total is perfect on GF1, if I put EmpType Running totals on GF1, nothing displays (whether or not I keep Group 2). The only way I can get EmpType Running Totals to display is by placing them on GF2. However, the EmpType and SubTotal running totals must be in the same section, to display in order:

Emptype1
Emptype2
EmptypeSubtot1And2
Emptype3
Emptype4
EmptypeSubtot3And4

CompanyTotal

To be clear, you are suggesting placing all of the above running totals in GF1, correct?

Sorry for not getting this -- any clarity you can provide would help me greatly.

Caryn.

 

[lbass]

I'm sorry--I should have said reset on change of the CompName group for all the running totals.

Yes, I do mean that all results should be displayed in the Group 1 footer.

Please let me know if this still isn't working--it should work.

-LB

 

[carynbo]

LB,

Do you have any idea of the impact your suggestions make?! This solution worked perfectly - I am free to move on (until the next roadblock).

My thanks and gratitude to you for solving my puzzle!

HAGreatWeekend! Caryn