Running the Position Funding Cube by Financial Department

Summary

In this article, you will learn the steps to create a Position Funding Cube to display payroll information by employee and Financial Department.

Body

   Overview   

You can run the Position Funding Cube by employee and by Financial Department, rather than by HR Department. This may be helpful if you have positions across many different HR departments.

   Instructions   

  1. Step 1: Navigate to the Position Funding Cube by clicking All UGA Faculty and Staff Reports > Financial-HCM > Position Funding Cube. Click the Position_Funding_Cube.xslx link to open the Excel file.
  2. Step 2:  The Postion_Funding_Cube.xlsx file download box is displayed. Click OK.
  3. Step 3: The Position_Funding_Cube Excel Workbook is displayed. To make the appropriate changes to the file, click the Enable Editing button.
  4. Step 4: To gain access to the external data connection, click the Enable Content button.
  5. Step 5: The Postion_Funding_Cube data is now editable. To refresh the data in the Pivot Table, click the Analyze tab.
  6. Step 6: Click Refresh.
  7. Step 7: In the PivotTable Fields menu, under DEPARTMENT_UGADEPTBUDGRPT, click to check the box for UGADEPTBUDGRPT Dept ID Des... option. Then, click and drag the field to the FILTERS window.
  8. Step 8: Click the UGADEPTBUDGRP Dept ID Descr drop-down menu.
  9. Step 9: To be able to select multiple departments, click the Select Multiple Items checkbox.
  10. Step 10: To open the data tree to locate the appropriate department(s), click the [+] next to ALL.
  11. Step 11: The data tree is expanded.
  12. Step 12: Click the checkbox next to the department or level that you want to view.
  13. Step 13: Click OK.
  14. Step 14: Click OK to populate the desired pivot table data fields, the PivotTable Fields menu will be hidden from the worksheet. To re-display the PivotTable Fields menu, click on a cell in the report that displays PivotTable data
  15. Step 15: The PivotTable Fields menu is displayed again on the right side of your screen. In the Row Labels (A1) cell, click the Filter button.
  16. Step 16: The Fiscal Year pop-up menu is displayed. On this page, you need to deselct the pre-set 2019 filter. Click the box next to Select All to select all Fiscal Years.
  17. Step 17: Click OK.
  18. Step 18: Since you want to see position funding regardless of HR Department, click and drag the DEPTSECURITY data item up to the PivotTable Field menu and release the left mouse button.
  19. Step 19: If you want to break figures down by department, select Dept ID Descr and drag it to the FILTERS window between the ACCOUNTING_DATE and GL Account ID Descr fields.
  20. Step 20: If you want to break the figures down by employee, drag Name Emplid Badge No (under Employee > More Fields) to the ROWS window, between Dept ID Descr and GL Account ID Desc.
  21. Step 21: The data now displays payroll information by employee and financial department

In this article, you will learn the steps to create a Position Funding Cube to display payroll information by employee and Financial Department.

This article starts from within the Data Warehouse. You will need to connect to the VPN with 02 Restricted access first. then, locate the report on reports.uga.edu under Human Capital Management > Payroll > Position Funding Cube . Click the Position funding Cube link and log in when prompted with your MyID and password.

1. Step 1

step image

First log into UGA Data Warehouse

Navigate to the Position Funding Cube by clicking All UGA Faculty and Staff Reports > Financial-HCM > Position Funding Cube .

Click the Position_Funding_Cube.xslx link to open the Excel file.

2. Step 2

step image

The Postion_Funding_Cube.xlsx file download box is displayed. On this page, you have the choice to open the file directly in Excel or save the file to your computer.

Note: If electing to save the file to your computer, you must be signed in and on the VPN with 02 Restricted access in order for it to function properly.  

Click OK.

3. Step 3

step image

The Position_Funding_Cube Excel Workbook is displayed.

To make the appropriate changes to the file, click the Enable Editing button.

4. Step 4

step image

To gain access to the external data connection, click the Enable Content button.

5. Step 5

step image

The Postion_Funding_Cube data is now editable.

Next, you will begin setting up the Pivot Table fields to display the desired data in your workbook. 

To refresh the data in the Pivot Table, click the Analyze tab.

6. Step 6

step image

Click Refresh.

7. Step 7

step image

The PivotTable Fields menu is displayed. On this page, you will begin selecting the data fields you want to display in the workbook. In this example, you want to display employees by financial department. 

In the PivotTable Fields menu, under DEPARTMENT_UGADEPTBUDGRPT , click to check the box for UGADEPTBUDGRPT Dept ID Des... option. Then, click and drag the field to the FILTERS window.

8. Step 8

step image

The selected field is displayed as a filter in row 2 of the workbook. This filter allows you to view data organized in a departmental tree/hierarchy.

Click the UGADEPTBUDGRP Dept ID Descr drop-down menu.

9. Step 9

step image

The filter tool is displayed. On this page, you can drill into the data tree by checking the appropriate boxes.

To be able to select multiple departments, click the Select Multiple Items checkbox.

10. Step 10

step image

To open the data tree to locate the appropriate department(s), click the [+] next to ALL .

11. Step 11

step image

The data tree is expanded.  

Example shown here: Plus box next to the 2020-2075 tree item to view current FY2020 department information.

12. Step 12

step image

Continue to drill into the data tree until you reach the appropriate department by clicking the plus boxes next to the appropriate level(s).

Click the checkbox next to the department or level that you want to view.   

Example shown here: box next to the 27000000 - Graduate School  tree item.

13. Step 13

step image

Click OK.

14. Step 14

step image

Now the Position Funding Cube is set to only show funding for the selected Department ID(s).

Note: Once you click OK to populate the desired pivot table data fields, the PivotTable Fields menu will be hidden from the worksheet. To re-display the PivotTable Fields menu, click on a cell in the report that displays PivotTable data.

Example shown here: cell B2. . 

15. Step 15

step image

The PivotTable Fields menu is displayed again on the right side of your screen.

In the Row Labels (A1) cell , click the Filter button.

16. Step 16

step image

The Fiscal Year pop-up menu is displayed. On this page, you need to deselct the pre-set 2019 filter.

Click the box next to Select All to select all Fiscal Years.

17. Step 17

step image

Click OK.

18. Step 18

step image

The re-filtered data fields are displayed. You can drill down to each pay period by clicking the [+] next to any of the fiscal years shown in column A.

Since you want to see position funding regardless of HR Department, click and drag the DEPTSECURITY data item up to the PivotTable Field menu and release the left mouse button.

19. Step 19

step image

The data now displays position funding by financial department.

If you want to break figures down by department, select Dept ID Descr and drag it to the FILTERS window between the ACCOUNTING_DATE and GL Account ID Descr fields.

20. Step 20

step image

If you want to break the figures down by employee, drag Name Emplid Badge No (under Employee > More Fields) to the ROWS window, between Dept ID Descr and GL Account ID Desc.

21. Step 21

step image

The data now displays payroll information by employee and financial department  

Additional Information:

The Data Warehouse contains the Position Funding Cube, which provides payroll expense details and encumbrance amounts by chartstring by employee. The report can be used as a personal services detail or payroll cost report. The cube includes Payroll Actuals from 7/1/2018-12/31/2018 data that has been added from legacy systems.

Note: Currently, you must drill through on the Budget Amount of Encumbrance Amount fields. Drill through is not available on calculated fields like Actual Amount. However, drill through returns all values for the entire row related to Budget, Encumbrance, and Actuals.

Data in the Data Warehouse gets refreshed nightly. A best practice is to review filters on a regular basis to ensure you are pulling any new data your existing filters may exclude. Occasionally, new data elements are added which may create users impacts, requiring adjustmnts to repopulate data.

Tutorial Resources

Quick Start to the Position Funding Cube

Cube Field Definitions Quick Reference Guide

How to Use a Cube

Lynda.com Training Resources

 

Details

Details

Article ID: 149567
Created
Sat 9/24/22 11:17 PM
Modified
Fri 1/5/24 5:09 PM