Excel Functionality
You can enhance your Smart View queries by using Excel functionality such as formulas and formatting to produce reports with greater analytical information and presentation quality. There are a number of ways to do this:
- Format the cells in the Essbase query
- Add rows or columns to do additional calculations
- Create another sheet and reference the cells in the Essbase query
Format Cells in Essbase Query
If you set your Smart View Formatting Options to Use Excel Formatting, Essbase will retain your Excel formatting when you refresh. On a larger query, this may impact performance. If that is an issue, you may decide to Use Cell Styles (no formatting) and reference the Essbase query data on another sheet where you use Excel formatting. See below for details.
Add Excel Formulas
You can add Excel formulas in rows and columns outside of the query POV and grid. This can be done to the left or the right, above or below. Open 5YearTrend.xlsx to see an example.
Reference Cells in Essbase Query
If your report has a large number of cells to retrieve from the database, performance may be enhanced by having one sheet with the unformatted data from the CalRptg database and another sheet that references those cells with your desired formatting. Open SRECNA_by_Fund.xlsx to see an example.
Mixing Smart View and Excel Functionality
In ad hoc grids, you can insert calculating and non-calculating columns and rows within or outside the grid. Inserted rows and columns, which may contain formulas, text, or Excel comments, are retained when you refresh or zoom in.
Always refresh the grid before inserting rows or columns.
Butterfly Reports: Typically, Smart View grids consist of member names on rows above and columns on the left of the data grid. Using the range retrieval capabilities of worksheets enabled for multiple grids, you can create grids with different layouts, such as a butterfly report with a column of members between two columns of data cells. To see an example, search for "butterfly" in the Oracle Online User Guide.
Practice – Exercise 4
Complete Exercise 4 and email your completed file to the instructor no later than two days before the first class meeting.
End of Self-Study for Session 1
This concludes the self-study portion of Smart View Ad Hoc for CalRptg in preparation for the first group session. Please be sure to join the scheduled group session to review the self-study and work on a case study.