When standard reports available in Manager’s Reports tab do not furnish information you want, create custom reports using the visual query builder under the Custom Reports heading in that tab.
This feature allows great flexibility in defining reports, yet requires no particular software or database skills. Essentially, every variable from every ledger entry from all transactions in a business’ accounting records can be queried and included in a custom report. The results can be filtered, ordered, and grouped.
Create a custom report by going to the Reports tab, clicking Custom Reports, then New Report:
The query builder screen appears with a placeholder report already defined:
Entries in the query builder are of three types:
- Text fields for titles, names, descriptions, or certain search or operator parameters (more about these below)
- Checkboxes to activate filtering criteria, ordering, grouping, or display options
- Dropdown menus for selecting database elements to include in the report and operators to apply to the data
In most cases, multiple choices can be made by clicking Add line. Dropdown menu content varies according to prior selections. Some selections activate additional fields (see examples below). Criteria can be deleted by clicking the X at the end of a line. Fields and checkboxes on the query form are as follow:
-
Nameis used as a title on the completed report. If not edited, the report will be titled Custom Report. Any name you enter will also appear in the Custom Reports listing. -
Descriptionappears only in the Custom Reports tab listing, not on the finished report. This is useful for identifying a saved report without having to view it. -
FromandToidentify the first and last dates to be searched and reported. By default, the current date is prefilled for both. -
Accounting methodallows a choice between Accrual basis and Cash basis -
Selectallows you to choose the specific variables or elements to be included in the report. Each selection criteria line will become a column on the finished report. Their order can be adjusted by clicking and dragging on the double-ended arrows to the left of the lines. -
Aliasis used to rename a column. This is especially helpful when selection criteria are lengthy. For example, the combination of Inventory item and Code might receive the alias Code for the sake of simplicity and saving space. - The
Where…checkbox activates filters. These can become complex, especially when multiple filters are applied. Typically, additional context-specific fields appear. (See examples below.) - The
Order by…checkbox provides the ability to sort results by something besides date. Order can be ascending or descending. It can also be determined by criteria that are not selection criteria, but are present within the ledger entries. For example, a custom report on inventory items sold could be sorted by unit price, even though only quantity and total amount are shown on the report. -
Group by…provides interim subtotals for designated variables. - When context is appropriate, another checkbox appears for
Groups to collapse. With this option, entire groups can be reduced to a single line summarizing entries within the group.
Notes
Multiple selection criteria will capture transactions with any of the criteria listed. Since Manager is a double-entry accounting system, and some actions (such as invoicing for inventory items) involve more than just two accounts, transactions can seem to be duplicated. But these are simply postings to individual accounts in the general ledger that must be addressed with filters in some situations.
Multiple filters are governed by the Boolean AND operator. In other words, a selection must pass all filters sequentially to appear in the report. There is no option for an OR operator. To achieve the effect of OR filtering, you must use multiple filters to exclude everything you do not wish reported.
When groups are collapsed, only group names and subtotals are preserved.
The placeholder custom report mentioned above produces this result for a day on which there are no transactions:
Obviously, that is not very informative. But it illustrates the effect of some entries on the query builder. The best way to understand custom reports is to build a few and experiment. Here are a few examples to get you started:
Example 1
To generate a custom report listing all inventory your customers have purchased or returned on credit, create the following:
Both inventory items and inventory kits were included as selection criteria to be sure both were captured. Negative values were selected for quantity and amount purely to generate a more conventional presentation. Manager designates credits and lists quantities that reduce inventory as negative numbers. But a sales summary normally presents positive numbers.
The first filter captures only postings to the Inventory - sales account, leaving out other categories of sales. The second filter excludes cash sales made without a sales invoice.
The ordering alphabetizes customers. The grouping provides subtotals for each customer. The resulting report looks like this:
Example 2
You can generate a custom report showing your general ledger transactions grouped by account for a specific period:
In this case, no filtering is applied, because all transactions are desired. Notice that the report is grouped by general ledger account, even though that variable is not a selection criteria (only a portion of the report is shown to save space):
Example 3
A custom report showing general ledger transactions only for Accounts receivable, grouped by customer name, can be created as follows:
The resulting report is shown below. One customer still owes money; the other’s account is fully paid:
Notes
Like other Manager reports, custom reports can be copied to a clipboard for pasting into other applications’ documents. Simply click Copy to clipboard while viewing the report:
Custom reports can also be cloned. This feature is useful when nearly identical reports are desired, avoiding the need to recreate most of the query. To clone a report, click its View button, then Clone:
Another custom report form will appear. After editing it with characteristics of the new custom report, click Create.