What cool things have you done with SuiteAnalytics Workbook?

With some help from support, we were able to (partially) use workbooks for EEO1 reporting this year with the addition of a salary range in the report.

To build this workbook, follow the steps below:

1. Click on the Analytics tab.
2. Click New Workbook.
3. Search for and select the Record Type “Paycheck”.
4. Go to the Criteria tab.
5. Under Record, click the link icon next to Paycheck.
6. Click “Employee”.
7. Click and drag Location to add the condition.
8. In the Filter: Location popup window, double click Corporate Headquarters.
9. Click OK.
10. Under Paycheck, click and drag Period Ending to add the condition.
11. In the dropdown, select “on”, and enter the date 12/22/2018.
12. Click OK.
13. Click the link icon next to Paycheck.
14. Select “Earnings”.
15. Under Earnings, click and drag Earnings to add the condition.
16. In the Filter: Earnings popup window, double click Salary.
17. Click OK.
18. Go to the Data tab.
19. Click the link icon next to Paycheck.
20. Select Employee.
21. Under Employee, click and drag Gender and Ethnicity to the data chart.
22. Click the link icon next to Employee.
23. Select Job.
24. Under Job, click and drag EE01 Classification to the data chart.
25. Go to the Formula Fields subtab.
26. Click New Formula.
27. Enter the Formula Field Name “Salary Band”.
28. Choose the Output Type String.
29. Copy and paste the formula below into the Formula field:
    CASE WHEN {payearn.rate}*26 < 19239 THEN ‘1. $19,239 and under’
    WHEN {payearn.rate}*26 < 24439 THEN ‘2. $19,240 – $24,439’
    WHEN {payearn.rate}*26 < 30679 THEN ‘3. $24,440 – $30,679’
    WHEN {payearn.rate}*26 < 38999 THEN ‘4. $30,680 – $38,999’
    WHEN {payearn.rate}*26 < 49919 THEN ‘5. $39,000 – $49,919’
    WHEN {payearn.rate}*26 < 62919 THEN ‘6. $49,920 – $62,919’
    WHEN {payearn.rate}*26 < 80079 THEN ‘7. $62,920 – $80,079’
    WHEN {payearn.rate}*26 < 101919 THEN ‘8. $80,080 – $101,919’
    WHEN {payearn.rate}*26 < 128959 THEN ‘9. $101,920 – $128,959′
    WHEN {payearn.rate}*26 < 163799 THEN ’10. $128,960 – $163,799′
    WHEN {payearn.rate}*26 < 207999 THEN ’11. $163,800 – $207,999′
    WHEN {payearn.rate}*26 > 207999 THEN ’12. $208,000 and over’
    END

    This formula will annualize the pay from the selected pay period based on what was on that paycheck, and categorize the employees into salary bands.

30. Click Apply.
31. Click and drag the Salary Band formula to the data chart.
32. If you would like to add a formula that shows the salary at the time of this paycheck, click New Formula. This is optional, but will help validate the salary bands. If you choose not to do this, you can skip to step 38.
33. Enter the Formula Field Name “Salary”.
34. Choose the Output Type “Float”.
35. Copy and paste the following formula: {payearn.rate}*26
36. Click Apply.
37. Click and drag the Salary formula to the data chart.
38. Click the “Add Pivot +” tab.
39. Click and drag EE01 Classification to Rows.
40. Click and drag Salary Band to Rows, below EE01 Classification.
41. Click and drag Gender to Columns.
42. Click and drag Ethnicity to Columns, below Gender.
43. Click and drag Employee to Measures.
44. Click the sum icon Σ just above Fields.
45. Check the Set Individually Box.
46. Change EE01 Classification to Bottom.
47. Click OK.
48. Click the blue refresh icon in the top right to load the pivot table.
49. Click the menu icon in the top left to save the workbook.

The pivot table generated here will show the information you requested for the EE01 form.