Working with Excel Filters on Ad Hoc Sheets

Data source types: All supported Oracle Smart View for Office providers, cloud and on-premise, ad hoc only

Excel filters are retained when performing ad hoc in cases where the ad hoc operation results in the same set of column dimensions. For example, set filters on the members of the Measures dimension as follows:

Initial ad hoc query with Year in the row dimension. The column dimension members are Profit|Loss, Profit, Inventory, Ratios, and Measures. Each column dimension member has an Excel filter defined.

If you zoom in on the row dimension, Year, the result set retains the Excel filters that were set in the column dimensions because the column dimensions and members didn’t change.

Ad hoc query with the Year row dimension expanded to show the Qtr1, Qtr2, Qtr3, and Qtr4 row dimension members. Each column dimension member (Profit|Loss, Profit, Inventory, Ratios, and Measures) still contains the Excel filter.

However, if you zoom in on a column dimension member, such as Inventory, the set of columns in the result set changes, causing you to lose the Excel filters.

Ad hoc query with the Inventory column dimension expanded, causing the result set to change. In addition to the column dimension members (Profit|Loss, Profit, Inventory Ratios, and Measures), we also have the Inventory dimension members, Opening Inventory, Additions, and Ending Inventory, as column dimensions. The Excel filters are lost.

Guidelines

  • Excel filters are retained for most ad hoc operations, such as Zoom In, Zoom Out, Remove Only, and Keep Only, Undo, and Redo, as long as the same set of column dimensions appear in the result set.

  • Excel filters are also retained after a Submit or a Refresh.

  • Excel filters are not retained for Pivot or Pivot to POV operations.

  • Excel filter retention is not supported on multiple-grid sheets. For example, after creating filters in one grid, then moving to a second grid and creating filters on it, the filters on the first grid will be lost.

    However, there may be some cases where the filters are retained. For example, if two adjacent grids both start on the same row, then the filters may be retained. Because of these variations, Oracle cannot assure that Excel filters will be retained on multiple-grid sheets.