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.
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. | |
Calculation time displayed? | System.Boolean | opt. | If checked, then the calculation time is displayed in the "Description" field of the operation. | - |
Generate 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 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
| |
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. | |
Filter statement |
|
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. |
Want to learn more?
Examples
Example 1: 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 |
Example 2: 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.
|
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.
|
Contains("Shop" & "Arl") | Search for the term Shop A or Shop N. All cell texts containing Shop A and Arl are selected.
|
BeginsWith("Shop A") | Search for cell texts beginning with Shop A.
|
NotBeginsWith("Shop A") | Search for cell texts which do not start with Shop A beginnen.
|
Equals("Shop A") | Search for cell text Shop A. |
Equals("Shop \"Maria\"") | Search for cell text Shop "Maria". |
Equals("Shop A" | "Shop N") | Search for cell texts Shop A or Shop N. |
The following input is not allowed: | 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 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)
;
Troubleshooting
Nothing known up to now.