Purpose
This procedure is used to create and adjust General Ledger User Reports templates. These GL User Reports allow you to create financial reports in a format you require for your specific needs. The templates you create can be used to show financial information from the General Ledgers 8 years of historical figures. On each template you can nominate exactly what is to appear in the ‘columns’ of the report, and then use the template to run either Balance Sheet or Profit & Loss reports as required.
The format of the ‘rows’ on the report will automatically follow the sequence of your GL Category and Chart of Account structure as per the standard financial reports in the system so all you need to concentrate on is what details you want to see in each column of the report. Reports can also be run using any of the standard consolidations as follows:
- Consolidated – no Branch/Division detail
- By Branch – no Division detail
- By Division – no Branch detail
- By Branch by Division
- By Division by Branch
Once your template is written/adjusted you can use the ‘GL User Report Run’ function to launch the report(s).
Data Input
Step 1 – Report Code
When creating a new template, enter a new and unique report template code to use. One that does not already exist on the system. If you want to adjust an existing report template then you may search for and select an existing report template code to change.
For details on the use of “codes”, see The Role of “Codes” in Online Advantage
Return to the Top
Step 2 – Details
Report Heading
Enter the report heading for this report template. This heading is automatically printed at the start/top of each page of the report for identification.
For more details on descriptions, see The Role of “Names & Descriptions” in Online Advantage
Consolidate on Major Account
Select this option if you wish to have the GL figures consolidated at the “Major Account Number” level as set up on each individual Chart Of Accounts held in the system. If this option is left disabled then the ‘Minor’ chart of account details will be where a balance/movement is recorded in the GL. This consolidation will be in conjunction with any other consolidation that may take place when printing a report i.e. a Category consolidation will override this since only Category figures will be shown.
Print Account No on Report
Select whether the account number should be printed on the report i.e. the Chart of Account code. If not enabled then only the Chart of Account name will be shown.
Zero Replacement Character
Enter a replacement character for the number zero. This is an optional function which allows you to specify replacement character(s) for any figure that would normally show on the report as zero i.e. ‘0’
Characters such as ‘*’ or ‘-‘ may be entered to replace any field with a zero value. A maximum of three characters is allowed for zero replacement so that the word ‘Nil’ can also be used.
Minus Sign Position
Select the position of the ‘minus’ sign i.e. the ‘-‘ sign. This entry will determine how values with a negative value will appear on the report. A setting of ‘Left’ will display a minus sign to the left of the value e.g. -999 whilst ‘Right’ will display a minus sign to the right of the value e.g. 999-
Selecting the ‘Bracket’ option will surround the value with brackets e.g. (999). Note that this is a mandatory entry with ‘Bracket’ being the default option for templates.
Scaling Factor
Enter a scaling factor here that allows reports to be produced with all the financial figures rounded up to your specified factor. This entry must be numeric. An example would be ‘1000’ to provide a report rounded in thousands. Note that the scaling used here is across all columns on the report containing financial figures, but the number of decimal places can be overridden for each column on the report if desired in the ‘Column’ section to follow.
No. Decimal Places
Select the default number of decimal places to be shown on the report – either ‘None’, ‘One’ or ‘Two’. Note that the number of decimal places can be overridden for each column on the report if desired in the ‘Column’ section to follow.
Statistic Scaling Factor
Enter a scaling factor here that allows reports to be produced with all the ‘Statistics’ figures rounded up to your specified factor. This entry must be numeric. An example would be ‘1000’ to provide a report with ‘statistics’ rounded in thousands. Note that the scaling used here is across all columns on the report with ‘Statistics’ figures in them, but the number of decimal places can be overridden for each column on the report if desired in the ‘Column’ section to follow.
No. Decimal Places
Select the default number of decimal places to be shown on the report for ‘Statistics’ data. Choose either ‘None’, ‘One’ or ‘Two’. Note that the number of decimal places can be overridden for each column on the report if desired in the ‘Column’ section to follow.
Category Totals Only
Enable this option if you require a report without the account details appearing on it i.e. Chart of Account level details. When selected, the report will only show the general ledger category totals for a shorter summary report.
Print Category Percentages
Select this option to represent each category total on the report as a percentage of the very first category total figure that appears, which is usually the ‘Total Sales’ or ‘Revenue’ category. Thus you should see such figures as a ‘Gross Margin’ and ‘Total Expenses’ as a percentage of ‘Total Sales’, provided your category structure has such categories in it.
Column Width
Enter the default column width for the report template. The numeric entry here becomes the default column width for each column added to the report. Note you can adjust the width on a column by column basis as required when creating and editing column details. The ability to set a column width allows you to reduce the size of columns with numbers like percentages (or scaled totals) down to three to five characters and thus allow you to get more columns across the page.
Alternate Category Code
Choose a different structure for the “rows” on the report by selecting an Alternate Category. The ‘Standard’ GL structure is the default Category structure used unless otherwise selected.
Report Font
The choice of font size affects how many columns you can fit across the “page” of the report based on the available fixed width. For wider reports, these will be in landscape orientation. The width required for each column depends on the size of your data and the format you choose i.e. whether your figures are in the tens of thousands or millions and whether you have a comma separator and decimal places in the format e.g. 3,389.00 uses more space than 3389.
Choose the Small font size where you have more columns to include on the report. A combination of zero decimal places and small font will allow more columns to fit into the width of a report.
Choose the Large font size if you have fewer columns.
Note that the font size settings doesn’t matter if you intend to export the report data i.e. xls, csv, txt format etc.
Return to the Top
Step 3 – Columns
The ‘Column’ section of the template allows you to specify how many columns you want on your report, and the details to be printed in each of the columns.
Note that whilst the maximum number of columns on the report is unlimited, when viewing in fixed page output formats like Acrobat PDF you will only be able to see the columns that fit within a single page width. The same will apply when sending the report to print to a network printer or direct printer, all of which have a ‘fixed’ width. The use of other output formats like Excel, CSV and Text files allows all the columns on your report template to appears since they only have a theoretical maximum width.
Click the ‘Add’ button to add a report column to the list of existing columns on your template. You may edit existing column details by clicking on the columns listed. You can also click the delete icon to remove the report column details from the list.
Type
When adding or editing a column you first need to select the column ‘Type’ which determines the source of information to appear. The available column types are as follows:
- Figure – specifies that the column is to show a figure held in the financial history in the general ledger
- Hidden – same information as ‘Figure’ but the column is to be hidden from the report view. The reason for providing a ‘Hidden’ column is to allow you to use ledger figures in calculation columns without actually showing the figures on the report
- Calculation– indicates that the column will show the results of a calculation you specify based on the details/results in other columns on the report template
- Percentage – used for the ‘Financial Report Writer’ reports only
- Blank – used for spacing or grouping columns allowing you to insert a ‘blank’ column
- Line – prints graphic line down page – similar use as the ‘blank’ but more obvious
- Second page – forces the report columns to appear on a second page
Figure
Width
Enter the width for this column. The template default column width will appear, however you are allowed to adjust the width specific to this column by entering a whole number at this prompt.
No. Decimal Places
You are allowed to adjust the number of decimal places specific to this column. Select the number of decimal places to be displayed for the data in this column from either ‘Zero’, ‘One’ or ‘Two’.
Heading
Enter the heading text to be shown on the report for this column. This heading will be printed at the top of each column at the start of each page.
Width
The width of the column defines how much space is allocated for it on the report layout. Choose a smaller width for columns with figures like “quantities”. For columns where you expect the data to be in the 10’s of millions, choose a larger width. As a guide, a width of 6 fits 999,999 figures. What ‘fits’ within the column width is also dependant on the font size.
You can also use width to ‘leave a gap’ between columns or space out columns to your liking.
Note that the width setting doesn’t matter if you intend to export the report data i.e. xls, csv, txt format etc.
For Column Type: ‘Figure’ and ‘Hidden’ ONLY
Financial
You need to select the type of Financial figure to use on the column from the choices below:
- Actual – uses actual ledger balance/movement figures
- Budget – uses the ledger budget figures
- Forecast – uses figures from one of your ledger forecasts if available
Code
If you have selected Actual or Budget figures for this column, then you can specify which year’s figure you want to use from the list of eight years available.
If you have selected Forecast figures to be printed in the column, you need to specify the forecast code to inform the system of which set of forecast figures to use for this column.
Months
Select the months you wish to include in this column. There are some shortcuts available here as follows:
Current Month – if selected, the figures from the current General Ledger month will be used in this column. This option allows you to write report templates that will always show the right figures for “current” when the month is changing over time
Current Year – if selected, the total figures from the current year General Ledger including the current month will be used in this column. This option allows you to write report templates that will always show the right figures for the ‘Year To Date’ as the month changes over time
Brought Forward – using this option selects the opening balance figure to be used for the column. This is useful for Balance Sheet reporting in order to show a start balance because otherwise a balance sheet item only has a movement for each month. For example, selecting the “Brought Forward” balance of month ‘1’ will allow you to report on the closing balance of the account from the previous year.
ALL – This option will total all months for the year as the figure to use
Choose – the last option available is to select individual months from the periods available to be added together and the total will be used as the figure for the column.
Selections
With the ‘Figure’ and ‘Hidden’ column types, the option exists to add in additional ‘Selections’ to be used when picking the figures from the ledger history. This is essentially a way of adding filters on the specific column so that only figures that match the criteria will be shown. For example, if you always want the report to be run for certain divisions, you can enter those divisions here. You could have different divisions on each column of a report, or put in selections for two or more divisions to combine their figures for this report column. You can even mix different selection types on a columns filter e.g. specify one Division and two branches. Any combination of selections is possible, as long as it is logical to the figures appearing in the given column. The selection types available for use on columns are as follows:
- Account (Chart of Account)
- Branch
- Category
- Division
- Statistic
Column to Copy
Because the column selections above can be fairly long/complex, there is an option to copy them from other columns to save you re-typing them. Simply select the column that has the selections you want to copy, and click on the “Copy” button. Once copied, you can edit the selections on the column you are working on independently from the column you copied them from.
For Column Type: ‘Calculation’ ONLY
Formula
Input the formula to be used for the column to calculate the figure to appear on the report. A formula must be entered for calculation columns. The formula is entered in the following basic sequence: ‘Value’ ‘Operator’ ‘Value’ ‘Operator’ ‘Value’ etc. A ‘Value’ can either be a reference to another column on the report, or a ‘Constant’. You can enter as many values as required to complete your formula. Each ‘Value’ must be separated by an ‘Operator’.
An ‘Operator’ is a mathematical symbol. The following operators are available:
- + : Adds two values together
- – : Subtracts the second value from the first
- * : Multiplies the two values together
- / : Divides the first value by the second
- > : If the first value is greater than the second, the first value will be retained for further calculations. Otherwise, zero will be used for further calculations
- < : If the first value is less than the second, the first value will be retained for further calculations. Otherwise, zero will be used for further calculations
- % : The first value will be expressed as a percentage of the second value
NOTE: You cannot have an operator on the end of a formula.
Each item entered in the formula must be separated by a space if typed in. The report will perform all calculations in the formula from left to right.
Formula Build Tools
There are ‘Formula Build Tools’ that can be used to help you enter or complete a formula. They provide all the basic options normally used in calculation columns. Simply select the ‘column’ you wish to use in your formula and click the ‘Add Column to Formula’ button. This will include the column for calculation into the formula field above.
Alternatively input the ‘constant’ you want to use in the formula (if necessary), and click the ‘Add Constant to Formula’ button. This will include the constant into the formula field above.
You may then click on the operator to be used after entering a ‘Value’ which will automatically include the operator into the formula field. Once an operator has been applied to two values, it is the result of that calculation that is used by the next operator. You can then add ‘Values’ and ‘Operators’ to the formula, in sequence as required.
Formula Examples
Some examples of valid column formula that may be in report templates:
Example 1
1 + 2 + 3
Will add columns 1 and 2 and 3 together to provide a quarterly figure. Note this could also be achieved with a ‘Figure’ column by selecting the 3 months to be used to get the same result.
Example 2
1 * “1.1”
Which will multiply column 1 (say actuals) by a constant of 1.1 (i.e. inflating the figures by 10 percent)
Example 3
1 – 2 % 1
This will subtract column 2 (say Branch “X”) from column 1 (say Branch “Y”) and the result will be expressed as a percentage of column 1. In other words it will calculate the difference between branches “X” and “Y” as a percent of branch “Y”‘s figures
For Column Type: ‘Percentage’ ONLY
Column Number
The Percentage column is only useful when combining a User Report template with a ‘Financial Report”. The column setting on the template here allows you to nominate which column on the report is to be shown as a percentage in this column.