<< Click to Display Table of Contents >> Navigation:  USING REPORTS > Custom Reports

Sometimes you may want a report that doesn't seem to exist as a built-in report. To make sure you aren't "reinventing the wheel" when there's already an existing report that does what you need, we suggest checking the descriptions of existing reports in the Reports ⇒ Report Browser window.

 

Some of the built-in reports that do things you could do with Custom Reports, but don't need to, are Reports ⇒ Summary ⇒ Income Statement Yr/Yr Comparison (a year-over year comparison for all or part of one year against its previous year, with a difference column), Reports ⇒ Summary ⇒ Income Statement General Comparison (similar, but for any two ranges of dates), and Reports ⇒ Summary ⇒ Budget Comparison (comparing budget to actuals for a specified range of dates, with a difference column). There is also Reports ⇒ Fund ⇒ Budget Comparison, which is like the one under Reports ⇒ Summary but split out by fund.

 

If you don't find what you want among the built-in reports, you may be able to create it with Reports ⇒ Custom Reports. Basically custom reports allow you to select up to 4 columns for a report, each of which can be Income and Expenses, Budget, or a Difference between the two previous columns. (A Difference column can thus only be selected as the 3rd or 4th column of the report.)

 

Each column can be for a specified named Date Range, like Last Month, Last Year etc. or a user-specified Custom Date Range. The advantage of using named date ranges like Last Month is that whenever you run the report, it will calculate the appropriate dates to run it for.

 

You also get to specify:

 

•a Heading for each column (including some codes based on the range of dates included, which will be explained further below)

•whether the report is for all funds combined, or broken out by fund

•what levels of account to include, and

•a title for the report.

 

Here's the window that comes up when you select Reports ⇒ Custom Reports:

 

 

Setting up the Columns of the Report

 

Each row of the table at the top of this window represents one column of the resulting report. They are labeled that way at the left to make that clear ("Column 1" etc.).

 

As mentioned above, the Type can be Income and Expenses, Budget, or for columns 3 or 4 (as long as the two preceding columns are either Income and Expenses or Budget) a Difference between the two preceding columns. In column 3, the Difference options are "Difference (Col 2 vs Col 1)" and "Difference (Col 1 vs Col 2)", giving the two options of which column's value is compared to which to give the result in the Difference column. Similarly, in column 4, the Difference options are "Difference (Col 3 vs Col 2)" and "Difference (Col 2 vs Col 3)".

 

The Difference column shows how much "better" the first indicated column (for instance, column 2 when using "Difference (Col 2 vs Col 1)") are than the second indicated column (column 1 in this example). So for Income accounts, a Difference amount is "better", and so positive, if the first column in the named Difference is more than the second column in the named Difference. For Expense accounts it is positive if the first one is less than the second one.

 

When doing a Difference between an Income and Expenses column and a Budget column, the recommended accounting way is to have the first column indicated in the Difference expression be the Income and Expenses column. When doing a Difference between two Income and Expenses columns, the recommended accounting way is to have the first column indicated in the Difference expression be the one with the later date.

 

Budgets for a partial year are pro-rated, based on the exact count of days within the year that are included in the selected date range for the budget column.

 

Date Ranges and Headings

 

There are a lot of options for Date Range, explained in a table in the Help topic on Selecting Dates for Reports.

 

After selecting a Date Range other than Custom Date Range, the Start Date and End Date will be automatically filled in for you based on today's date. Those fields are never editable, unless you select Custom Date Range. If you end up memorizing the report (which will be explained further later) and running it on later dates, those Start and End Dates will be recalculated based on the date on which you are running the report. If you use Custom Date Range for any column, you will be required to review the definition of the memorized report before running it, so that you can edit the Start and End dates appropriately.

 

Also when you select a Date Range, a suggested Heading will be filled in for you. Those Headings can have codes such as "[Month]" included in them, which will be replaced by the appropriate actual month based on the calculated Start and End Dates whenever you run the report. Here are the codes that may appear in suggested Headings:

 

 

As an example, if you select the Date Range "Last Month", on a row with Type "Income and Expenses", the Heading suggested for you will be "[Month] [Year] Actuals". You can edit that to use other wording, but only the codes included in the suggestion, or the code "[Date Range]", will be allowed.

 

Other Settings in this Window

 

You can select either the "All Funds Combined" or "By Fund" radio button, representing the same difference as using the Reports ⇒ Summary built-in reports (All Fund Combined) or the Reports ⇒ Fund built in reports (By Fund). When it is by fund, there will be one section of the report printed for each fund that has activity (or budget lines, if budget columns are selected) within the specified ranges of dates in the report.

 

Unlike many Fund reports from the Reports ⇒ Fund sub-menu, the By Fund Custom Reports don't add a section at the bottom of the page(s) for each fund, showing the starting and ending balances of the fund, and changes to it, for the reporting period. That's because most Custom Reports will cover multiple ranges of dates, and thus there would be no obvious choice for the period to add that section for. Another option one might think would make sense would be to have one fund balances summary section under each column of the report, but that summary section is much wider than would fit under one column of numbers, so that really doesn't make sense.

 

The "Include" drop-down list is common to many other built-in reports, allowing you to select which levels of account to include in the report - All Levels of Account, Only Top-Level and Subaccounts, or Only Top-Level Accounts. Levels that are excluded (if you don't select All Levels of Account) are summarized into totals at the higher levels.

 

The Report Title is just that - it will be printed as the title of the resulting report.

 

Memorizing the Report

 

If you click the Memorize button, you will be able to memorize this report for later re-use with the Reports ⇒ Memorized Reports menu option. The program will first check that all of your settings are complete and valid before memorizing it, and give you appropriate messages about anything you need to fix. When you run a previously memorized custom report, you will have an option to return to this window for further editing first, or just run the report immediately. Date ranges and the actual displayed headings will always be appropriately recalculated based on the saved Date Range and Heading values. If any columns have Custom Date Range, you will be required to return to this window to set the dates for that column, before running it.

 

You can also memorize or re-memorize custom reports in the window that displays the actual report, which allows you to memorize it with changed Sorts or Filters (most likely Filters - it would be hard to change the sort order on reports like this without ruining them).

 

Running the Report

 

Clicking the OK button in this window will immediately run and display the report. As with Memorize, the program will first check that all of your settings are complete and valid before running it, and give you appropriate messages about anything you need to fix.

 

The program will determine, based on your selections, whether the report will be in portrait or landscape mode. Generally 2 or fewer columns will always be in portrait mode, 3 columns will be in portrait mode if you are hiding account numbers (via a setting in the Maintenance ⇒ Main Window Options window) or landscape mode if you are showing account numbers, and 4 columns will always be in landscape mode. Sorry, you cannot control this further.

 

When you are finished with a custom report in the reports-viewing window, you will be returned back to this Custom Reports window. You can then edit it further, create another custom report, or close this window with the Close button. (Unlike many other windows, ESC doesn't also close it - we don't want you to lose a carefully-constructed Custom Report by accidentally clicking ESC.)

 

Validation Rules

 

The program has a number of rules for what combinations of settings are valid for a custom report, including the following. Note that when we refer to rows of the table, that is the same as columns of the resulting report.

 

•Any row of the table at the top that has any fields filled in must have all of the editable fields filled in. (The Start and End Date fields are only editable if the Date Range is "Custom Date Range". Difference rows never have the date fields filled in.)

•As mentioned in the table of Date Ranges above, you cannot run reports including any of the date ranges Year to Last Month End, Year to Last Quarter End, Last Year to Last Month End or Last Year to Last Quarter End when you are still within the first month of your fiscal year (for the Last Month End ones) or in the first quarter of your fiscal year (for the Last Quarter End ones), because in that case the Last Month End or Last Quarter End will be in the prior year. However, you can define such reports, Memorize them, and use them later when they are valid (after the first month end or quarter end).

•Budget rows must have a date range that is entirely within one fiscal year, since budgets are for a fiscal year.

•A budget must exist for the fiscal year that a Budget row is for. (If this is not the case when you are just Memorizing the report, you will still get a warning message but will still be allowed to memorize it.)

•A Difference row can only be the 3rd or 4th row, and you can't have two Difference rows.

•When a Difference row is comparing an Income and Expenses row to a Budget row, either they must be for the same Date Range, or the Budget row must be for an entire fiscal year, and the Income and Expenses row must be for some part of that fiscal year.

•There must be a Heading on each row.

•In the Heading field, the only square-bracketed codes that are allowed are the ones in the default heading for that Date Range, or "[Date Range]".

•The version of the Heading field with the codes expanded to real dates etc. must fit into three lines of at most 13 characters each (split into lines at spaces). Headings that are longer will still be displayed, but will be cut off with a warning message.

 

---

This topic was last edited on Apr 7, 2022