You can filter your hierarchies so your report only displays members that satisfy a certain condition.
- Go to the member selector of the hierarchy you wish to filter. Under the Advanced tab, select the dropdown and select filter result, or select the filter icon.
- Select which measure to filter by:
- Current measure: Use the measure currently used in the grid filters
- This measure: Manually define a measure to use
- Measure in slice: Filter by a certain slice, for example, Sales amount in a particular period.
- Select what filtering to apply (greater than, equal to, etc.) - this can come from an Excel range.
- Select the value to compare with:
- This value: Specify a value, or use a value in an Excel cell
- Current measure: Use the measure currently used in the grid filters
- This measure: Manually define a measure to use
- This selection: Filter by a value of a certain slice
- Is null: Use this option to remove members where the specified measure value is null
Example of filtering (hierarchy mode)
Let's say we want to find products that sold more than 250 units in 2022, Quarter 3. We want to show the sales numbers for those subcategories during 2022 and its subsequent quarters.
- Create a grid with Date.Calendar on columns and Product Categories on rows.
- We want to filter what products are shown. Select the Product Categories hierarchy so its details appear in the right panel. We will choose to show products at the Product level.
- Select the Advanced tab and, on the Members dropdown, choose Filter result.
- Select the Date.Calendar edit control in the grid to change its selection to the desired member, such as 2022, Quarter 3.
- Select the This measure radio button and select Order Quantity as the desired measure.
- Change the Operation to >, and type 25 in the edit field on the right.
- Select OK. The new filter is displayed in the advanced selections tab.
- Select OK again to run the Report – the Grid shows the members that fit our criteria:
