RD Tutorial: AR Sales By Area Pie and Bar Graphs

This tutorial will show you how to create a report to show sales by AR Sales Area 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), ARAREBAL (AR Balances) and COPERIOD (CO Periods) tables. These will be joined by the SalesAreaCode and PeriodID fields.
4. Select the following fields:
   • ARAREBAL.PeriodID
   • COPERIOD.PeriodName
   • 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 the ARAREBAL.PeriodID to have parameters for the periods required, by setting the Criteria column to:

   Between :FromPeriod and :ToPeriod

7. Click Check Query (Alt+C). This will add add the :FromPeriod and :ToPeriod parameters to the Results tab.
8. 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.
9. Click Check Query (Alt+C) to check the query for errors. If no errors are found, click Accept (F9).
10. In the Report Designer, click the Report Wizard button, or go to Accredo > File > Report Wizard.
11. In the Report Design Wizard form, set the following:
    • Report Title: Sales by Area
    • Selections Title: Report Selections
    • Retain BeforeReport Code: Unselected (Clear)
    • Retain AfterQuery Code: Unselected (Clear)
12. In the Columns tab, set the Group for PeriodID to 1, and the Group for PeriodName to 1.
13. In the Columns tab, untick (unselect) the Visible column for PeriodID.
14. 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)
15. 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")
16. Click Generate (F9). The Wizard will generate a report listing all the results of the query.
17. 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
18. Increase the height of the BandTotal: Summary Band, by clicking and dragging. Delete the TotalPeriodActivity Expression field in this band.
19. Add a Band by clicking Insert Band. Change the BandType of the new band to btSummary. Change the Name of the new band to bandPie. Increase the height of the new band by clicking and dragging.
20. Add a Graph component by clicking Add New Graph, then clicking in the bandPie band. Resize the graph to make it bigger, by clicking and dragging. Set the Style to csPie and set the Name to graphPie.
21. 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. Set the Name to graphBar.
22. Set the following properties of the second graph:
    • BackWallColor: Silver
    • BackWallStyle: bsGradient
    • ShowLegend: True
    • View3D: True
23. 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

24. 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 bar graph

    tblGraph = PivotTable(CloneQuery,"PeriodID;PeriodName","SalesAreaCode","Sum(PeriodActivity)",False)

    graphBar.Table = tblGraph

    graphBar.XDataFieldName = "PeriodID"

    graphBar.XLabelFieldName = "PeriodName"

    'Dynamically add the graph fields based on the pivot table

    TotalFields = tblGraph.Fields.Count

    graphPie.ClearAllFields

    For x = 0 to TotalFields - 1

    tblGraph.Fields.Item(x).DisplayLabel = Replace(tblGraph.Fields.Item(x).DisplayLabel, " PeriodActivity", "")

    If x > 1 then

    graphField = graphBar.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

    'Clone the report query to create the memory table for the pie graph

    Dim SQL as String

    SQL = "SELECT ARAREA.SalesAreaCode " & _

    " ,Min(ARAREA.SalesAreaName) as SalesAreaName " & _

    " ,Sum(ARAREBAL.PeriodActivity) as Activity " & _

    "FROM ARAREA " & _

    " INNER JOIN ARAREBAL ON ARAREA.SalesAreaCode=ARAREBAL.SalesAreaCode " & _

    "WHERE ARAREBAL.PeriodID Between :FromPeriod and :ToPeriod " & _

    "Group By ARAREA.SalesAreaCode " & _

    "ORDER BY Activity desc "

    Dim tblgraphBar as Object

    tblgraph1 = ExecuteSQL(SQL,FromPeriod,ToPeriod)

    'BrowseDataset(tblgraphBar)

    graphPie.Table = tblgraph1

    graphPie.XDataFieldName = "SalesAreaCode"

    graphPie.XLabelFieldName = "SalesAreaName"

    graphPie.AddField("Activity")

    graphPie.AddPieColor(ColorByName("Orange"))

    graphPie.AddPieColor(ColorByName("Blue"))

    graphPie.AddPieColor(ColorByName("Green"))

    graphPie.AddPieColor(ColorByName("Red"))

    graphPie.AddPieColor(ColorByName("Yellow"))

    graphPie.AddPieColor(ColorByName("Black"))

    graphPie.AddPieColor(ColorByName("Brown"))

    'graphPie.ResetPieColors

    End If

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