RD Tutorial: AR Sales Areas by Period Graph

This tutorial will show you how to create a report to show sales by Period in a graph.

1. Go to Navigator > Setup > Report Designer to open the Report Designer.
2. First, create a SQL Query to return the Sales Area Code, Description and Period Activity for a range of periods. Click Edit Query (Alt+Q).
3. Select the ARAREA (AR Sales Areas) and ARAREBAL (AR Balances) tables. These will be joined by the SalesAreaCode field.
4. Select the following fields:
   • ARAREBAL.PeriodID
   • ARAREA.SalesAreaCode
   • ARAREA.SalesAreaName
   • ARAREBAL.PeriodActivity
5. Sort the results by PeriodID and SalesAreaCode, by selecting Ascending in the Sort columns for these fields.
6. Set ARAREBAL.PeriodID to be hidden, by un-ticking the Show column.
7. Set the ARAREBAL.PeriodID to have parameters for the periods required, by setting the Criteria column to:

   Between :FromPeriod and :ToPeriod

8. Go to the SQL tab. Add a line after the ,ARAREBAL.PeriodActivity line, before the FROM ARAREA line. In the new line type:

   ,PeriodEndDate(ARAREBAL.PeriodID) as DatePeriods

9. Click Check Query (Alt+C). This will add add the :FromPeriod and :ToPeriod parameters to the Results tab.
10. On the Results tab, enter a Period ID for the FromPeriod and ToPeriod, for example 201 and 206. Set the Type for both parameters to Number.
11. Click Check Query (Alt+C) to check the query for errors. If no errors are found, click Accept (F9).
12. In the Report Designer, click the Report Wizard button, or go to Accredo > File > Report Wizard.
13. In the Report Design Wizard form, set the following:
    • Report Title: Sales Area by Period
    • Selections Title: Report Selections
    • Retain BeforeReport Code: Unselected (Clear)
    • Retain AfterQuery Code: Unselected (Clear)
14. In the Columns tab, set the Group for DatePeriods to 1, and move this to top first row.
15. In the Selections tab, set the following for FromPeriod:
    • Caption: Period - From
    • Input Type: Period
    • Style: From
    • If Blank: Set to First
    • Default Value: AddPeriod(CurrentPeriod("AR"),-5)
16. In the Selections tab, set the following for ToPeriod:
    • Caption: Period - To
    • Input Type: Period
    • Style: To
    • If Blank: Set to Last
    • Default Value: CurrentPeriod("AR")
17. Click Generate (F9). The Wizard will generate a report listing all the results of the query.
18. To show the results in a graph, rather than listing all the figures, delete the following bands by selecting each band, then clicking Delete Band (F3):
    • bandColumnHeading: Column Header Band
    • bandGroupHeader1: Group Header Band
    • bandStandard: Detail Band
    • bandGroupFooter1: Group Footer Band
19. Increase the height of the BandTotal: Summary Band, by clicking and dragging. Delete the TotalPeriodActivity Expression field in this band.
20. Add a Graph component by clicking Add New Graph, then clicking in the bandTotal band. Resize the graph to make it bigger, by clicking and dragging.
21. Set the following properties of the graph:
    • BackWallColor: Silver
    • BackWallStyle: bsGradient
    • ShowLegend: True
    • View3D: True
22. Select the Report, then click the BeforeReport property. Add the following to the start of the code,

    ' See AfterQuery code for code relating to the graph
    Dim tblGraph as Object

23. Select the Report, then click the AfterQuery property. Delete the existing code, and enter the following code:

    ' Create a list of colours to be used in the graph
    Dim ColorsForGraph as Object
    ColorsForGraph = CreateObject("Accredo.StringList")
    ColorsForGraph.CommaText = "red,blue,green,yellow,cyan,pink,silver,brown,purple,teal"
    If not Query.IsEmpty Then
      'Clone the report query and pivot to create the memory table for the graph
      tblGraph = PivotTable(CloneQuery,"DatePeriods","SalesAreaCode","Sum(PeriodActivity)",False)
      graph1.Table = tblGraph
      graph1.XDataFieldName = "DatePeriods"
      graph1.XLabelFieldName = "DatePeriods"
      'Dynamically add the graph fields based on the pivot table
      TotalFields = tblGraph.Fields.Count
      graph1.ClearAllFields
      For x = 0 to TotalFields - 1
        tblGraph.Fields.Item(x).DisplayLabel = Replace(tblGraph.Fields.Item(x).DisplayLabel, " PeriodActivity", "")
        If x > 1 then
          graphField = Graph1.AddField(tblGraph.Fields.Item(x).FieldName)
          graphField.DisplayLabel = tblGraph.Fields.Item(x).DisplayLabel
          graphField.BrushColor = ColorByName(ColorsForGraph[(X-2) Mod ColorsForGraph.Count])
        End If
      Next x
    End If

24. Save the report, then click Execute Report (Ctrl+R) to run the report.

Note: A similar report is provided with Accredo. See the ARSalesAreasByPeriodGraph.pfd report. That report displays figures by PeriodID, rather than Period End Date in this report, so gives slightly different results when periods are in different financial years.