How to Export a Custom CSV Report using SQL

How to Export a Custom CSV Report using SQL

Cube-IQ can export a Custom SQL Report to a CSV file

A CSV file can be exported from Cube-IQ using SQL. This is useful for when you need to create a report of a Load for Excel or to read it into another application.

Custom Export commands can be saved as different .sql files, and reopened to be Executed when needed.

Use the Custom Export using SQL feature to write the Command

The Custom Export using SQL is the feature that executes SQL command to a CSV file, it is also where SQL commands are saved and opened as .sql files. The SQL command is written in Cube-IQ's Export (User Definable) dialog window.

Open the Export (User Definable) dialog window by: 1) click the main menu, 2) click Import & Export tab in the left panel, 3) click the Custom Export using SQL button



The top part of the Export (User Definable) dialog window is where the buttons are to open and save .sql files for the Export command, the middle area is where the SQL command is written, and the lower part of the dialog window is where the Export is saved and the command is executed.

Cube-IQ SQL Tables, Fields, and Commands

Cube-IQ uses standard SQL commands and syntax such as SELECT, SUM, JOIN, GROUP BY, and ORDER BY… etc.

There are plenty of Tables and Fields in Cube-IQ. Here are some common tables used: Loads, Products, Containers, ProductsToLoad, and Blocks

Naming of fields normally follow the convention where key fields of a table are <table name> + "id". For example, the key field for Loads, Products and Containers are LoadId, ProductId, and ContainerId. The other field names are normally their name used throughout Cube-IQ such as Length, Width, Height, Quantity, TopOnly, MaxInLayer… etc.

The Custom Export using SQL feature can reference the current Load open by using this macro substitution: '%LoadId%'

Example of a Custom CSV Export for a "Products Not Loaded.csv" report

Here is the Sample Load in Cube-IQ where there are 6 Products Not Loaded.


This SQL export command will list the LoadId, ProductId, Quantity, Loaded, and Not Loaded from the current open Load.
Note how the '%loadid%' bolded in red is the macro substitution used to reference the currently open Load.

SELECT ProductsToLoad.LoadId, ProductsToLoad.ProductId, ProductsToLoad.Quantity,   
SUM(Blocks.NumDeep*Blocks.NumWide*Blocks.NumHigh) AS Loaded,
ProductsToLoad.Quantity-SUM(Blocks.NumDeep*Blocks.NumWide*Blocks.NumHigh) AS Not_Loaded
FROM ProductsToLoad
LEFT JOIN Blocks ON ProductsToLoad.ProductId=Blocks.ProductId AND ProductsToLoad.LoadId=Blocks.LoadId
WHERE ProductsToLoad.LoadId = '%loadid%'   
GROUP BY Blocks.ProductId, Blocks.ContainerSeqNo
ORDER BY Blocks.ProductId, Blocks.ContainerSeqNo

Place the command above into the Export (User Definable) dialog window, and then click export after entering the path and filename in the Filename field.

Open the exported CSV file in Notepad or Excel to see how it looks. Not Loaded products are listed in Column E along with the other fields in the SQL Export command.


SQL Commands can be Saved and Opened to easily export custom CSV reports

Click the Save button after writing a SQL command to save it to a .sql file, or click Open to open a previously saved SQL command.






    • Related Articles

    • How to import Custom Orientations

      Products can be imported from Excel to setup Custom Orientations instead of the regular Allowed Orientations Products imported from Excel have their Allowed orientation mapped by default to "All six". All six can be changed to "As defined", or to ".. ...

    • How to avoid using costly containers unless necessary

      A rule can be added to a Container to prevent it from being used unless it is the only Container that can accommodate a Product's size. Below optimized Load is an example with two Container IDs and two Product IDs. Quantity for each Container can be ...

    • How to automatically fill containers without the guesswork using the Load Proportionally rule

      Cube-IQ can completely load 1 container automatically using the Load Proportionally rule. The Load Proportionally rule is accessed by: 1) selecting the Load Setup ribbon, 2) selecting the Loading Rules tab, and  3) clicking the Load Proportionally ...

    • How to control product stacking using the Stacking Matrix

      Cube-IQ can stack fragile products automatically on top of medium and heavy products. Below is a load where products are poorly stacked on top of each other, products have not been assigned stack codes. Green heavy box of metal pewter mugs are ...

    • How to Limit Quantities of Hazardous Products using Q-value

      Q-values can be assigned to a hazardous product to limit the Quantity of hazardous Products in one Container. Here is a Hazmat Load of 4 Green and 9 Purple hazardous Products.  All 13 Hazard Products are loaded into one Container below. However, if ...