I then added 3 inner subqueries - which all each have the ‘dataset’ and a count for 'On Time, 2nd subquery count of ‘All Recs’ and 3rd subquery count for ‘Late’.
I cant work out how to coutn records on one dataset for different field contents ie Count records where OnTime = ‘1.OnTime’ and Count records where OnTime= ‘2.Late’
I assumed I needed multiple datasets to do that record counting
Try adding a criteria to one of the tables where Company = CurrentCompany.
It simplifies the query that is sent to SQL and is often helpful when order or customer tables are included and the salesperson security is applied behind the scenes.
I gotcha, you can do it with the same data set with a “CASE…WHEN” statement in your calc field with the count statement. I think doing this would really help your run time since it would reduce the number of tables. I think another big thing affecting your time are the joins on calculated fields, which this would fix. I unfortunately don’t have all the UD fields that you do so I can’t test with your query, but I threw a little example together that shows how CASE WHEN COUNT could work for you.
The BAQ just counts the number of stock / nonstock parts per product group, which could relate to on-time / late shipments per week in your BAQ.
Subquery 1 - Initial grouping / count
Subquery 2 - Final grouping to get counts on the same line
Subquery 3 - test query to verify counts in subquery 2 are correct
I like to move the Top Level query around to help visualize what happens. CASEWHENTEST.baq (27.5 KB)
Ok, as per another post I changed the ICE version on the BAQ. Apparently still not guaranteed to work, but if that’s the case I can snip a few screenshots.
