MB Script: IC Product Quantities by Location Pivot Table

The following is the entire script for the MB Tutorial: IC Product Quantities by Location Pivot Table. Refer to the tutorial for information on how to use this script.

SQL = "SELECT ICProd.ProductCode " & _
" ,ICPROD.Description " & _
" ,ICQTY.LocationCode " & _
" ,ICQTY.QuantityAvailable " & _
"FROM ICPROD " & _
" JOIN ICQTY ON ICPROD.ProductCode=ICQTY.ProductCode " & _
"WHERE ICProd.NonDiminishing = False " & _
"AND ICProd.ProductCode BETWEEN :FromCode AND :ToCode " & _
"Order By ICProd.ProductCode , ICQty.LocationCode "

'Report Selections
Dim scForm as Object
scForm = CreateObject("Accredo.ScriptedForm")
scForm.Caption = "Locations Cross Tab Selections"
Dim FromCodeControl as Object
Dim ToCodeSibling as Object
FromCodeControl = scForm.InputCode("FromCode","ICPROD", "Product - From", "", False, False, True)
scForm.SetValue("FromCode", "")
ToCodeSibling = scForm.InputCode("ToCode","ICPROD", "Product - To", "", False, False, True)
ToCodeSibling.DittoSibling = FromCodeControl
scForm.SetValue("ToCode", "")
Do
  If Not scForm.Execute Then
    Abort("Report cancelled by user.")
  Else
    FromCode = scForm.GetValue("FromCode")
    ToCode = scForm.GetValue("ToCode")
    Exit Do
  End If
Loop
If FromCode = "" And ToCode = "" then
  FromCodeSelect = False
Else
  FromCodeSelect = True
End If
'Default empty selection to start/end
If FromCode = "" Or ToCode = "" then
  ICPROD = OpenTable("ICPROD")
  ICPROD.IndexName = "Product"
  If FromCode = "" Then
    ICPROD.First
    FromCode = ICPROD.ProductCode
  End If
  If ToCode = "" Then
    ICPROD.Last
    ToCode = ICPROD.ProductCode
  End If
End If

Dim tblQty as Object
tblQty = ExecuteSQL(SQL,FromCode,ToCode)

Dim tblPivot as Object
tblPivot = PivotTable(tblQty, "ProductCode;Description", "LocationCode", "Sum(QuantityAvailable)", True)

TotalFields = tblPivot.Fields.Count
tblPivot.Fields.Item("ProductCode").DisplayWidth = 20
tblPivot.Fields.Item("Description").DisplayWidth = 40
tblPivot.Fields.Item(TotalFields - 1).DisplayLabel = "Total|Available"

For x = 2 to TotalFields - 2
  CurrentLabel = tblPivot.Fields.Item(x).DisplayLabel
  tblPivot.Fields.Item(x).DisplayLabel = Replace(CurrentLabel, "QuantityAvailable", "|Quantity|Available")
  tblPivot.Fields.Item(x).DisplayWidth = 10
Next x

For x = 2 to TotalFields - 1
  tblPivot.Fields.Item(x).total = True
Next x

Dim Report as Object
Report = CreateREport(tblPivot, "IC Quantity by Location")
Report.Destination = "Screen"
Report.Run