Selecting the “CLICK HERE TO CHOOSE FIELDS:” link displays the column selection screen:
The area on the left displays the fields that are available. Each group can be expanded separately or use +/- All to expand and contract all groups at once. There are a large number of available fields and so they divided into these groups. The Transaction Fields group is also subdivided into sections, purely for convenience. The groups have no functional effect on queries.
For a list of all the available fields, see the reference section “FinJinni Report Columns” later in this document.
The area on the top right displays the fields that are selected. A field is selected by checking it on the left or by dragging it with the mouse into the area on the top right.
The area on the bottom right displays the columns by which the data will be sorted. Of course, you can always resort the data in Excel.
Note that in this case, the Subtotal column is checked. This will cause the Excel table to be created with Subtotals on the selected column, in this case Account_Type. The subtotals can be put either above or below the data rows. If “Show Subtotals Above Rows” is selected, the formatting will be slightly different on the subtotal rows – additional columns to the left of the subtotal column will be filled in and the default will be to collapse all groups.
Only one column can be subtotaled on in a query and this will also convert the Excel table to a standard Excel list-range. (There are small differences in the way Excel handles tables and ranges. Refer to the Excel documentation for more information.)
You can also choose a highlight color for the subtotal rows[1]. Data rows are not highlighted.
Here is another example:
[1] If you assign a color to the subtotal rows, you will need to save your spreadsheet as a macro-enabled spreadsheet due to the formula needed for conditional formatting of those rows.
Note that this selection sorts on Account_Order first, because, for example, we want the type “Income” to appear before “Expense”. That is the only purpose of the Account_Order field.
On Summary queries, you will see a “Y” next to the columns that are summed in the queries. Any non-sum columns result in an automatic grouping. For example, if you were to select Class, Item or similar criteria, the result will be refined to sum and display data by those items in each row.
[1] If you assign a color to the subtotal rows, you will need to save your spreadsheet as a macro-enabled spreadsheet due to the formula needed for conditional formatting of those rows.
Lastly, you can also define your own custom calculated fields, which are derived from other existing fields. See the section “Advanced Topic: Custom Query Fields”.