Index card Advanced filter

( Main menu - Settings - Edit Project - Project permissions - Access filter - Advanced Filter )

In the advanced filter view you can manually enter SQL-like statements into the text field above.

You can use these filters as selection filters, prefilters or alerting filters (see SQL generator example below).

3.Choose a column and an operator. Some column types offer an additional selection list. Choose one of the values or enter them between inverted commas manually into the text field.

4.Click the button Insert to automatically generate a SQL-query that is displayed in the text field above.

5.To add additional statements click one of the buttons NOT, AND, OR, ( ) and compose another statement as explained above. Click the button Insert.

Assumption: You only want data records to be displayed in the list view that contain the exact value 02.08.2004 in the

column "start editing"

and the "ID" must be 21 or higher.

You have to enter following statement into the SQL text field:

[ start editing ] = '02.08.2004' and [ ID ] >= '21'

You generally have to put column captions within square brackets, e.g.:

[ start editing ]

and values within single quotaion marks, e.g.:

'02.08.2002'

More examples:

•BETWEEN Statement - the value of a column must be between two limit values

[ ID ] BETWEEN '100' AND '400'

AND

[ Date ] BETWEEN '2008-01-01' AND '2008-12-31'

The column ID has to be greater or equal to 100 and lower or equal to 400

and

the column Date has to be greater or equal to 2008-01-01 and lower or equal to 2008-12-31

Important note

•IN Statement - the value of a column must correspond to a defined task list

[ ID ] IN ('1','4','10')

[ Priority ] IN ( 'high','mid')

The column ID must match 1,4 or 10

the column Priority must match high or mid.

•LIKE Statement - der Wert einer Spalte ist nicht exakt bestimmbar (unscharfe Suche)

The example above may be written as follows, e.g. if you do not know the exact value of the priority values:

[ ID ] IN ('1','4','10')

[ Priority ] LIKE 'hi'

[ Priority ] LIKE 'mi'

The column ID must match 1,4 or 10

the column Priority must contain 'hi'

the column Priority must contain 'mi'

The use of the LIKE operator would also exact the following values for the column Priority:

•minimal

•incredible-high

•gimmi

•Issue bezogene Felder - Felder die fix zu einem Eintrag gehören und nicht im Rahmen der Tabellendefinition modifiziert werden können:

•[ID]

The id of the issue

•LASTSUNDAY

The current state of the issue

•LASTSUNDAY

The status history of the issue

•LASTSUNDAY

The time at which a issue was currently changed

Format {'dd.mm.yyyy hh:mm:ss'}

•LASTSUNDAY

Time at which an entry had the status "new".

•date based dynamic filters - a column (type:date) equals a generic value

[ Date ] BETWEEN { LASTSUNDAY } AND { TODAY+7d }

The Date column must have a value greater than or equal to the date of the last Sunday and less than or equal to the date seven days after the current date. The dynamic date values are calculated at runtime of the filter and must always be enclosed in curly braces and can only be applied to columns of the date type.

Possible dynamic values:

•TODAY

the date of today

•LASTSUNDAY

the date of the last sunday ago

•+/- D

add or substract a number of months

•+/- WD

Add or subtract a number of workdays (Saturdays and Sundays are not counted).

•+/- M

Add or subtract a number of months

•+/- Y

Add or subtract a number of years

•user specific dynamic filters - a column (type:date) will be compared to a user specific property

[ Date ] > { LASTLOGIN - 5 WD }

The Date column must be larger than the last log time minus five working days.

•LASTLOGIN

the date of the last login of an user

Important note: The function LASTLOGIN can not be used in an alerting filter!

•If you search for inexact values, choose the operator "LIKE" instead of the operator "=".

•If you have generated a filter in the advanced mode you (usually) will not be able to edit it in simple mode.