MB Tutorial: IC Product Quantities by Location Pivot Table

This tutorial shows you how to produce a report on IC Product quantities by location. You can use MaxBasic to create a Pivot Table with this information, then format the table.

1. To begin, create a SQL query to extract the data. Go to Navigator > Setup > SQL Query Builder.
2. Select the ICPROD (IC Products) and ICQTY (IC Quantities) tables. These will be joined by the ProductCode fields.
3. Click to select the following fields:

   ICPROD.ProductCode

   ICPROD.Description

   ICQTY.LocationCode

   ICQTY.QuantityAvailable

   The fields selected will be displayed in the grid at the bottom of the SQL Query Builder form.

4. Go to the SQL tab. The SQL code will be displayed. Add these lines to the end of the SQL code:

   WHERE ICProd.NonDiminishing = False
AND ICProd.ProductCode BETWEEN :FromCode AND :ToCode
Order By ICProd.ProductCode , ICQty.LocationCode

   This will only select products that are diminishing (kitsets will not be included), and will allow you to input a range of Product Codes to select from. It will also order the results by the Product Code, then Location Code.

5. Go to the SQL for Script tab. This contains the query in a format that can be inserted into a script. Copy the text in this tab.
6. Go to Accredo > Script > Script Editor to open the Script Editor.
7. Type the following in the Script Editor:

     SQL =

   Then Paste the SQL Query into the window, on the same line as SQL = . You now have the SQL query so it can be used by the MaxBasic code. The script will look like this:

   SQL = "SELECT ICPROD.ProductCode " & _

   " ,ICPROD.Description " & _

   " ,ICQTY.LocationCode " & _

   " ,ICQTY.QuantityAvailable " & _

   "FROM ICPROD " & _

   " INNER JOIN ICQTY ON ICPROD.ProductCode=ICQTY.ProductCode " & _

   " WHERE ICProd.NonDiminishing = False " & _

   "AND ICProd.ProductCode BETWEEN :FromCode AND :ToCode " & _

   "Order By ICProd.ProductCode , ICQty.LocationCode "

8. You can now add the code to create a scripted form, to input the Product Code range. The caption will be Locations Cross Tab Selections. Type the following after the SQL Query:

   'Report Selections
   Dim scForm as Object
   scForm = CreateObject("Accredo.ScriptedForm")
   scForm.Caption = "Locations Cross Tab Selections"

9. Add controls to let the user input the From and To Product Codes:

   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", "")

10. Add code to run the scripted form, and get the input From and To Product Codes:

    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

11. Add code to execute the SQL Query above, using the input From and To Product Codes, and capture the results in a table.

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

12. Add code to create a pivot table based on the table created above. The ProductCode and Description fields will not be pivoted. The LocationCode will be pivoted, and the sum of the QuantityAvailable will be used as the pivot value field. The final parameter True sets the pivot table to contain row totals. See also PivotTable:

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

13. Add code to set the display width and labels for fields in the pivot table:

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

14. Add code to change the display for fields with a QuantityAvailable label to Quantity|Available, so the label will be displayed over two lines:

    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

15. Add code to have the fields display a total:

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

16. Finally, add a report to display the pivot table:

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

17. Click Save... (Ctrl+S) to save the script.
18. Click Run (Alt+R) to test the script. The pivot table containing IC Product Quantities by Location is shown as a report.

The entire script for this tutorial is at MB Script: IC Product Quantities by Location Pivot Table. You can cut and paste this script into the script editor to create this report.