Exercise 5: Stacked Dimensions
In this exercise you will create a report showing your division’s planned Non-Compensation Expenses in thousands by Fund for the current fiscal year and next year. When you are finished, your report will look similar to this sample:
Exercise
1. Create a new Excel file and begin to create an ad hoc query from the CalRptg database. Review the Accessing Smart View and Essbase Ad Hoc Query chapters if needed.
2. With your cursor on cell A3, open the Member Selection dialog box for Account and select Total Non Compensation (or you can type Total Non Compensation in cell A3).
3. Expand the width of column A as needed so you can see the accounts.
4. Insert a column between columns A and B.
5. Type Fund in cell B3 and then delete column G so that the Fund dimension appears only once.
6. In cells C1-E1, type the following:
- 2023-24
- Forecast
- Working
7. Enter your entity in F1.
8. In cell C2, type YearTotal.
9. With your cursor on cell B3, open the Member Selection dialog box for Fund and make these selections:
10. Click the blue > to move the selected Funds to the right pane.
11. If Fund is in the selection pane, remove it before clicking OK.
12. Your sheet should look like this:
13. Refresh the data in the sheet.
14. Open the Member Selection dialog box for Time_Series and select Periodic ($000s); you’ll need to expand TS_InThousands to see it. If Time_Series is in the selection pane, remove it before clicking OK.
15. Refresh the data in the sheet.
16. The next steps are to move the Year, Scenario, and Version dimensions from the POV onto the grid. Select the year (2023-24) in the POV and click the Pivot button.
17. Notice that the year was moved to the rows, not the columns. Select the year and click the Pivot icon again to move the Year dimension to the columns. You should have only 2023-24 in the columns. If Year also appears, delete the contents of cells D2.
18. Repeat these steps for the Scenario and Version dimensions to move them to the columns. Your sheet should look like this, except the numbers may be different if you have a different org selected:
19. Select Period in cell C5 and click the Member Selection icon.
20. In the Member Selection dialog box, expand YearTotal and the quarters, and then select YearTotal and each of the months as shown.
21. Remove Period from the selection pane if it appears there. Click the Fill Horizontally icon and click OK.
Result: YearTotal and the months of the year are spread across the columns.
22. Double-click on Total Non Compensation in cell A6 to zoom in one level.
23. Refresh the data.
24. Complete your report by adjusting column widths and formatting cells as desired.
25. Save your file as Exercise 5.xlsx
Example of completed Exercise 5
Return to course: Cascading Reports