You can filter a report by searching for members in a hierarchy.
Select the advanced search toolbar item in the hierarchy editor to open the search dialog. From here you can search for members and select individual or all search results to be inserted into the report.
You can also search from the Advanced tab of the hierarchy editor. Search text can then be driven from an excel range.
Search types
You can also search for members where the caption or property doesn't contain a particular string.
| Type | Description |
| Begins with | Begins with the given value |
| Exact match | Matches the given value exactly |
| Contains | Contains the given value |
| Ends with | Ends with the given value |
| Not Begins with | Doesn't begin with the given value |
| Not Exact match | Doesn't exactly match the given value |
| Not Contains | Doesn't contain the given value |
| Not Ends with | Doesn't end with the given value |
Search locations
| Type | Description |
| Begins with | Begins with the given value |
| Exact match | Matches the given value exactly |
| Contains | Contains the given value |
| Ends with | Ends with the given value |
| Not Begins with | Doesn't begin with the given value |
| Not Exact match | Doesn't exactly match the given value |
| Not Contains | Doesn't contain the given value |
| Not Ends with | Doesn't end with the given value |
Case sensitivity
You can search for members:
| Case | Description |
| Ignore case | The case of the members' text isn't taken into account. |
| Match case | The search differentiates text by case. |
| Use database default | Whatever the cube usually does. |
Multiple searches
You can search for and pick multiple members in one go. Select the required members and select Pick before doing another search. When you select OK all the picked members are added to the selections.
Search from an Excel range
Under the Advanced tab, select Member search from the toolbar or dropdown menu.
You can now use the range picker to select the cell that contains the search text, along with the search criteria as described above.
If you change the value of the search cell, the grid updates accordingly.
For this dynamic searching to work on the web, you will need to first unlock this cell using standard Excel formatting. It will then be possible to enter a new value in to this cell in the web application. The grid will be refreshed after submitting changes by clicking the option in the toolbar (alternatively, you could use an XL3Link or enable submitting changes on enter in the workbook options).
Example
The report below shows a simple grid with Geography on rows and Fiscal Years on columns.
- To only show those members on Geography whose name begins with B, edit the Geography hierarchy, and select Clear All.
- Select the Advanced tab and then select All Hierarchy Members, and OK.
- Select Member Search. You have these options:
- Enter a value in the Search Value field. For example, enter B to report all members whose name starts with the letter B.
- Select the ellipse and add filtering using MDX.
- Use Excel range to hold the value that should be used
- You can also determine the Search by criteria:
- Begins with
- Exact match
- Contains
- Ends with
- Not Begins with
- Not Exact match
- Not Contains
- Not ends with
Select OK when you have defined your search criteria.
The report now only shows those members of the Geography hierarchy that start with B.
