This article demonstrates how to re-create the GL Balances view of the of the General Ledger (GL) Journal Cube. The GL Journal Cube, available in the Data Warehouse, can be used to review posted transactions over a given spending period. It provides actuals information at the summary and detail levels. It downloads as an Excel Cube with Pivot Table functionality.
In this article, you will learn how to setup the balances view of the GL Journal Cube in Excel.
With the GL Journal Cube open in Excel, click the New Sheet button.
A new sheet is displayed.
NOTE: Cell A1 should be selected.
Click the Insert tab.
The Insert menu ribbon is displayed.
Click the PivotTable button.
The Create Pivot Table popup is displayed.
Click the Use an external data source option.
Click the Choose Connection... button.
The Existing Connections popup is displayed.
Click the QA_JOURNAL_TRANSACTIONS_CUBE field.
Click the Open button.
Click the OK button.
A blank PivotTable is inserted. To begin setting up the view you will need to select the appropriate PivotTable Fields.
Click and drag the Budget Ref option in the PivotTable Fields menu into the Filters box.
The Budget Ref column is displayed.
Click and drag the Ledger Descr option in the PivotTable Fields menu into the Filters box.
The Ledger Descr column is displayed.
Click and drag the Dept ID Descr option in the PivotTable Fields menu into the Filters box.
The Dept ID Descr column is displayed.
Click and drag the UGAFUND option in the PivotTable Fields menu into the Filters box.
Click the Ledger Descr drop-down menu.
Click the All tree item.
Click the Actuals tree item.
Click the Budget Ref drop-down menu.
Click the 2021 tree item.
Click the More Fields option under FUND > UGAFUND in the PivotTable Fields menu.
Click and drag the Fund Code Descr option in the PivotTable Fields menu to the Rows box.
Click and drag the UGAACCTTREE option in the PivotTable Fields menu to the Rows box.
The Row Labels information is displayed.
Click and drag the A mount option in the PivotTable Fields menu to the Values box.
To view by account tree level, you will need to adjust the cell display settings.
Right click any row within the table.
Example show here: 000000 - Accounts 10500 - Tuition cell.
The 000000 - Accounts cell context menu is displayed.
Click the Field Settings... option.
Click the Display tab.
Click the Classic PivotTable layout option.
To view the UGAACCTTREE Level02 in formation, click the appropriate Accounts cell.
Example shown here: 100000 - RI State Appropriations cell.
The UGAACCTTREE Level02 information is displayed.
To view Level03 information, click the appropriate Level02 cell.
Example shown here: 100000 - Assets cell.
To hide the Level01 data,
right-click a cell in the appropriate column.
Example shown here: 000000 - Accounts cell.
To deselect, click the UGAACCTTREE Level01 option.
The Level01 column is hidden.
To hide the Level02 data, right click the appropriate cell within the column.
To deselect, click the UGAACCTTREE Level02 option.
The Level02 column is hidden.
To hide the PivotTable menu, click a cell outside the table.
The Balances view of the Gl Journal Cube is now displayed.
NOTE: If necessary, zoom out to show all the columns.
Additional Information:
To access the General Ledger Journal Cube, you must be signed into the VPN using 02 Restricted access. The OIR homepage can be accessed at https://oir.uga.edu/ and you can reach the report using the Operational Reports link. You can also access the report at reports.uga.edu.