Jump to: navigation, search
  • Main page
  • Recent changes
  • Random page
  • Set Searching

    Starting with XLCubed v6.5 you can filter a report by searching for members in a hierarchy. Click on 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.

    Searching can also be made through the advanced tab of the hierarchy editor. This then allows for the search text to be driven from an excel range.

    Capture.png

    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

    Multiple Searches

    From Version 8.1, you can search for and pick multiple members in one go. Just select the required members and press the "Pick" button before doing another search. When you press OK all the picked members will be added to the selections.

    Searching from an Excel range

    Under the advanced tab, select 'Member search' from the toolbar or dropdown menu.

    MemberSearch1.PNG

    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 will then update 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).

    UnlockCell.jpg

    Example

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

    MS1 a.png

    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: Ms9.png


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


    MS6.png

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

    MS3.png

    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:

    MS4.png

    The Advanced window is now displayed as:

    MS7.png

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

    MS8.png

    See Also