If you want to filter on a column other than the entities that are listed above, you can use the Custom Filter choice. This will allow you to filter on values of any field that you have selected.
When you click on Custom Filter, this screen will display:
You can enter any valid SQL filter expression. You only need to know a little about SQL to start using filters; these expressions have the basic form:
column-name op ‘value’
Where “op” is any valid logical operator such as “=”, “!=”, “>”, “<”, etc. The value you want to filter on is entered in apostrophes if it is a string value; omit the apostrophes for numeric values. You can also use the SQL clauses “IN”, “LIKE”, and so on. The column name is case insensitive.
You can build complicated expressions using AND, OR, and parenthesis. Any expression valid in a SQL WHERE clause can be entered.
Some examples:
Trans_Number = 2227 (for a specific transaction)
customer_name LIKE ‘C%’ (all customers with names starting with C)
Debit > 100 or Credit > 100 (only rows with debit or credit > 100)
_item_color is not null (custom field “color” must be present)
_item_color in (‘Blue’,’Red’) (select from a set)
And a more complicated example:
(Trans_Type = ‘Bill’ and memo is not null) OR (Trans_Type like ‘BillPay%;)
This will select both Bills with a memo field filled in and also all bill payments.
Normally, all column names used in the filter must appear in the column list for the query. An alternative is to put column names in square brackets, e.g. [Customer_Name], in which cae FinJinni will recognize them and insure the proper columns are always included even if they don’t appear in the Excel output. Furthermore, you can supply a default value instead of NULL since in SQL NULL values don’t compare to strings even when using a not-equal compare. For example, if you want to include only names that don’t match a certain string, use this syntax:
[Customer_Name,’’] NOT LIKE ‘SAMPLE%’FinJinni will automatically insert a “COALESCE” function to reduce nulls (no customer) to spaces.