MB Tutorial: IC Products and Prices Pivot Table

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

1. Go to Accredo > Script > Script Editor to open the Script Editor.
2. To begin, open the COPRICE table. Enter the following code:

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

3. Create a scripted form to input the report selections.The caption will be Report Selections. Enter the following code:

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

4. Add code to run the form:

   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

5. Add code to default the Product From and Product To if they are not entered:

   '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

6. Create a SQL query to extract the data. Go to Navigator > Setup > SQL Query Builder.
7. Select the ICPROD (IC Products) and ICSELL (IC Sell Price) and COPRICE (CO Price List) tables. ICPROD and ICSELL will be joined by the ProductCode field. ICSELL and COPRICE will be joined by the PriceCode field.
8. Select the following fields:
   • ICPROD.ProductCode
   • ICPROD.Description
   • ICPROD.SalesGroupCode
   • ICPROD.StockGroupCode
   • ICSELL.PriceCode
   • ICSELL.Price
   • COPRICE.LineNo
9. Go to the SQL tab. The SQL code will be displayed. Add these lines to the end of the SQL code:

   Where ICProd.ProductCode Between :ProductFrom and :ProductTo
ORDER BY ICPROD.ProductCode
,COPRICE.LineNo

   This will allow you to input a range of Product Codes to select from. It will also order the results by the Product Code, then Line No.

10. 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.
11. Type the following in the Script Editor:

      SQL =

    Then Paste the SQL Query into the window. You now have the SQL query so it can be used by the MaxBasic code.

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

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

13. Add code to create a pivot table based on the table created above. The ProductCode, Description Sales Group Code and Stock Group Code fields will not be pivoted. The Line No will be pivoted, and the sum of the Price will be used as the pivot value field. The final parameter False sets the pivot table to not contain row totals. See also PivotTable:

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

14. 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
    For x = 4 to TotalFields - 1
      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

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

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

16. Click Save... (Ctrl+S) to save the script.
17. 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 Products and Prices Pivot Table. You can cut and paste this script into the script editor to create this report.