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
This is exactly why we allow you to do nested queries in reporting. Each sub-query is run once for each row in the query above it.
You could also group by both length and width and use the count() aggregate function. Many ways to skin this…
I am using nested queries, and when I tried to query the widths first, then run the select query afterwards it wouldn't work. I will go through the logic again tomorrow, and see if I missed something
I got it to work by grouping, not sure what I was doing wrong the first 100 times, but I may have been grouping in the wrong order. The only thing I lack now is how to add the subtotal of board feet and linear feet for each width.
When I was using hard coded widths, (a separate query for each width), I made each query a table in a table group and used the summary row to add the subtotals. Now that it is one query, how can I make it insert the subtotals after each width ie after 1.375" wide then after 1.75" wide …… I need a total bdft and lnft of all widths at the end, which is easy using the summary portion of the table. Just not sure on the subtotals within each width. Thanks
One of my reports only has enough data to fill half a page, but the module has 5 pages in preview mode displaying page one 5 times. Any ideas?
I basically had to remake the report for it to get it to work correctly 
Solved by using data key grouping…case closed. Thanks everyone
1 Like
