Financial Report Layouts

Financial Reports

This function is used to maintain financial report configurations. You can run a financial report as described at Reports > Financial Reports.

A new Aqilla instance comes with the following standard financial reports:

The standard financial reports use the standard set of account categories and accounts.

You can modify a financial report or create one from scratch but it's often better to copy and rename an existing financial report.

A Financial Report Configuration comprises three sections:

Field	Description
Report Header	Basic details about the report and attributes that apply across the report.
Report Rows	Details about the rows in the report.
Report Profiles	Details about the profiles (i.e. groups of columns) in the report.

Custom Reports

You can customize a financial report to suit your needs.

The following screenshot is showing three custom reports that show P&L by a single project, P&L by all projects and budget and actuals for Projects.

Report Headers, Rows and Profiles

Each report has a Header, Report rows and report profiles.

Report Headers

The Header consist of all filters. The function of the report is dictated by the filters in the header.

Example: P&L by all projects is shown here below.

Each field on this record is described below. Mandatory fields are highlighted thus.

Field	Description
Report Name	The report name - max 60 alphanumeric characters.
Report Title	The report title.
Shading	None or one of several colours - used to emphasise alternate rows.
Lined Totals	Yes or No - used to emphasise totals.

You can select one breakout per report and / or up to three filters. Each of these can be either Runtime or Fixed .

If Runtime is selected the user will be prompted to make choices when running the report (e.g. select from a list of projects) and any pre-selected data will be presented as the default. If Fixed is selected the user will not be able to make any changes (e.g. the report is specific to a pre-selected project).

Report Rows:

This function is used to configure the data that shows on the individual rows of a report. Sample report rows are shown below:

A sample Report Row Edit the view is shown below:

Each field on this record is described below. Fields which are mandatory (when displayed) are highlighted thus.

Note that some fields are displayed or hidden based on the Row Type selection as shown below:

Common Fields

Field	Description
Row Type	One of several predefined types: Heading - This simply enables you to create a line on the report with no values to provide an additional heading or if the heading is left blank to separate parts of the report. Account Category - This is the simplest method of creating financial reports. The row type is based on account categories maintained at Configuration > Account Categories, which provide a hierarchical code structure categorising the different types of account. Selecting an account category for a line will automatically provide a structured report of all the accounts that are contained within that category (and its sub-categories). For example Assets consists of Fixed Assets and Current Assets . Both Fixed Asset and Current Assets are further divided in sub-categories. The standard Balance Sheet and Profit & Loss reports provided in the system are configured almost entirely by using this type of report row. Calculation - Rows can be derived by a calculation of two or more other rows in the report by specifying the names of the rows in the calculation. For instance, Net Assets can be calculated by subtracting the Liabilities row from the Assets row. The operators +, -, , and / are supported and parentheses () should be used to control the evaluation sequence within the formula. Criteria* - When the data cannot be selected using account categories, this row type provides a more generic method of selecting data. A combination of up to 5 different criteria can be used to select data for an individual line. The criteria are treated as AND conditions (e.g. if two criteria are used, the data will be extracted if both conditions are met).
Description	A description - max 60 alphanumeric characters - not shown on report.
Row Name	The row name - max 60 alphanumeric characters - must start with an alpha character and contain no spaces.
Row Sequence	Automatically generated; cannot be edited in this view.

Heading

The Heading row type uses the above common fields plus the optional field Heading (max 60 alphanumeric characters).

Account Category

Field	Description
Account Category	An account category maintained at Configuration > Account Categories. This is used as a heading so is not entered separately.
Suppress Zero Values	Yes or No - if no transactions are selected for an account, the line is suppressed. For instance, if listing all the creditors, it will determine whether a line is displayed for each creditor account or just those with transactions.
Continuation	A number of rows can be grouped together so that they appear on the report within the same section. If specified this field defines the row name for which this definition is a continuation.
Continuation Level	0, 1, 2, 3, 4 or 5 - defines the row number within the grouped row.
Expansion Level	0, 1, 2, 3, 4 or 5 - defines what the default level of expansion is in the hierarchy for the given row. Level 0 will show just a single row of totals, greater levels will display additional levels of the hierarchy broken out.
Continuation Child Level	Blank, 0, 1, 2, 3, or 4.
Sort By	Account Name, Account Code or Alternative Account Name. Defines the sequence of the accounts within the report row.
Heading	A heading - max 60 alphanumeric characters.

Calculation

Field	Description
Heading	A heading - max 60 alphanumeric characters.
Formula	The formula uses row names and mathematical operators - e.g. Sales - CostOfSales
Format	The format in which the result is to be displayed is selected from a drop down list.
Available Row Names	The available row names are listed to assist the entry of the formula.
Hidden	If a row should be hidden and only used in calculations.

Criteria

Field	Description
Continuation	A number of rows can be grouped together so that they appear on the report within the same section.
Continuation Level	0, 1, 2, 3, 4 or 5 - defines the row number within the grouped row.
Expansion Level	0, 1, 2, 3, 4 or 5 - defines what the default level of expansion is in the hierarchy for the given row. Level 0 will show just a single row of totals, greater levels will display additional levels of the hierarchy broken out.
Continuation Child Level	Blank, 0, 1, 2, 3, or 4.
Sort By	Account Name, Account Code or Alternative Account Name
Normal Sign	Debit or Credit.
Heading	A heading - max 60 alphanumeric characters.
More Selections (5)	Up to 5 additional selections can be applied.

Report Profiles

If you expect to report across multiple companies held within a single Aqilla instance please also refer to Articles > Multi-Company Accounting.

A Report Profile defines which columns to show on a report and the sequence of the columns across the page. A sample report profile is shown below:

A sample Report Profile Edit view is shown below:

A sample Report Profile Edit view is shown above

A sample *Report Column Edit* page is shown above.

Each field on this record is described below. Fields which are mandatory (when displayed) are highlighted thus.

Note that some fields are displayed or hidden based on the Column Type selection as shown below:

All Column Types

All column types have the following fields:

Field Name	Description
Column Name	The column name - max 60 alphanumeric characters - must start with an alpha character.
Description	A description - not displayed on report.
Column Type	One of several predefined types: Account Code - displays the account code. Alternative Row Description - works in the same way as Row Description except that it will use the Alternative Account Name (if specified in the accounts record) instead of the Account Name. Balance - shows the balance on an account. Blank - will display a blank column. Calculation - a calculation based on other report columns. Period Movement - the total value of transactions for the selected period Period Spread Backward - displays a column of period movements for each period from the start of year to the selected period. Period Spread Forward - displays a column of period movements for each period following the selected period to the end of the financial year. Row Description - displays the row name, account category name, or account name according to the context YTD - the total value of transactions from the start of year to the selected period In combination the two Period Spread columns can typically be used to report on the main ledger for all periods up to the period being reported and for the remaining periods, to the end of year, to report on budgets.
Heading	Up to three lines of heading can be displayed.
Hide	Yes or No. This is typically used in conjunction with Calculation columns, where you may not want to display the intermediate figures that make up a result. See also Visible in the Columns summary view. This disables the processing of the column if Visible = No and as such the result is not available to any calculation.

Numeric Column Types

All numeric column types have the following fields in addition to (1) above:

Field Name	Description
Format	Determines how amounts are formatted - select from dropdown list.
Scale	None, Thousand or Million.
Mark Negative In Red	Yes or No - self-explanatory.

Balance, Period Movement, YTD, Period Spread Backward, Period Spread Forward

These column types have the following fields in addition to (1) and (2) above:

Field Name	Description
Debit / Credit	Both, Debit or Credit - specifies whether the column should only report on debit or credit transactions (or both).
Ledger Definition	The report is based on one of the ledgers defined at Configuration > Ledger Definitions.
Field To Report By	BaseValue, CurrencyValue or Currency or Base Value - specifies whether the column should use base values or currency values. Selecting CurrencyValue or BaseValue will display the base value if the transaction is in base currency otherwise it will display the currency value. This latter option is normally only used in conjunction with Revalue below. Users may also report by any user defined numeric values in the ledger (e.g. attributes Value1, Value2 etc.) as well as base and currency values. For instance, if Time is being passed to the ledger from timesheets, this can be selected in a report column when defining a report.
Revalue	No or one of the currency rate types maintained at Configuration > Currency Rate Types. You can choose to revalue the Field to Report By based on any currency rate type. For example if you use daily rates for transaction entry, you may want to define an average rate for the period for reporting purposes. Normally this will be the currency value but if a ledger has a base value different to the main ledger it is valid to revalue this.
Custom Heading Parameter	Blank or Period Name. If Period Name is selected, the Heading text can contain a parameter %1, which is replaced by the column’s Period Name.
Company	Selecting Current Company will run the report for the currently selected company. If you select a specific company (e.g. to produce a multi-company report) then data for that company will be used when calculating values.
More Selections (5)	Up to 5 further selection criteria can be applied to the column. For example, this might be used to show separate columns for each division or cost centre or project.

Balance, Period Movement, YTD

These column types have the following fields in addition to (1) and (2) and (3) above

Field Name	Description
Year Offset	0 or a negative or positive value - used to define the column in a different year relative to the report year.
Period Offset	0 or a negative or positive value - used to define the column in a different period defined by the Relative To parameter.
Relative To	Report Period, Last Period or First Period - specifies whether the period is relative to the period defined when running the report or relative to the beginning or end of year.

Calculation

Calculation columns have the following fields in addition to (1) and (2) above:

Field Name

Description

Formula

If the column is a calculation, the calculation is defined here using the available columns that are displayed in the report (and constants). A calculation uses the standard + - * / and ( ) operators. For example to calculate a budget variance percentage, the calculation might be:

(Balance – Budget Balance) / Budget Balance * 100

The ABS function is also supported e.g. ABS(Balance – Budget Balance) will always return a positive result.

Note that all columns that make up a calculation must be defined in the same report profile. If required, columns can be hidden from the report.

Available Columns Names

The available column names are listed to assist the entry of the formula.

Apply Category Sign

Yes or No - The default behaviour when displaying a value in a column is to use the sign defined against an account’s account category at Configuration > Account Categories. This is not always applicable when performing a calculation. This option enables this feature to be disabled for specific columns.