Summary

This operator allows filtering rows from a table according to conditions on their fields. It roughly corresponds to the WHERE condition of an SQL statement.

Example: Search for the term "Shop A". All rows containing the string "Shop A" in a given column are selected.

Filter on columns 1.2 vs 2:

Filter on columns

1.2

2

UI changes


  • Difference to the older version Summary:

    • you can add as many filters as you need
    • Values can now be replaced with #XI.TISPar#, but the filtering expression itself cannot be replaced here (in custom filter).

    • Similar to formatting columns, there's a new version with a JSON interface.

      Additionally, two new operational settings are being extracted from the existing user control:

      1. "Use Custom Filter"
      2. "Custom Filter - Textbox" will become new settings.

      Both of these new settings should be marked as overrideable by parameters.

      The JSON will exclusively contain an array of filtering expressions



Configuration

Settings


Name

Value

Opt.

Description

Example

 Use custom filter expression

System.Boolean 

 -

If checked, then filters added to the besides text field are also applied. This textfield can also take input from a parameter, then above will be shown a P. 


@FILTEREXPRESSION

System.String

-

Unlimited filters can be defined using pre-defined values in drop-down menus. In the leftmost drop-down menus, the column is selected, followed by the condition and, if required by the condition, a value in the rightmost field.

In the field below, custom filters can be added. To activate these filters, the checkbox "Use custom filter expression" has to be checked.

-

Calculation time are they displayed?

System.Boolean

opt.

If checked, then the calculation time is displayed in the "Description" field of the operation.

-

Generate a blank row if nothing has been found

System.Boolean

opt.

If checked, then a blank row is created instead of an empty table in case no row matches the criteria. This row contains NULL in Integer or Date fields and the text defined in parameter "Text fields content for blank line" in Text fields.

-

Text fields content for a blank line

System.String

opt.

If the filter operator creates a blank row, then this text will be displayed in all text columns.

-

Filter types

Simple Filters

  1. Choose column to be filtered.
  2. Choose filter condition.
  3. Define a value for filtering
  4. If you choose more than one, define whether the conditions are to be applied separately or together (And/Or).

You can now see the defined filters and the number of chosen lines in the operations view.


 

User Defined Filters

Filters can also be created manually.
1. Activate the checkbox for using the user-defined filter.



2. Type in the filter statement.


 

Filter statement

  • Define the columns always with encircling "@"
  • Use definitions such as
    • ==, !=
    • <, >
    • <=, >=x
    • BeginsWith(@...@, "x"), NotBeginsWith(@...@, "x")
    • EndsWith(@...@, "x"), NotEndsWith(@...@, "x")
    • Contains(@...@,"x"), NotContains(@...@,"x")
    • IsNull(@...@), NotIsNull(@...@)
    • IsEmpty(@...@)
    • IsNullOrEmpty(@...@), NotIsNullOrEmpty(@...@)
    • Top(@...@, 10), Bottom(@...@, 5) – Example delivers the Top 10 or Bottom 5 values
    • AboveAverage(@...@), BelowAverage(@...@)
    • OneOf(, 8.0, 3, #XI.TISPar("F1")#,4) – Here in column "Position" the comma-separated values are being chosen.
  • Connect more than one condition by "and" and "or".


Parameter Controlled Filter Conditions

To filter tables via [TIS]Board and parameters, define filter parameters.

Now define a statement that includes the filter

#XI.TISPar("filter1")# ==

Controller portlet

Now make a controller portlet for [TIS]Board, where you can type in the condition for your filter.

Parameter Controlled Filters for Columns

Create a parameter for defining column names

Now define a statement, which includes the filter

BeginsWith(#XI.TISPar("ColumnName")#,"A")

Controller portlet

Now make a controller portlet for [TIS]Board, where you can type in the column for your filter. 

You can define several portlets for one filter – One for the column itself, and another one for the condition.

Want to learn more?

Examples

Example: Filter for times related to work

Situation

From the table below only data containing times related to work (starting with "Arbeit" in column D) should be displayed.

Operation setting

It is possible to filter for more than one criterium, but only one is needed here.
You could also tick the "Use custom filter expression checkbox", rather than adding a filter expression with the predefined columns.

                                           

Result

TIS Project

Confluence Op Filter Columns.gzip

Example: Filter conditions for text

The allowed operators are & and |

  • & = logical AND
  • | = logical OR

Filter condition

Explanation

Contains("Shop A")

Search for the term Shop A. All cell texts containing Shop A are selected.
Example:

  • Shop A
  • Shop Arlberg
  • 1-A-Shop Anton

Contains("Shop A" | "Shop N")

Search for the term Shop A or Shop N. All cell texts containing Shop A or Shop N are selected.
Example:

  • Shop A
  • Shop Arlberg
  • 1-A-Shop Nordpol

Contains("Shop" & "Arl")

Search for the term Shop A or Shop N. All cell texts containing Shop A and Arl are selected.
Example:

  • Shop Arlberg

BeginsWith("Shop A")

Search for cell texts beginning with Shop A.
Example:

  • Shop A
  • Shop Arlberg

NotBeginsWith("Shop A")

Search for cell texts which do not start with Shop A beginnen.
Example:

  • 1-A-Shop Nordpol

Equals("Shop A")

Search for cell text Shop A.

Equals("Shop \"Maria\"")

Search for cell text Shop "Maria".
Note:In order for the inverted comma to be included in the search, it must be preceded by a backslash.

Equals("Shop A" | "Shop N")

Search for cell texts Shop A or Shop N.

The following input is not allowed:
Equals("Shop A" & "Shop N")

This query would always yield an empty table because cell texts can not be Shop A und Shop N at the same time. Use the combination Equals("Shop A") and Equals("Shop N").

Example 3: Filter conditions for numbers

  • Columns containing a null value are considered as <Condition not fulfilled>.

The format for decimal numbers (decimal point or comma) and the mathematical sign for negative values is subject to the custom of the operator (=custom of the client) The following functions are implemented:

  • Equal EQ()
  • Unequal NE()
  • Greater than or equal GE()
  • Greater than GT()
  • Lesser than or equal LE()
  • Lesser than LT()

Example: EQ(37) means to search for numbers equal to 37.



Grammar in EBNF (further definitions in XFilter.g3) Quelle erweitern


Troubleshooting

Nothing is known up to now.


Related topics