RD Tutorial: AR Outstanding Balances by Sales Area

This tutorial will show you how to create a report to show the AR Outstanding Balances by sales area.

1. Go to Navigator > Setup > Report Designer to open the Report Designer.
2. First, create a SQL Query. Click Edit Query (Alt+Q).
3. Select the ARCUST (AR Customer) and the ARAREA (AR Sales Areas) tables. These will be joined by the SalesAreaCode fields.
4. Select the following fields:
   • ARCUST.CustomerCode
   • ARCUST.CustomerName
   • ARCUST.Balance1
   • ARCUST.Balance2
   • ARCUST.Balance3
   • ARCUST.BalanceCurrent
   • ARAREA.SalesAreaName
   • ARCUST.SaleAreaCode
5. Set the Sort column to Ascending for the ARCUST.CustomerName and ARCUST.SalesAreaCode to order by these fields.
6. Go to the SQL Tab. Add the following line before the ORDER BY line:

   WHERE (ARCUST.Balance1 + ARCUST.Balance2 + ARCUST.Balance3 + ARCUST.BalanceCurrent) <> 0

7. In the SQL Tab, make sure the ORDER BY clause is in the following order:

   ORDER BY ARCUST.SalesAreaCode
           ,ARCUST.CustomerName

8. Click Check Query (Alt+C) to check the query for errors. If no errors are found, click Accept (F9).
9. In the Report Designer, click the Report Wizard button, or go to Accredo > File > Report Wizard.
10. In the Report Design Wizard form, set the following:
    • Report Title: Outstanding Customer Balance Report Grouped by Sales Area Code.
    • Selections Title: Report Selections.
    • Retain BeforeReport Code: Unselected (Clear).
    • Retain AfterQuery Code: Unselected (Clear).
11. Leave the defaults in the Columns and Selections tabs.
12. Click Generate (F9). The Wizard will generate a report.
13. Select the Report, then click the BeforeReport property. Add the following as the first line, before the existing code:

    Total = 0
    AreaTotal = 0

14. Add a band with BandType btGroupHeader after the Column Header band. Set the GroupExpression to Query.SalesAreaCode.
15. Add a band with BandType btGroupFooter after the Detail band. Set the GroupExpression to Query.SalesAreaCode.
16. Select the Query.SalesAreaCode Expression in the Detail band. Cut (Ctrl+X) this, then Paste (Ctrl+V) it into the Group Header band. Move it to the left of the band.
17. Select the Query.SalesAreaName Expression in the Detail band. Cut (Ctrl+X) this, then Paste (Ctrl+V) it into the Group Header band. Move it next to the Query.SalesAreaCode expression.
18. Delete (F3) the Introduction Band.
19. Select the Detail band, then click the AfterBand property. Change the code to:

    AreaTotal = AreaTotal + BalanceTotal
    BalanceTotal = 0

20. Select the Detail band, then click the BeforeBand property. Change the code to:

    BalanceTotal = (Query.Balance1 + Query.Balance2 + Query.Balance3 + Query.BalanceCurrent)

21. Select the Footer band, then click the AfterBand property. Change the code to:

    Total = Total + AreaTotal
    AreaTotal = 0

22. In the Column Header band, Detail Band and Summary band, delete all the fields for Balances, Balance totals and Sales Areas.
23. Add a label component to the Column Heading band, to the right of the band. Set the label to Balance.
24. Add an expression component to the Detail band, to the right of the band. Set the Expression to BalanceTotal.
25. Add an expression component to the Group Footer band, to the right of the band. Set the Expression to AreaTotal.
26. Add an expression component to the Summary band, to the right of the band. Set the Expression to Total.
27. Click Select Bands (F2) beside the Query Bands field. Select all the bands.
28. Save the report, then click Execute Report (Ctrl+R) to run the report.
29. You can resize and move objects around to improve the appearance of the report. For example, you may want to set the BorderBottom property for the AreaTotal and Total expression fields to rbDouble, and set the BorderTop to rbSingle, to print borders around above and below the total amounts.

Note: This report is provided with Accredo. See the AROutstandingBalancesBySalesArea.pfd report.