Filter on columns 1.2

Summary

This operator allows to filter 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.

Note that implicit parameter reference has been removed in operator version 1.2. Parameter must be referenced explicitly using the syntax `#XI.TISPar('...')#`.

Configuration

Settings

Name	Value	Opt.	Description	Example
@FILTEREXPRESSION	System.String	-	Four 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 "Apply user defined filters" has to be checked.	-
Apply user defined filters	System.Boolean	-	If checked, then filters added to the below text field are also applied.
Show calculation time	System.Boolean	opt.	If checked, then the calculation time is displayed in the "Description" field of the operation.	-
Create blank row	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 blank line	System.String	opt.	If the filter operator creates a blank row, then this text will be displayed in all text columns.	-

Want to learn more?

Screenshot

Filter types

Simple Filters

Choose the column with the criterion to filter. Choose the filter condition. If necessary, define the value for filtering If you choose more than one, define whether the conditions are regarded separately or together (And/Or).

In the operations view you can see now the defined filters and number of chosen lines.

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 conditions by "and" and "or".

Parameter Controlled Filter Conditions

To filter tables via [TIS]Board and parameters, define filter parameters.
Now define a statement which includes the filter	#XI.TISPar("Filter 1")# ==
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("Column name")#,"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.

Examples

Example: Filter on Shops

Situation	The goal is to filter a table with a bunch of rows.
Settings	The operator is going to filter everything out except the shops with the ID "ShopA" and the shops that have a turnover between 300 and 500
Result
Project File	-

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.
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)

grammar XFilter;
 
options 
{
    language=CSharp3;
    TokenLabelType=CommonToken;
    output=AST;
    ASTLabelType=CommonTree;
}
 
tokens
{
    OPERATOR;
    COMPAREEXPRESSION;
    FUNCTIONEXPRESSION;
    XIFUNC;
}
 
 
 
@lexer::namespace{xi.OpMisc.Filter}
@parser::namespace{xi.OpMisc.Filter}
 
 
 
/*
 * Parser Rules
 */
 
public
filterfunc  :   logical_expression EOF!
                      ;
 
 
 
logical_expression
                        :   booleanAndExpression (OPERATOR_OR^ booleanAndExpression )* 
                        ;
 
booleanAndExpression
                        : compareexpression (OPERATOR_AND^ compareexpression)*
                        ;
 
 
 
function        : FUNCTIONIDENTIIFER LBrace comparand (Comma comparand)* RBrace -> ^(FUNCTIONEXPRESSION FUNCTIONIDENTIIFER comparand comparand*)
                        ;
 
expression  : (comparand) operator_comp^ comparand
                        ;
 
 
operator_comp   
                        :   OPERATOR_COMP_EQ
                        |   OPERATOR_COMP_GT
                        |   OPERATOR_COMP_GE
                        |   OPERATOR_COMP_LT
                        |   OPERATOR_COMP_LE 
                        |   OPERATOR_COMP_NE 
                        ;
 
logical_op  :   OPERATOR_AND
                        |   OPERATOR_OR
                        ;
 
compareexpression   
                        : (NOT^ )? LBrace! logical_expression RBrace!
                        | (NOT^ )? function
                        | (NOT^ )? expression
                        ;
 
 
 
 
 
 
constant
    : STRING_LITERAL
    | CHARACTER_LITERAL
    | FloatString
    | Number
    | DIGIT
    | Boolean
    | DateTime
    ;
 
 
comparand   : (xIIDENTIFIER_COLUMN|constant);
 
xIIDENTIFIER_COLUMN
                : ICOL0 
                | XIFUNC
                | ICOL1                         
                ;
 
 
//          
 
 
 
/*
 * Lexer Rules
 */
 
XIFUNC_PROLOG
        :   'XI.TIS' (LETTER1)+
        ;
 
fragment
LETTER1
    :   'A'..'Z'
    |   'a'..'z'
    | DIGIT
    |   '_'
    ;
 
VAL_QUANTIFIER
    : 'P'
    | 'p'
    | 'Z'
    | 'z'
    ;
 
OPERATOR_COMP_EQ    : '==' ;
OPERATOR_COMP_GT    : '>' ;
OPERATOR_COMP_GE    : '>=' ;
OPERATOR_COMP_LT    : '<' ;
OPERATOR_COMP_LE    : '<=' ;
OPERATOR_COMP_NE    : '!=' ;
 
OPERATOR_AND
    : '&&' | 'and' | 'AND'
    ;
 
OPERATOR_OR
    : '||' | 'or' | 'OR'
    ;
 
 
 
 
protected NOT   :   '!' | 'not';
 
 
 
fragment 
DIGIT       :   ('0'..'9');
 
protected FloatString
    :   '-'? (DIGIT)* '.' (DIGIT)+
    ;
 
protected Number:   '-'? (DIGIT)+ (VAL_QUANTIFIER)?
    ;       
    
protected Boolean
    :       'true' | 'false' | 'True' | 'False'
    ;
 
protected TimePart
    : 'T'DIGIT DIGIT? ':' DIGIT DIGIT? ':' DIGIT DIGIT?
    ;
protected DateTime
    :   DIGIT DIGIT DIGIT DIGIT '-' DIGIT DIGIT? '-' DIGIT DIGIT? (TimePart)*
    ;
 
 
 
protected Comma: ',';
protected LBrace: '(';
protected RBrace: ')';
 
 
 
fragment
XIDENTIFIER :   '#'
                        ;
 
fragment
BACKSLASH       :   '\\'
                        ;
 
fragment
BLANK               : ' '
                        ;
 
FUNCTIONIDENTIIFER  
                        :   ('A'..'Z')(LETTER1)*
                        ;
 
CHARACTER_LITERAL
    :   '\'' ( EscapeSequence | ~('\''|BACKSLASH) )* '\''
    ;
 
STRING_LITERAL
    :  '"' ( EscapeSequence | ~(BACKSLASH|'"') )* '"'
    ;
 
fragment
ESC_IDENT
        : BACKSLASH ( '@'   )
        ;
        
 
 
EscapeSequence
    :   '\\' ('b'|'t'|'n'|'r'|'\"'|'\''|'\\')
    ;
 
 
Comment         : '/*' ( options {greedy=false;} : . )* '*/' {$channel=Hidden;}
                        ;
 
 
WS                  :   ( ' '
                        |           '\t'
                        |           '\r'
                        |           '\n'
                            )  {$channel=Hidden;}
                        ;
 
 
 
 
 
 
ICOL0   
        :   ('@' DIGIT+ '@')
        ;
 
 
ICOL1   
        :   '@' ( ESC_IDENT | ~('@') )+ '@'
        ;
 
 
fragment
CONSTANTS
        :   STRING_LITERAL
        | CHARACTER_LITERAL
        | DateTime
        | Number
        | Boolean
        | FloatString
        | XIFUNC
        ;
 
 
XIFUNC
        : (XIDENTIFIER XIFUNC_PROLOG BLANK* '(' BLANK* (CONSTANTS) (BLANK* ',' BLANK* (CONSTANTS))* BLANK* ')' BLANK? XIDENTIFIER)
        ;

Summary

This operator allows to filter 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.

Note that implicit parameter reference has been removed in operator version 1.2. Parameter must be referenced explicitly using the syntax #XI.TISPar('...')#.

Configuration

Settings

Name

Value

Opt.

Description

Example

@FILTEREXPRESSION

System.String

-

Four 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 "Apply user defined filters" has to be checked.

-

Apply user defined filters

System.Boolean

-

If checked, then filters added to the below text field are also applied.

Show calculation time

System.Boolean

opt.

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

-

Create blank row

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 blank line

System.String

opt.

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

-

Want to learn more?

Screenshot

Filter types

Simple Filters

Choose the column with the criterion to filter.

Choose the filter condition.

If necessary, define the value for filtering

If you choose more than one, define whether the conditions are regarded separately or together (And/Or).

In the operations view you can see now the defined filters and number of chosen lines.

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 conditions by "and" and "or".

Parameter Controlled Filter Conditions

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

Now define a statement which includes the filter

#XI.TISPar("Filter 1")# ==

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("Column name")#,"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.

Examples

Example: Filter on Shops

Situation

The goal is to filter a table with a bunch of rows.

Settings

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

Note that implicit parameter reference has been removed in operator version 1.2. Parameter must be referenced explicitly using the syntax `#XI.TISPar('...')#`.

Filters can also be created manually.
1. Activate the checkbox for using the user defined filter.
2. Type in the filter statement.

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.