Smart View Ad Hoc - Exercise 7 Step-by-Step

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. There are three dimensions in the CalRptg database that do not exist in the CalPlan database: Chart2, Program, and Time Series.

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:

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, click the Options button.

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. Click 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 button next to the Filter (funnel) button. Be sure to check the Fill Horizontally button at the bottom of the dialog box before clicking OK.

14. Refresh the data.

15. Add Excel formatting if desired.

16. Save your file for future use. You can update it whenever you enter data into CalPlan.

Extra Credit

Use the Cascade functionality to create additional sheets for other DeptIDs.

End of Self-Study

This concludes the self-study portion of Smart View Ad Hoc for CalRptg. Please be sure to join the scheduled group session to review the self-study and work on a case study.