MB Script: IC Products and Prices Pivot Table

The following is the entire script for the MB Tutorial: IC Products and Prices Pivot Table. Refer to the tutorial for information on how to use this script.

Dim tblCOPRICE as Object
tblCOPRICE = OpenTable("COPRICE")
tblCOPRICE.IndexName = "Line"

'Report Selections
Dim scForm as Object
scForm = CreateObject("Accredo.ScriptedForm")
scForm.Caption = "Report Selections"
Dim ProductFromControl as Object
Dim ProductToSibling as Object
ProductFromControl = scForm.InputCode("ProductFrom","ICPROD", "Product - From", "", False, False, True)
scForm.SetValue("ProductFrom", "")
ProductToSibling = scForm.InputCode("ProductTo","ICPROD", "Product - To", "", False, False, True)
ProductToSibling.DittoSibling = ProductFromControl
scForm.SetValue("ProductTo", "")
Do
  If Not scForm.Execute Then
    scForm.ShowStatusHint("Cancelled")
    Abort("", True)
  Else
    ProductFrom = scForm.GetValue("ProductFrom")
    ProductTo = scForm.GetValue("ProductTo")
    Exit Do
  End If
Loop
'Default empty selection to start/end
If ProductFrom = "" Or ProductTo = "" then
  ICPROD = OpenTable("ICPROD")
  ICPROD.IndexName = "Product"
  If ProductFrom = "" Then
    ICPROD.First
    ProductFrom = ICPROD.ProductCode
  End If
  If ProductTo = "" Then
    ICPROD.Last
    ProductTo = ICPROD.ProductCode
  End If
End If

Dim SQL as String
SQL = "SELECT ICPROD.ProductCode " & _
" ,ICPROD.Description " & _
" ,ICPROD.SalesGroupCode " & _
" ,ICPROD.StockGroupCode " & _
" ,ICSELL.PriceCode " & _
" ,ICSELL.Price " & _
" ,COPRICE.LineNo " & _
"FROM ICPROD " & _
" INNER JOIN ICSELL ON ICPROD.ProductCode=ICSELL.ProductCode " & _
" INNER JOIN COPRICE ON COPRICE.PriceCode=ICSELL.PriceCode " & _
"Where ICProd.ProductCode Between :ProductFrom and :ProductTo " & _
"ORDER BY ICPROD.ProductCode " & _
" ,COPRICE.LineNo "

Dim tblPrices as Object
tblPrices = ExecuteSQL(SQL,ProductFrom,ProductTo)
'BrowseDataset(tblPrices)

Dim tblPivot as Object
tblPivot = PivotTable(tblPrices, "ProductCode;Description;SalesGroupCode;StockGroupCode", "LineNo", "Sum(Price)",False)
'DocumentObject(tblPivot)

TotalFields = tblPivot.Fields.Count
tblPivot.Fields.Item("ProductCode").DisplayWidth = 20
tblPivot.Fields.Item("Description").DisplayWidth = 40
For x = 4 to TotalFields - 1
  Dim tblCOPRICE as Object
  tblCOPRICE = OpenTable("COPRICE")
  tblCOPRICE.IndexName = "Line"
  'Report Selections
  Dim scForm as Object
  scForm = CreateObject("Accredo.ScriptedForm")
  scForm.Caption = "Report Selections"
  Dim ProductFromControl as Object
  Dim ProductToSibling as Object
  ProductFromControl = scForm.InputCode("ProductFrom","ICPROD", "Product - From", "", False, False, True)
  scForm.SetValue("ProductFrom", "")
  ProductToSibling = scForm.InputCode("ProductTo","ICPROD", "Product - To", "", False, False, True)
  ProductToSibling.DittoSibling = ProductFromControl
  scForm.SetValue("ProductTo", "")
  Do
    If Not scForm.Execute Then
      scForm.ShowStatusHint("Cancelled")
      Abort("", True)
    Else
      ProductFrom = scForm.GetValue("ProductFrom")
      ProductTo = scForm.GetValue("ProductTo")
      Exit Do
    End If
  Loop
  'Default empty selection to start/end
  If ProductFrom = "" Or ProductTo = "" then
  ICPROD = OpenTable("ICPROD")
  ICPROD.IndexName = "Product"
  If ProductFrom = "" Then
    ICPROD.First
    CurrentLabel = tblPivot.Fields.Item(x).DisplayLabel
    If tblCOPRICE.FindExact(val(CurrentLabel)) then
      tblPivot.Fields.Item(x).DisplayLabel = tblCOPRICE.PriceCode
    End If
    tblPivot.Fields.Item(x).format = "0.00"
Next x

'BrowseDataset(tblPivot)

Dim Report as Object
Report = CreateReport(tblPivot, "IC Products and Prices")
Report.Destination = "Screen"
Report.Run