A Quick Customer Report
We can in fact design reports around any file in MoneyWorks, allowing for Customer, Product and Job reports amongst others. To illustrate this, we’ll write a quick report to show our top debtors.
To do this you can either create a new custom report (choose File>New>New Custom Report), or just append the required parts onto the bottom of the report we have just created.
- Create a new Heading part with a heading of “Top Debtors”
In the previous section we only looked at the general ledger. Now we want to step through each of our customers. To do this we use the For Each part, which steps through each record of the nominated file that meets a specified criteria.
- Create a new part and set its type to For Each
- Set the options as shown below
This is going to step through each customer (i.e. each record in the Name file) which has a DBalance value greater than zero (i.e. they owe us something). This is sorted by the amount owed (note that we have set the sort to descending). We have named the current record being processed CUST so that we can refer to it in later calculations.
- Click OK to close the For Each part
- Append another heading part, but don’t open it. Instead double-click on the cell in COL1 of that part, to open the Cell window
- In the Value field, type “=CUST.Code” (without the quotes), set the Align to Left, and click OK
When the report is printed, MoneyWorks will evaluate this formula, which will give us the value of the code field in the name record being processed.
We’ll put the name and balance in columns 2 and 3 of the report—if you are creating a new report, you will need to append a couple of columns.
- Set the Value of the COL2 cell to “=CUST.Name”, and Align to Left
- Set the Value of the COL3 cell to “=CUST.DBalance”
You should have something like:
- Append another part to the report and set it to End For
This is where the For Loop terminates. Basically every report part between the For Each and the End For is processed for each record found in the file (or list) that the For Loop is traversing.
- Click the Test Button and preview the report.
You should get a list of everyone who owes you anything. We want to limit the report to just those who owe more than a specified amount, which we will enter when the report runs. Such a custom setting is done in the report Settings.
- Click the Settings toolbar button to open the Settings window
- Turn off the Standard Settings and the Time Interval Spec, and turn on the Custom Settings, then click the + button
A My Checkbox custom setting will appear in the list
- Double click My Checkbox, set the Control pop-up to Number, the Name to MinBal, then close the Control and the Settings windows
When we run the report, we can now enter a number into MinBal. We just need to change the For Each loop to only search for balances that exceed this value.
- Double click the For Each part and change the where formula to:
- Click the Test button to open the Report Settings window
- Key in a suitable value into the MinBal field and preview the report
The report only lists customers who owe more than the balance entered.
From this, you should have got a hint of the awesome power and flexibility that is available to you in report design in MoneyWorks. We’ve only skimmed the surface—for a more technical discussion see Custom Reports.