Exercise 5

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 5 completed

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. 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.

Fund dimension moved to rows

6. Enter your entity in F1.

7. Select cell B3 and open the Member Selection dialog box for Fund. Expand Total Funds, Current Funds, Unrestricted, and Restricted so you can select:

  • Unrestricted Funds
  • Designated
  • Restricted Gift Funds
  • Restricted Endowments and FFEs Funds
  • Contracts and Grants

Fund member selection dialog box with 5 fund groups selected                                                                                                                                      

8. If the dimension name, Fund, is in the selection pane, remove it before selecting OK.

9. Insert three rows between rows 1 and 2. Period will now be in row 5.

10. Enter the following in cells C2:C4

  • 2024-25
  • Forecast
  • Working

Ad hoc with year, scenario, and version entered twice

11. To ensure that each dimension is entered only once, clear the contents of cells C1:E1.

12. Select cell K1 and then open the Member Selection dialog box for Time_Series. 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.

Member selection for Time Series

13. Refresh the data in the sheet.

14. Select Period in cell C5 and click the Member Selection button.

15. In the Member Selection dialog box, check the box for YearTotal on the left. Then you can open the drop-down list on the Options button and select Check Base Members

Check Base Members highlighted on Member Selection Options button drop-down

16. If Period appears in the selection pane on the right, remove it. Be sure to select the Fill Horizontal button at the bottom of the window before selecting OK. 

Member Selection dialog box with Fill Horizontal button highlighted

17. In cell A6, double-click on Total Non Compensation to zoom in one level.

18. Refresh the data.

19. Complete your report by adjusting column widths and formatting cells as desired.

20. Save your file as Exercise 5.xlsx

Next Topic: Cascading Reports