Set Searching

Starting with XLCubed v6.5 you can filter a report by searching for members in a hierarchy.

Search types

From Version 8, you can also search for members where the caption or property doesn't contain a particular string.

Type	Description	Availability
Begins with	Finds all members where the caption (or other selected location) begins with the given value	v6.5
Exact match	Finds all members where the caption (or other selected location) matches the given value exactly	v6.5
Contains	Finds all members where the caption (or other selected location) contains the given value	v6.5
Ends with	Finds all members where the caption (or other selected location) ends with the given value	v6.5
Not Begins with	Finds all members where the caption (or other selected location) does not begin with the given value	v8.0
Not Exact match	Finds all members where the caption (or other selected location) does not exactly match the given value	v8.0
Not Contains	Finds all members where the caption (or other selected location) does not contain the given value	v8.0
Not Ends with	Finds all members where the caption (or other selected location) does not end with the given value	v8.0

Search locations

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

Case sensitivity

From Version 7.6, you can have XLCubed search for members:

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

Example

The report below shows a simple grid with Geography on rows and Fiscal Years on columns.

We would like the report to only show those members on Geography whose name begins with B. Edit the Geography hierarchy, and select Clear All by clicking:

Next, click Advanced tab and then select All Hierarchy Members by clicking:

Then, click on Member Search and the following window will be displayed.

At this point you have three options:

• enter a value in the Search Value field – in our example we enter B as we want to report all members whose name starts with the letter B

• click the ellipse and add some filtering using MDX

• use Excel range to hold the value that should be used

In our example we are using the value in cell F2 to determine the filtering on our report.

You can also determine the 'search by' criteria as below:

The Advanced window is now displayed as:

As you can see the report now only shows those members of the Geography hierarchy that start with B.

See Also

• Ranking, Sorting and Filtering