Below is an example of a report that can be created in Report Generator. This report will show a list of your debtors and a break down showing the percentage of their outstanding balance that is held in each period.For example, do they have a greater outstanding balance in the current period or in period 4?
Note: The percentages of the balance per period in this report will be based on the last debtors ageing date. This report could be run after the month end process has been run to generate updated data with the new customer ageing.
Create a new report and give it a name.
In the print list add the following fields:
- From the Customer Drop Down add “Account Number” and “Trading Name”
- From the Function Drop Down, Select the Add button to enter the function creation screen.
- From the Customer Drop down select “A/C Current” and press the add button
- Click on the divide symbol (/)
- From the Customer Drop Down, select “A/C Balance”
- Press the add button
- Press the multiply symbol (*)
- Click on 1 then 0 then 0 again (100) from the number pad
- Press the Equals symbol (=) to finalise the function
- Click OK.
NOTE: This will add this function to the bottom of the list on the left hand side of the Report editing area.
- Repeat the previous step for Periods 1 through to Period 4 by replacing “A/C Current” with A/C Period 1 thru 4 in the function as required.
This will allow you to add the new functions to the print list
Once the A/C Current”/”A/C Balance”*100 field has been added to the Print list, select the box with three dots next to it to open the Print detail screen. In this screen set the number of decimal places to 2. You can also change the Column Heading if you would like, to make it more meaningful to the user. A suggestion would be to change the label to “Current %”.
Repeat this step for the other four period functions. A suggestion for the names would be to change their labels to “P1 %”, “P2 %”, “P3 %” and “P4 %” as appropriate.
The Sort List can contain which ever field you would like to sort the report by. A suggestion could be to sort by trading name or account number. Or you could sort by one of the percentage field to find the customers that owe the highest percent in that period.
The Qualify list can have any fields and qualifiers you would like depending how you want to narrow down the information displayed. But it should have at least the following fields:
From the Customer Drop Down add “A/C Balance” and set the qualifier as “is NOT from 0 to 0”. This will prevent any debtors showing who have a zero dollar balance. It will also prevent errors due to the system trying to divide by zero.
When outputting the report, you can print the report to either a printer, or you could output it as a file. If you output it as a file, it will then allow you to import the file into Excel and manipulate it from there. To change how you would like the report produced, select the button with three dots next to the report name. This will open the Report Generator Maintenance screen. Select the output method in the “Output To” drop down.
- If you select Print you can leave the other settings as is.
- If you select File you will need to set the Delimit drop down to “TAB”, Tick the Include heading tick box and enter a name in the while box next to the “Output To” drop down. The file name should have no spaces and end in “.txt”.
The report should be ready to run now.