Data filters limit the data retrieved from the database by specific member selections for one or more dimensions. That is, data is returned from the database only if it meets the specified conditions set up in the filter. Data filters are applied on the server side (at the database), not on the client side.
Use the Data Filter dialog to do the following:
Filter data based on top n/bottom n values
Apply sorts to data/metadata
Apply show or hide criteria to display or suppress members from appearing in a query. For queries with multiple dimensions in the rows or column, Show/Hide can be used to create asymmetric reports (queries that include nested dimensions that differ by at least one member across an axis).
The top n feature retrieves the number of highest data values from the Results set sorted in specified order. The bottom n option retrieves the lowest number of lowest data values from the Results set sorted by specified order.
The Sorting feature sorts rows or columns of the query result set in ascending or descending numeric order. Sorting definitions are dynamic and are applied as the document is drilled, pivoted, and changed.
The Show/Hide Only feature includes or excludes members by member name, tuples, or data value criteria. It is an effective means of focusing analysis by values.
The Data Filter dialog box is opened by right clicking on a dimension in the data layout and selecting Data Filter. Data Filters are applied by way of MDX queries to the Essbase server, which processes them, not locally by Interactive Reporting.
For example, if the “Product” dimension is in the Rows pane of the data layout and you select the Data Filter option, the heading of the Data Filter dialog box reads: Rows Filter, and the member context appears accordingly. The Member context is the member selection that is required for the Data Filter criteria.
For each of the types of filters, the following is the respective context. Assume a report with “Product” members in the row pane and the “Year” member in the column pane of the data layout. The Data Filter is applied to rows:
Top/Bottom N—Because the “Product” members are filtered to show, for example, the “Top 5”, the criteria for the data values must be defined on a specific column. As a result, the Data Filter dialog box displays a “Selected Columns” section from a “Year” dimension column is selected for the member context (bottom left):
Sort—In a similar manner to Top/Bottom N, Sort requires the definition of the member context on the opposite axis. In the example, with “Product” dimension in the Rows pane of the data layout, the “Selected Columns” section from the “Year” dimension column is selected for the member context:
Show/Hide—when the Data Filter dialog box is opened for a dimension, Show/Hide applies to the dimension selected, where the member context are members from that dimension. For example, with the “Product” dimension in the Rows pane of the data layout, the Data Filter dialog box displays a list of “Product” dimension members from which you can show or hide data:
If two or more dimensions are in the columns or rows pane, the Data Filter dialog box displays all dimension combinations in the Member list pane, applicable. For example if the “Year” and “Scenario” are in the columns pane, a Rows Filter dialog shows all member combinations under Sort On:
Note: | Filters for tuples not present in the grid of the Data Filter dialog box are greyed out. |
To filter a selected number of top rows or columns or a selected number of bottom rows or columns:
In the data layout, right click on a dimension (either in the Rows or Columns pane) on which to set the filter and select Data Filter.
The Data Filter dialog box is displayed.
To retrieve the top n rows or columns of a dimension, select Top and in the text edit box. specify the number of rows or columns to retrieve. Enter a whole number only; decimal points and negative amounts are not accepted.
To retrieve the bottom n rows or columns of a dimension, select Bottom and specify the number of rows or columns to retrieve in the text edit box. Enter a whole number only; decimal points and negative amounts are not accepted.
From the Sort Order drop-down, select to sort rows or columns in Ascending or Descending order.
From the Sort on pane, select a column or row member from which to run the filter.
For Top/Bottom n and Sort data filters, an alternate axis member selection is required to apply the filter. For example, if you are applying a data filter against a column member, a row member must be selected.
To sort data in ascending or descending order:
In the data layout, right click on a dimension (either in a row or column) on which to set the filter and select Data Filter.
The Data Filter dialog box opens.
From Sort Order, select to sort rows or columns in Ascending or Descending order.
From Sort On, select a column or row member from which to run the filter.
For a Top/Bottom n data filter and Sort data filter, an alternate axis member selection is required to apply the filter. For example, if you are applying a data filter against a column member, a row member must be selected.
To show or hide a member or value in an asymmetric report:
In the data layout, right-click a dimension (in either the Rows or Columns) which to set the and select Data Filter.
The Data Filter dialog box opens.
From Select method, choose Hide or Show.
By default Show is selected.
To show or hide a member in the row label, select Member from the Where pane and from the Members pane, choose the members.
To show or hide dimension members when all dimension member values satisfy a condition, from the Where pane, select Values from the Where pane, and from the Members pane, choose the values.
Set a condition on a value by selecting Condition and indicating a comparative condition on the Set Condition dialog.
Click
to add the column member to the Selected pane.The selected member is displayed in the Selected pane as: Show or Hide (“member_name”). The selected value condition is displayed in the Selected pane as Show or Hide (“member_name”): val condition (“value”).