I have a table in the reporting module that is creating a material cut list for a machine. First I wrote a query that selected all the parts needed for this machine. Then I ordered them by width, so all pieces of the same width are cut at the same time. The problem is that a 6 inch long piece could be on the list, under the same width, 20 times. This makes the report like 15 pages long. This should be an easy fix by grouping by lengths right? Wrong. If I group by length, then it groups all lengths together no matter what the width is, and data is lost. I could hard code in a “where width = 1.375” in the query but then I have to a separate query for every single width. Plus the info is coming from a db table that changes data, so I don’t know which widths or the number of distinct widths will be in the db table. Below is a query with a hard coded width, but I want it to perform this query for each distinct width in the db table. I tried using a query tag listing the widths, and using that as a dataset parameter but it doesn’t work. Thoughts?
SELECT material, width, length as LENGTH, FORMAT(length/12, 2) as LinFeet, operation FROM mill_workorders WHERE machine = "OPTIMIZER" and width = 1.375 and po = ? GROUP BY length ORDER BY width ASC, LenFeet