Article Objective: To assist users in navigating Active Ledger & Reporting with creating custom and financial tables.
Context: Custom and financial tables can be created within any of the report 'Content' sections to customise additional data that you may want to display in your reports.
Please refer to the Glossary for definitions of key terms used in this article.
TABLE OF CONTENTS
|
Navigating to the content section
To begin, navigate to the Reports screen and select the relevant report template. For more information on how to do this, see the knowledge article: Reporting - creating a report.
Navigate to the content section of the statement you wish to add additional information for, i.e. profit & loss, balance sheet, etc.. Select the checkbox for 'Customise Content' and select Edit Content - this will generate a pop-up screen with the content information.
Add a table by hovering over the bottom of the left content section and clicking on the plus icon, and then More. This will bring up the remaining options, select either 'Custom Table' or 'Financial Table':
A custom table allows full flexibility in displaying any type of data in tabular form. See 'Custom tables' section of the article below for more information
A financial table contains the formula editor so that financial information can be automatically populated from your client's data. See 'Financial tables' section of the article below for more information.
Custom tables
After you select Custom Table, you can start building the table. Hover over the 'My Table' section and click on either the pencil icon to the left, or the grey space next to 'My Table'.
This will open up edit mode.
From here you can create as many columns and rows as required. To insert an additional column, hover over the area you would like the column and click on either of the + buttons.
To edit the formatting of an entire column, click on the pencil icon above the relevant column.
The following options will become available:
Field | Description | Validation |
Alignment (dropdown menu) | Determines the alignment of the data.
| Optional |
Vertical Alignment (dropdown menu) | Determines the vertical alignment of the data.
| Optional |
Sizing Type (dropdown menu) | Determines whether the width of the column remains fixed or changes relative to the data entered. | Required |
Select Size (dropdown menu) | Only available when 'Fixed' is selected as the Sizing Type. This determines the width of the column. There are seven options to choose from here. | Required |
Weight | Only available when 'Relative' is selected as the Sizing Type. This determines the width of the column. Input this manually. e.g. 1 (This Relative > Weight setting also helps when exporting a PDF and the custom table width is truncated or not displaying correctly) | Required |
Value Type (dropdown menu) | Determines if the column will contain numbers, text (string), or dates. | Required |
Number Format (dropdown menu) | Only available when 'Number' is selected as the Value Type. This determines the type of number to display.
| Required |
Rounding | Only available when 'Number' is selected as the Value Type. This is used to set the number of decimal places. | Optional |
Auto Calculate Total (checkbox) | Only available when 'Number' is selected as the Value Type. If this is selected, all columns with the number values will be added up automatically. | Optional |
To insert a row, hover either above or below an existing row and click on the + button.
By default, the first row is a column header and the second row beyond are standard rows. These can all be edited or deleted using the pencil or bin icons to the left of the row. Clicking on the hamburger icon allows you to move the row to another location.
Clicking on the edit icon brings up the following options:
Field | Description | Validation |
Row Type (dropdown menu) | Determines settings, such as font, colour, and borders, for the row. There are six options to choose from here. | Required |
Level | Determines whether to indent the data and to what extent. | Required |
Value Type (dropdown menu) | Determines if the row will contain numbers, text (string), or dates. | Required |
Alignment (dropdown menu) | Determines the alignment of the data.
| Optional |
Row Style (dropdown menu) | Can be set to 'Normal' or 'Grouped/Group Header' if the 'Header' or 'Column Header' Row Type is selected. Or to 'Normal' or 'Alternative' if the 'Total' Row Type is selected. | Required |
Vertical Alignment (dropdown menu) | Determines the vertical alignment of the data.
| Optional |
Number Format (dropdown menu) | Only available when 'Number' is selected as the Value Type. This determines the type of number to display.
| Required |
Rounding | Only available when 'Number' is selected as the Value Type. This is used to set the number of decimal places. | Optional |
Auto Calculate Total (checkbox) | Only available when 'Number' is selected as the Value Type. If this is selected, all columns with the number values will be added up automatically. | Optional |
Height | Only available when 'Spacer' is selected as the Value Type. This is used to set the height of the row in pixels. | Required |
In addition to being able to edit rows and columns, you are also able to customise individual cells. To edit a cell, or to enter data into it, simply right click on the individual cell and select Cell Options.
Many of the same formatting features will be available. The Value box is used for data entry, a larger box denoting a 'String' (text) value type and a smaller box, a 'Number' and 'Date' value type.
The 'Colspan' option allows you to merge cells together in a row, simply select how many columns wide you would like the cell to be.
Click Save.
Once you have completed your table, hit the Close button.
Note: Make sure you save the report template in its entirety so all details are saved and able to be rolled over in coming years.
Financial tables
After you select Financial Table, you can start building the table. Hover over the table section and click on either the pencil icon to the left, or the grey space.
Note: Creating a financial table works in exactly the same way as a custom table. The differences are that you begin with two columns of period data (which automatically adjust to the number of columns selected in your financial report), and you are able to add formulae to allow for automatic calculations in your reports.
You are able to add, edit and delete rows and columns as described above. However, depending on the row type (i.e. if standard or total rows are selected), you are also able to select the 'Use formula for this row' checkbox.
Clicking on the checkbox brings up the following options:
Field | Description | Validation |
Title | This is the title of formula. | Required |
Title When results are a mix of positive and negative | Alternative name of total displayed in the report if the outcome is a mix of positive and negative balances over multiple years. | Optional |
Title When all results are negative | Alternative name of total displayed in the report if the outcome is a negative balance. | Optional |
Ledger Side | Determines whether the total is shown as a debit or credit balance.
| Required |
Style | Determines the look of the total line. Can be set to 'Normal' or 'Alternative'. The default option is 'Normal'. | Required |
Format result as percent % (checkbox) | If you wish for the result of the formula to be represented as a percentage, select this checkbox. | Optional |
Formula | You are either to manually type a formula in the box provided, or select one of the options from the 'Insert Formula' section.
After one of the above options are selected, further information will be available to fill out.
See 'The formula editor' section of the article below for more information. | Required |
Show only if the following Headers are Present (dropdown menu) | This will default to 'Show Always'. If you wish to display only for certain headers, make your selection accordingly from the dropdown list of headers. | Required |
Hide If Zeroes Only (checkbox) | If you do not wish to show results if they are NIL, select this checkbox. | Optional |
The formula editor
As mentioned in the above table, there are four options to choose from when adding a type of element to insert, in addition to manually inputting the formula. Each of these options provide further dropdown menus after selected.
For this example, we will select Standard Formula. This will bring up a second dropdown menu with the below options.
Choose the relevant formula and click Insert.
Once your formula is complete, click Save.
Using the copy and paste feature to construct tables
If you prefer to prepare your table in Excel and copy and paste the information into the report builder for custom or financial tables, you are able to do so.
To do this, construct your table in Excel and select the box below that outlines 'Select this box to paste from Excel'. We will use a custom table as an example.
From here, copy and paste your information in using Ctrl-v, as prompted below.
You can either choose to include headings in the information to be copied and pasted over, or you can manually input the headings.
Note: If choosing to include headings in the Excel information copied over, you will need to firstly, delete the column header automatically populated in the table by selecting the bin next to the row, as below.
You then must elect the copied headers to be the column header. To do this, select the pencil icon next to the row, select the Row Type as Column Header and click Save.
This will update the table accordingly.
Once you are satisfied with your table, hit the Close button.
Tip: This feature is especially useful if you are customising the content of the tax reconciliation section of the report.