Did you know how easy it is to search for members within XLCubed?
Here’s a simple grid – let’s search for members within the Geography hierarchy.
First of all, let’s open the Geography member selector by just double-clicking the Geography header.
XLCubed offers a number of options when searching.
Firstly, you can simply enter a search term in the box before clicking the first of the icons within the green border.
That will find the first entry in the hierarchy – drag across to include in your grid report.
Click the second icon to find the next member that fits the search criteria – you can also cycle back to find the previous member(s) if needed by clicking the third icon in this group.
Advanced Search
Your second option is to use the Advanced search button – this will allow you to select all hierarchy members based on particular search criteria:
This allows you to be more specific with your search criteria:
| Type | Description |
| Begins with | Finds all members where the caption (or other selected location) begins with the given value |
| Exact match | Finds all members where the caption (or other selected location) matches the given value exactly |
| Contains | Finds all members where the caption (or other selected location) contains the given value |
| Ends with | Finds all members where the caption (or other selected location) ends with the given value |
| Not begins with | Finds all members where the caption (or other selected location) does not begin the given value |
| Not exact match | Finds all members where the caption (or other selected location) does not exactly matches the given value |
| Not contains | Finds all members where the caption (or other selected location) does not contain the given value |
| Not ends with | Finds all members where the caption (or other selected location) does not end with the given value |
You can determine which Property to search by as well as setting options for case sensitivity.
| Location | Description |
| MEMBER_CAPTION | Searches in the member caption of the member |
| MEMBER_UNIQUE_NAME | Searches in the unique name of the member |
| MEMBER_KEY | Searches in the key of the member |
| PARENT_UNIQUE_NAME | Searches in the unique name of the parent of each member |
| Other Properties | Searches in the selected member property of each member |
You can also set restrictions on the levels that will be searched.
Let’s search for all hierarchy members that contain the string ‘di’.
As you can see all hierarchy members containing the string ‘di’ have been returned.
Click the button to the left of Member to select all members or pick individual members and then click Pick.
Clicking OK will create entries in the member selector:
Advanced Search – Member Selector
Finally, you can also set up a search using the Member Selector Advanced tab – select the All Hierarchy Members icon (highlighted below):
Click the drop-down, select Member Search and set the criteria before clicking OK:
The grid returns the rows as determined by the search criteria:
The search value can also be in an Excel range so that dynamic searching is possible.
If this value changes then the grid updates accordingly. If you want to use this functionality in a published report, remember to unlock the Excel cell so that new values can be entered in the web report.
We hope you’ve found this blog useful and as always, we value your feedback.