FD Tutorial: Dashboard Product Sales

You can modify the FD Tutorial: Dashboard Analysis of Expenses to instead return product sales. This tutorial requires the IC Module.

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

4. Name the table tblGrpProducts.

6. Add the following fields to the table:

No.	Name	Display Label	Type	Domain	Size	Width	Visible
1	ProductCode	Product	String	IC Product Code	28	15	Selected
2	Description	Description	String	Product Description	60	22	Selected
3	CostOfSalesYTD	CostYTD	Float	Amount		12	Selected
4	SalesValueYTD	SalesYTD	Float	Amount		12	Selected
5	GrossProfitYTD	GrossProfitYTD	Float	Amount		14	Selected
6	MarginYTD	MarginYTD	Float	Percent1		11	Selected
7	SalesQtyYTD	SalesQtyYTD	Float	Quantity	4	12	Selected
8	CostOfSalesPTD	CostPTD	Float	Amount		12
9	SalesValuePTD	SalesPTD	Float	Amount		12
10	GrossProfitPTD	GrossProfitPTD	Float	Amount		14
11	MarginPTD	MarginPTD	Float	Percent1		11
12	SalesQtyPTD	SalesQtyPTD	Float	Quantity		12

11. Name the grid gridProdSales.

13. Set the table to tblGrpProducts.

14. Change the Name of the navigator to navProdSales.

17. Set the Table to tblGrpProducts. This links the grid to the memory table created previously. Set the Hook Control to gridProductSales, to link the navigator to the grid.

18. Enter the following code, to extract the sales product and populate the memory table, and display it in the grid:

Sub RefreshProducts
  Dim tblProducts as Object
  SQLPROD = "SELECT ICPROD.ProductCode as Product " & _
  " ,ICPROD.Description " & _
  " ,ICPROD.CostOfSalesYearToDate as CostOfSalesYTD " & _
  " ,ICPROD.SalesValueYearToDate as SalesValueYTD " & _
  " ,(ICPROD.SalesValueYearToDate - ICPROD.CostOfSalesYearToDate) as GrossProfitYTD " & _
  " ,ICPROD.MarginYearToDate as MarginYTD " & _
  " ,ICPROD.SalesQuantityYearToDate as SalesQtyYTD " & _
  " ,ICPROD.CostOfSalesPeriodToDate as CostOfSalesPTD " & _
  " ,ICPROD.SalesValuePeriodToDate as SalesValuePTD " & _
  " ,(ICPROD.CostOfSalesPeriodToDate - ICPROD.SalesValuePeriodToDate) as GrossProfitPTD " & _
  " ,ICPROD.MarginPeriodToDate as MarginPTD " & _
  " ,ICPROD.SalesQuantityPeriodToDate as SalesQtyPTD " & _
  "FROM ICPROD " & _
  "WHERE (ICPROD.Inactive = False) " & _
  "AND (ICPROD.SalesValueYearToDate > 0) " & _
  "ORDER BY GrossProfitYTD DESC "
  tblProducts = ExecuteSQL(SQLPROD)
  tblProducts.First
  tblGrpProducts.Empty
  tblGrpProducts.AllowInsertDelete = True
  Do Until tblProducts.EOF
    tblGrpProducts.Append
    tblGrpProducts.ProductCode = tblProducts.Product
    tblGrpProducts.Description = tblProducts.Description
    tblGrpProducts.CostOfSalesYTD = tblProducts.CostOfSalesYTD
    tblGrpProducts.SalesValueYTD = tblProducts.SalesValueYTD
    tblGrpProducts.GrossProfitYTD = tblProducts.GrossProfitYTD
    tblGrpProducts.MarginYTD = tblProducts.MarginYTD
    tblGrpProducts.SalesQtyYTD= tblProducts.SalesQtyYTD
    tblGrpProducts.CostOfSalesPTD = tblProducts.CostOfSalesPTD
    tblGrpProducts.SalesValuePTD = tblProducts.SalesValuePTD
    tblGrpProducts.GrossProfitPTD = tblProducts.GrossProfitPTD
    tblGrpProducts.MarginPTD = tblProducts.MarginPTD
    tblGrpProducts.SalesQtyPTD= tblProducts.SalesQtyPTD
    tblGrpProducts.Save
    tblProducts.Next
  Loop
  tblGrpProducts.AllowInsertDelete = False
  tblGrpProducts.First
End Sub

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

Sub OnCreate
RefreshProducts
End Sub

22. Add the following code to enable the Open and Print Buttons:

Sub OnNavProd(Sender as Object, Button as Number, ByRef Handled as Boolean)

Dim Report as Object

If Button = ebOpen Then

Dim Form1 as Object

Form1 = CreateObject("Accredo.ICProductForm")

Form1.Find(tblGrpProducts.ProductCode )

Form1.GraphSelection = "Range of Periods"

Form1.GraphField2 = ""

Form1.GraphField = "SalesValue"

End If

If Button = ebPrint Then

Report = CreateReport(tblGrpProducts, " Product Sales For Last 12 Months ")

Report.Destination = "Screen"

Report.Run

Handled = True

End If

End Sub

23. Go to the layout tab. Select the navProdSales component. Go to the Events tab. Set OnClick to OnNavProd.

25. Change the link label Caption to Product Sales.

27. Add code to go to the IC 13 Period Sales Report when the Link Label is clicked. Go to the Code tab, and enter the following code:

Sub OnLinkProduct

Dim Form1 as Object

Form1 = CreateObject("Accredo.ICThirteenPeriodSalesReport")

Form1.Layout = "13 Period Sales"

End Sub

28. Go to the layout tab. Select the Link Label component. Go to the Events tab. Set OnClick to OnLinkProduct.