FD Tutorial: Dashboard Budget vs Actual Expenses

Using the Form Designer, you can create a dashboard to show graphs and list key figures for your company. This tutorial requires the AP Module.

This tutorial will show you how to display the Budget vs Actual for the Top 5 Expenses for the last 12 Months.

Note: You can also use this tutorial to show the Budget vs Actual for the Top 5 Sales Groups for the last 12 Months. See FD Tutorial: Dashboard Budget vs Actual Sales Groups.

1. Go to Navigator > Setup > Form Designer.
2. Click the Memory Table button, then click inside the form on the right. A Memory Table object will be added to the form. The memory table object will not be displayed when the form runs, so it doesn't matter where you put it.
3. Click the memory table icon on the form. The properties will be listed on the left.
4. Change the Name of your table to tblGraphBudgetExp.
5. Double-click the Memory Table icon on your form. The FD Memory Table Designer will open.
6. Add the following fields to the table:

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

7. Click Save. The FD Memory Table Designer will close.
8. Set the Active property of the Memory Table to True.
9. Click the Graph button, then click inside the form on the right. A Graph object will be added to the form.
10. Click the Graph on the form, to show the properties on the left.
11. Change the Name of the graph to graphBudgetExpense.
12. To change the display of the graph, set the following properties:

    Property	Value	Description
    BackWallColor	clWhite	Click the [...] button in BackWallColor, and click white, to set the graph back wall to white.
    Height	132	Sets the graph height. You can also drag to change the height.
    Left	10	Moves the graph to the left of the form.
    ShowGridLines	False	Hides the grid lines.
    Top	32	Sets the graph position from the top. You can also drag to change the position.
    Width	600	Sets the graph width. You can also drag to change the width.

13. Set the Table to tblGraphBudgetExpense. This links the Graph to the memory table created previously. Set the XLabelFieldName to ExpenseCode.
14. Double-click the graph in the form. The FD Graph Editor opens.
15. Add the following fields to the Graph Editor:

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

16. Click Save. The FD Graph Editor will close.

    You can now write the MaxBasic code to populate the Memory Table and graph.

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

    Sub RefreshBudget
      Dim ExpenseCodes as String
      PeriodToUse = CurrentPeriod("AP")
      StartPeriod = AddPeriod(PeriodToUse, -12,True)
      EndPeriod = AddPeriod(PeriodToUse, -1,True)
      SQLExpenseCode = "SELECT Min(APANLBAL.PeriodID) as PeriodID " & _
      " ,Min(APANLBAL.AnalysisCode) as ExpenseCode " & _
      " ,Sum(APANLBAL.Budget) as ExpBudget " & _
      " ,Sum(APANLBAL.PeriodActivity) as Expense " & _
      "FROM APANLBAL " & _
      "WHERE APANLBAL.PeriodID Between :StartPeriod AND :EndPeriod " & _
      "Group BY ExpenseCode " & _
      "Order BY Expense DESC "
      tblExpenseCode = ExecuteSQL(SQLExpenseCode,StartPeriod,EndPeriod)
      Counter = 0
      tblGraphBudgetExp.empty
      tblGraphBudgetExp.AllowInsertDelete = True
      tblExpenseCode.First
      Do Until tblExpenseCode.EOF or (Counter >= 5)
        tblGraphBudgetExp.Append
        tblGraphBudgetExp.ExpenseCode = tblExpenseCode.ExpenseCode
        tblGraphBudgetExp.ExpBudget = FormatNumber(tblExpenseCode.ExpBudget, "$0.00")
        tblGraphBudgetExp.Expense = FormatNumber(tblExpenseCode.Expense, "$0.00")
        Counter = Counter + 1
        tblGraphBudgetExp.Save
        tblExpenseCode.Next
      Loop
      tblGraphBudgetExp.AllowInsertDelete = False
      graphBudgetExpense.RefreshData
    End Sub

18. Create a routine called OnCreate to run when the form is created. This will call the RefreshBudget sub-routine. Enter the following code:

    Sub OnCreate
      RefreshBudget
    End Sub

19. Click the Layout tab. Click on the form, or select the frmFDForm component.
20. Go to the Events tab. Set OnCreate to OnCreate.
21. You can add a label as a title to the graph. Click the Label button, then click on the form.
22. Change the Caption to Budget vs Actual - Top 5 Expenses for Last 12 Months.
23. You can change label size, position, font, colour or other properties.
24. Click Save Form (Ctrl+S).
25. Click Run (Ctrl+R) to test the form.