Select the field you wish to add to your report and click the “Add” button. Do this for every field you wish to add. You can select and add multiple fields at once.
To remove a field, select the field and click the “Remove” button. You can select and remove multiple fields at once.
To set the fields in the right order, select the field and click the “Up” or “Down” button to move the field up or down.
Every field in your report has several options.
Select “Show” if you want the field next to it to be displayed in the report. Leave this unselected if you want to filter on this field without displaying it. Use the checkbox next to “Show” to select or unselect all fields at once.
“Show Mode” can be used to perform an action on the field before displaying it.
SUM: Sum all values of this field and show the total
MIN: Show the lowest value that exist for this field.
Max: Show the highest value for this field.
AVG: Show the average amount for this field.
COUNT: Show the number of rows
Using one (or more) of these calculations causes all other fields to be grouped on identical values.
If the selected field is a Date / Time field you have extra options.
DATE: Show only the date component (leave out time)
YEAR: Show only the year of the date component (e.g. 2016)
MONTH: Show only the month as a number (1 to 12)
DAY: Show only the day as a number (1 to 31)
TIME: Show only the time (leave out date)
HOUR: Show only the hour as a number (0 to 23)
MINUTE: Show only the minutes as a number (0 to 59)
SECOND: Show only the seconds as a number (0 to 59)
“Sort” determines if the sort order is lowest to highest (ASC / Ascending) (Default) or highest to lowest (DESC / Descending).
This only applies when a sort order is used.
The “Order” determines if sort is used and in what order. In the screenshot sorting will be done on the second field first, next on the first field and last on the third field. If two fields have the same number, the upper one in the list will be sorted first.
There are 3 filtering options.
1. No filtering. Leave the checkbox blank.
2. Standard filtering. Check the box
3. Special filtering: Check the box twice to make it grey.
Standard filtering is performed before calculations (Show Mode) are made (WHERE clause). Special filtering is performed after calculations are made (HAVING clause). In a HAVING clause the Show Mode is also used.
“Filter Mode” determines the relation between your filters.
AND: this filter needs to be applies in addition to the other filters.
AND NOT: Leave out rows that have this value
OR: This filter gives an alternative to the previous filter. “OR” always groups with the previous “AND (NOT)” filter.
OR NOT: The opposite of OR. Groups with the previous AND (NOT).
By sorting the fields in the report you can also determine the order of the filters.
Filter Type determines how to treat your filter.
= The filter text must be exactly the value of the field
<> value is unequal to the filter text.
< value is smaller than filter text
<= value is smaller or equal to filter text
>= value is larger or equal to filter text
> value is larger than filter text
LIKE: value must contain filter text. Use a % or * sign as a wildcard to tell Sequenchel where in the value you want to search. Text% means the value must start with the filter text. %text means the value must end with the filter text. %text% means the filter text can be anywhere in the value. If you use no % this option is identical to =
NOT LIKE: The filter text must not be present in the value. Use a % sign here as well.
IS: When looking for empty values you cannot use =. Use IS in combination with NULL to search for empty values.
IS NOT: use in combination with NULL to exclude empty values.
IN: use multiple values in your filter text separated by commas to have your value be equal to one of more options. This is equal to 1 AND and several OR statements.
NOT IN: your value must not be equal to any of the values in the filter text, separated by commas.
Sequenchel will automatically add quotes if required. The user does not need to add them. When searching for specific dates, the safest way to enter them is yyyy-mm-dd.
If you type a search text containing a space or special characters, Sequenchel will search for exactly that. If you require multiple search texts, add another filter by double clicking on the field label.