Adding Advanced Filters

Adding Advanced Filters

To see the filter definition for an advanced filter, click the Advanced Options button next to that filter. The system then displays a dialog box with information like the following:

This represents the logic of the filter, but not its literal syntax. This filter selects only products that are either of the following:

• Candy products sold in Bangalore

• Snack products sold in Houston

The Advanced Filter Editor enables you to easily add, modify, and remove parts of a filter expression, while seeing the resulting expression with every change. This book does not describe this tool in detail, but instead provides a brief demonstration.

1. Click the Advanced Options button in the Filters box. The system displays a dialog box like the following:

   

2. Click Add Condition.

   The dialog box now looks something like this:

   

   Initially, the advanced filter editor uses the first level (considering alphabetic order) defined in your subject area.

3. If you want to use a different level:

   1. Click YearSold, which enables you to edit this part of the expression.

   2. Optionally type a string into the search box and then click Search. This is useful if the cube has a large number of levels.

   3. Select a level from the drop-down list on the left. As soon as you do, the expression is updated. For example:

      

      The end of the drop-down list may include measures; if so, see Filtering the Data by Measure Value, later in this page.

4. If you want to change the operator from IS to IS NOT:

   1. Click IS, which enables you to edit this part of the expression.

   2. In the left area, click IS NOT. As soon as you do, the expression is updated.

5. To specify a member of the level:

   1. Click <select a value>, which enables you to edit this part of the expression. The left area might look like the following:

      

   2. In the left area, click the Search button under Select a value to display a dialog.

   3. The resulting dialog displays a set of members and options. Optionally type text into the search box and press Enter; this restricts the set of members listed in the dialog.

      The list of values also includes all searchable measures (see the next section) and all pivot variables (see Defining and Using Pivot Variables).

   4. Select members and options by selecting their check boxes.

   5. Click the check mark button in the upper right hand corner of the dialog. As soon as you do, the expression is updated.

      For example:

      

   6. Alternatively, you may enter an MDX key by selecting the MDX Key option.

   This advanced filter is equivalent to a simple filter. Typically you would now add another condition or a branch that contains other conditions.

6. To add another condition:

   1. Click AND, so that you can edit this item.

   2. Optionally, in the left area, click OR. As soon as you do, the expression is updated.

   3. Click Add condition.

      You might now see the following:

      

   4. Edit this condition in the same way that you edited the first one.

7. Click OK to close the dialog box.

   Or add another condition or a branch that contains other conditions.

Note that you can undo any change. To do so, click Undo.