Display Total Employee Headcount in Parent Class and Department Using Analytics Workbook

Create a Workbook Pivot Table using a Dataset which includes a custom formula that maps sub class and sub department to its parent Class and Department.

First, create a Dataset:

1. Click Analytics 
2. Click Datasets
3. Click New Dataset
4. Click Employee
5. Second column from the left: Click and drag desired employee fields to create dataset criteria filter. See article 82543 Dataset Criteria Filters.
6. Upper left side of the page: Click Formulas
7. Click New Formula
8. Formula Field:
1. Formula Field Name: Enter Field Name
   Example: Parent Class
2. Choose Output Type: Select String
3. Formula box: Enter the following formula
   Note: The custom formula maps from the third level sub class to the first level or parent class.
• Case
  WHEN {class.parent#display} IS EMPTY THEN {class#display}
  WHEN {class.parent.parent#display} IS EMPTY THEN {class.parent#display}
  ELSE {class.parent.parent#display}
  END
4. Click Validate
5. Click Save
9. Click New Formula
10. Formula Field:
1. Formula Field Name: Enter Field Name
   Example: Parent Department
2. Choose Output Type: Select String
3. Formula box: Enter the following formula
   Note: The custom formula maps from the third level sub department to the first level or parent department.
• CASE
  WHEN {department.parent#display} IS EMPTY THEN {department#display}
  WHEN {department.parent.parent#display} IS EMPTY THEN {department.parent#display}
  ELSE {department.parent.parent#display}
  END
4. Click Validate
5. Click Save
11. Click and Drag Parent Class to the data grid
12. Click and Drag Parent Department to the data grid
13. Click Refresh icon
14. Click Save
15. Save Dataset As: Enter Dataset Title
    Example: Headcount per Class and Department
16. Click Save

Next, create a Workbook Pivot Table from the newly created Dataset:

1. Click Create New Workbook
2. Choose Visualization Type: Click Pivot
3. Dataset column: 
4. Click and drag Parent Class to Rows Drop fields
5. Click and drag Parent Department to Columns Drop fields
6. Click and drag Entity ID to Measures Drop fields
7. Click Refresh icon to see updates
8. To show total, click Sum icon
9. Totaling:
1. Placement Rows: Select desired placement
2. Click OK
10. Upper right side: Click Save
11. Save Workbook As: Enter Workbook Title
    Example: Headcount per Class and Department
12. Click Save