Exercise 7: Ad Hoc Query with CalPlan
1. Create a new Excel sheet.
2. From the Smart View Panel, connect to Essbase and the CalPlan database.
3. Create an ad hoc analysis. Your screen should look like the example below for CalPlan. Smart View displays #NoAccess until you change the Entity dimension to your entity.
CalPlan example
CalRptg example
4. Insert a column between columns A and B.
5. Enter Chart1 in cell B3.
6. Delete column D so that Chart1 exists only once in the sheet.
7. Enter these members in the dimensions:
Fund: Current Funds
Entity: Enter a DeptID in your org
Year: FY25
Scenario: Operating Budget
Version: Working
Account: Total Non-Compensation
Period: YearTotal
8. Refresh to fetch data.
9. Now you can Zoom in on Chart1 to see where you have non-comp expenses planned. Zoom In to all levels.
10. Now you’ll turn on suppression to limit the display to only the rows with data. On the Smart View ribbon, select the Options icon.
11. Select Data Options on the left panel and then check the box for No Data / Missing in the Suppress Rows section of the dialog box. Select OK.
12. Refresh the data.
13. Now you’ll modify the Period dimension to show the months in addition to the YearTotal. Open the Member Selection dialog box and select each of the months. A quick way to select the months is to check the box for YearTotal and then select Check Base Members from the drop-down menu on the Member Selection icon next to the Filter (funnel) icon. Be sure to check the Fill Horizontally icon at the bottom of the dialog box before clicking OK
14. Refresh the data.
15. Add Excel formatting as desired.
16. Save your file for future use. You can update it whenever you enter data into CalPlan.