Optimizing Ad-Hoc Reporting in Oracle Hyperion Smart View via PBCS Connection

User Experience Level: New/Beginner
Author: Noal Neal, Key Performance Ideas


Traditional Oracle Hyperion Planning allows for Smart View management of metadata and data through grid and reporting frameworks. These frameworks are accessible through Smart View panel connections. For example, consider a Smart View Planning connection to the data source for the Planning application PLNtstc1:

Additionally, on-premise Planning allows for ad-hoc reporting to be easily done through Smart View via an Essbase database connection. This option allows for the user to conveniently design an “ad-hoc”, functional reporting grid by selecting dimension members of choice in the format desired, and then retrieving/submitting data defined by the established intersections. For example:

By connecting to the “Main” database for the app PLNtstc1 above via Essbase connection, I can easily select dimension members in the grid above and work on reporting as needed.

New Technology

As of Summer 2014, Oracle Planning and Budgeting Cloud Service (PBCS) does not offer direct Smart View database connectivity via an Essbase connection. Consider the same Planning app, PLNtstc1, but in PBCS. Fewer connections are offered in Smart View:

As you can see above, there is no direct Essbase connection for ad-hoc reporting – which is not the case for traditional on-premise Planning. This is probably to be expected because PBCS is a cloud service platform; it only allows for one Planning app per environment, and restricts most backend server and database access for users.

Process Optimization
Fortunately, ad-hoc reporting can be done in Smart View via the Planning connection for PBCS. There are some characteristics that are unique for ad-hoc reporting processes in PBCS that differ from those processes in on-premise planning. In this section, we’ll show you how to optimize ad-hoc reporting in Smart View by leveraging Smart View options and Excel formatting.

First, connect to the application database via the Planning connection. Consider PLNtstc1 again, and a connection to the “Plan1” database:

Click on the database of interest (“Plan1”) and then “Ad hoc analysis”. The grid containing the cube dimensions is rendered:

The database “Plan1” currently has no numeric data in it. Such is the case for any new database. Default settings prohibit some manipulation of grids with no data via suppression options. Suppose you wanted to setup a grid for a new database with no data so that you could push data through that grid. To do so, you must first change the settings in Smart View Options.

To access Smart View Options, go to the Excel ribbon » “Smart View” » “Options":

In “Data Options”, be sure to de-select “Navigate Without Data” and “Suppress Missing Blocks”. Also, leave “Suppress Rows” and “Suppress Columns” items de-selected:

De-selecting these suppression options will allow for you to easily manipulate your grid and select members – even when there is no data in the database.

Now suppose you want to remove Smart View default formatting for the grid and use your own formatting as set in Excel. You can make these changes by following this process: “Options” »“Formatting” »“Use Excel Formatting” (see below). It could also be prudent to de-select “Adjust Column width” if it is already selected so as to prevent the system from automatically adjusting your column width upon refresh.

Every time you setup a grid through your Planning connection on a new Excel workbook tab, the setting changes you make (discussed in part above) are erased, and the defaults are restored. To retain your settings and prevent the defaults from restoring, click on the drop-down button next to “OK” in the Smart View options, and click “Save as Default Options”:

Suppose aliases keep displaying upon refresh in your ad-hoc grid, despite the fact that “Member Name Only” is selected for “Member Name Display” in “Member Options” (in “Options”).

If this is the case, try clicking on “Change Alias” in the “Planning Ad Hoc” tab in the Excel ribbon – select “None” and then refresh the grid to render the member names instead of the aliases:

At this point, you are essentially all setup to prepare an ad-hoc reporting grid via a PBCS connection, capable of facilitating the retrieval, and submission of, numeric data.





Kscope 2018