FD Tutorial: Dashboard Budget vs Actual Sales Group

You can modify the FD Tutorial: Dashboard Budget vs Actual Expenses to instead return the Top 5 Sales Groups for the last 12 months. This tutorial requires the AR Module.

Follow the steps in the tutorial, with the following changes.

4. Name the table tblGraphBudgetSales.

6. Add the following fields to the table:

No.	Name	Display Label	Type	Domain	Size	Width	Req	Visible
1	SalesGroup	SalesGroup	String		10	10		Selected
2	Sales	Actual	Float	Amount		10		Selected
3	Budget	Budget	Float	Amount		10		Selected

11. Name the graph graphBudgetSales.

13. Set the table to tblGraphBudgetSales. Set the XLabelFieldName to SalesGroup.

15. Add the following fields to the Graph Editor:

Field Name	Display Label	Show Key Border	Pen Style	Pen Width
Sales	Actual		psSolid	1
Budget	Budget		psSolid	1

17. Enter the following code, to extract the sales information and populate the memory table, and display it in the graph:

Sub RefreshBudget
  Dim SalesGroup as String
  PeriodToUse = CurrentPeriod("AR")
  StartPeriod = AddPeriod(PeriodToUse, -12,True)
  EndPeriod = AddPeriod(PeriodToUse, -1,True)
  SQLSalesGroup = "SELECT Min(ARANLBAL.PeriodID) as PeriodID " & _
  " ,Min(ARANLBAL.AnalysisCode) as SalesGroupCode " & _
  " ,Sum(ARANLBAL.Budget) as Budget " & _
  " ,Sum(ARANLBAL.PeriodActivity) as Sales " & _
  "FROM ARANLBAL " & _
  "WHERE ARANLBAL.PeriodID Between :StartPeriod AND :EndPeriod " & _
  "Group BY SalesGroupCode " & _
  "Order BY Sales DESC "
  tblSalesGroup = ExecuteSQL(SQLSalesGroup,StartPeriod,EndPeriod)
  Counter = 0
  tblGraphBudgetSales.empty
  tblGraphBudgetSales.AllowInsertDelete = True
  tblSalesGroup.First
  Do Until tblSalesGroup.EOF or (Counter >= 5)
    tblGraphBudgetSales.Append
    tblGraphBudgetSales.SalesGroup = tblSalesGroup.SalesGroupCode
    tblGraphBudgetSales.Budget = FormatNumber(tblSalesGroup.Budget, "$0.00")
    tblGraphBudgetSales.Sales = FormatNumber(tblSalesGroup.Sales, "$0.00")
    Counter = Counter + 1
    tblGraphBudgetSales.Save
    tblSalesGroup.Next
  Loop
  tblGraphBudgetSales.AllowInsertDelete = False
  graphBudgetSales.RefreshData
End Sub

22. Change the Caption to Budget vs Actual - Top 5 Sales Groups for Last 12 Months.