Below is an example of a report that can be created in Report Generator. This report is an variation on the “Percentage of Outstanding Balance by Customer by Period Report’. This report will show a list of your top 10 debtors that have the highest percentage of their outstanding balance 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 should contain the function A/C Period 4”/”A/C Balance”*100 that may have been name “P4 %”. It should be set to Descending order.
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.
To set the report to only show the top 10 (this number can be changed as required) debtors that have the highest percentage of their outstanding balance in Period 4 select the button with three dots next to the report name. On the Report Generator Maintenance screen that will open tick the “Max Lines” tick box and enter the required number of lines (in our example this is 10) into the white box next to the max lines tick box. Select OK to save this change.
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.