Filters

Use filters to determine whether specific values are included in a report. You can exclude or include elements whose Caption, Unique name, Attribute or Value match criteria that you specify. You can also enter an MDX statement to define a filter.

To open the Filter pane, click its heading in the List Designer.

Filters can be combined using the operators AND, OR, NOT so that a report shows, for example, values between ‘x’ and ‘y’ and not less than ‘z’.

When you create a value filter, you need to complete two steps. The first is to specify the value(s) and operator(s). The second is to specify the range of data to apply the filter to.

SeeFiltering and sorting by value in the Data Area.

In many cases, this will be the same as the range of data displayed in the report. However, you may want to filter a separate range of data. For example, you might want a report to show only sales outlets in 2004 which reported sales above a certain amount in 2003.

These options are available from a right-click in the Filter pane:

  • Create a filter
  • Add an AND operation to the filter.
  • Add an OR operation to the filter.
  • Add a NOT operation to the filter.
  • Change a logical operator (for example, from AND to OR)

Filter by

Select the filter criteria from this list:

Criteria Description
Caption or unique name

You can filter elements by the Unique names which were generated by the database, or by the name of the element shown in the report. Use the text filter to search for texts and strings.

Attribute Filters the attributes of the elements defined in the OLAP database.
Value Filters the values of a range. You must specify the filter settings and the Data Area of the filter.

SeeFiltering and sorting by value in the Data Area.

Custom defined MDX Here you create a filter using an MDX statement.

Filter type

Use the Filter type to filter the caption, the unique name and the attributes. The choices depend on the selected filter type:

Filter Type Description
String The hierarchy is searched for elements whose caption, unique name or attribute is identical with the search text.
Wildcard You can use wildcards to filter the element names.
Regular expressions You can use regular expressions to filter the element names.

Regular expressions with AS2005+

To use .NET regular expressions with AS2005+ data sources, you must install an assembly on the Analysis Services server.

The assembly file is called Mis.Reporting.ServerExtension.dll and is installed to C:\Program Files\Infor BI\OLAP\bin by the Office Plus setup.

Use SQL Management Studio to connect to the Analysis Services database and add an assembly to the Assemblies folder.

The assembly must have these parameters:

  • Path: ..\OLAP\bin\Mis.Reporting.ServerExtension.dll.
  • Name: MisOlapYukonExtension
  • Permissions: Safe
  • Impersonation: Default

.NET regular expressions

To use .NET regular expressions select Regular Expression as the filter type. You can use these regular expressions:
Expression Explanation Example
^ Match-beginning-of-line operator ^A finds list elements starting with an A.
$ Match-end-of-line-Operator er$ finds list elements ending in er.
+ Match-one-or-more operator e.+r$ finds all list elements ending in r and also containing an e. At least one character must be located between e and r.
| Alternation Operator ^A|^B finds list elements beginning with A or B.
() Grouping ^(..) finds list elements containing an even number of characters.
. Any character ^c..d finds list elements beginning with c, followed by two characters, followed by d.
\ Defines the subsequent character as a literal. If you want to look for an 'operator character' in a list element, the backslash indicates that the character which follows belongs to a list element and is not an operator. So, \\ finds all list elements containing the character \.
Note: 

Regular expressions are case-insensitive. Prefix an expression with (?-i) to make it case-sensitive. A complete guide to .NET Regular Expressions can be found at:

Condition

The condition operators are:

Operator Description
* No restriction: no condition is set (default setting). The input box is disabled.
= The entered value will be considered.
>= Values greater than the entered value and the value itself will be considered.
<= Values smaller than the entered value and the value itself will be considered.
> Values greater than the entered value will be considered.
< Values smaller than the entered value will be considered.
<> Values greater and smaller than the entered value will be considered.
Contains Strings or numbers containing the entered value will be considered.
Does not contain Strings or numbers which do not contain the entered value will be considered.

Creating and editing filters

To create a filter, click New Filter and define the filter in Filter Settings.

To exchange one logical operator with another, select the operator and click Change Logical Operation. Select the new operator from the shortcut menu.

You can move parts of the filter by drag & drop.

To delete parts of a filter definition, choose the node in the tree or the operator and click Remove.

Filtering element names

To filter element names:

  1. Click New Filter
  2. Select Caption or Unique name from the Filter by list.
  3. Select the filter type, the condition and the value.
Note: 

The Condition refers to the alphabetical sort order of the elements in the list. The position within the hierarchy is not taken into account.

Filtering attributes

To filter attributes:

  1. Click New Filter.
  2. Select Attribute from the Filter by list.
  3. Select the filter type, attribute, condition and value.

Filtering values

To filter values:

  1. Click New Filter.
  2. Select Value from the Filter by list.
  3. Select the operators and values.
  4. Use the Data Area to define the range of date to which to apply the filter (Filtering and sorting by value in the Data Area).

Filtering with user-defined MDX

To create user-defined MDX:

  1. Click New Filter.
  2. Select User-defined MDX as the filter type.
  3. Click Custom MDX within the input area and then click the Select button.

The Edit Custom MDX dialog is displayed.

AND, OR and NOT operators

Combine filter definitions with the logical operators AND, OR and NOT.

  • AND : Creates the intersection of two sets.
  • OR: Creates the union of two sets.
  • NOT: Creates the exception of two sets.

Examples

These examples are based on the demo model Best Practices OLAP model supplied with OLAP Server.

Filter the element caption:

  1. Double-click the Sales cube to create an Ad-hoc report.
  2. Click the Product dimension.
  3. Click Filter in the List Designer.
  4. Click New Filter.
  5. Select Filter by Caption in Filter Settings.
  6. Select String as the filter type.
  7. Select Equals as the condition.
  8. Select All Season Tires as the value.

The list displays all elements which contain the caption All Season Tires.

Use regular expressions to filter:

  1. Click New Filter.
  2. Select Filter by Caption in Filter Settings.
  3. Select Regular Expression as the filter type.
  4. Select Equals as the condition.
  5. In the Regular Expression field, specify ^Car.

The list displays all elements beginning with the string Car.

Parent topic: Lists