I created a BAQ that simply takes all of our part numbers (grouped by part) from the [OrderDtl] table and adds 3 calculated fields to determine total orders, total quantity from those orders and the last date for which the part was ordered. Fairly straightforward. I tested the BAQ to make sure that all of these calculated fields adjusted as expected when a start and end date were given (which narrowed the scope of returned records by [OrderHed.OrderDate]).
So, if I parameterized with a start date of 01/01/2018 and an end date of 12/31/2018 – then I had better not see a “last ordered” date greater than 12/31/2018; and certainly, total orders and quantities that are much lower as compared to the entire history of the company’s overall sales.
The client requesting this prefers to have it in a tracker where he can simply select the start and end dates as filters and then refresh it to see the results. However, I’m having some difficulty working with the dates – because as soon as dates enter the BAQ as hard data points (so the tracker can use them), the BAQ wants to group on them, which I can’t do, of course.
I have not included dates in the BAQ because as soon as that happens, the BAQ is expecting me to group on that data point… which can’t happen, because I don’t want to return total orders and total quantities by all possible dates in the [OrderDtl] table. I only want to see these values by [PartNum].
When I don’t use the date parameter, I get the total history of sales for all parts. When I introduce a date parameter, I can see (as in the example above), sales totals for everything starting Jan 1st of 2019.
In a tracker situation, I can’t do this (because it expects a date field to parameterize on – and then the underlying BAQ wants to group off of that date).
If your range is 1/1/2018 to 12/31/2018, you only want records in that range included in your calculations. With the calculation being MAX(OrderDtl.NeedBy) (as an example)
So the source data of
With a desired criteria of 1/1/2018 to 12/31/18 should give
That’s exactly correct. You have your date range parameters limiting everything to 2018… therefore, you should only have returned calculated criteria on records associated with 2018 — precisely as you have it in your example.
Definitely not out of the question. We can only work within the limitations of the system. So, I’ll need to explain the situation and let the client know that the parameterized BAQ is the only way he’s going to be able to pull back the data he requires in the manner he wishes.
Thanks for all the feedback, Calvin. Your input is always terrific.
I’m not going to go gently into that night. I’m try various methods. I’ll come up with a solution, but it will be worse than using the Parameters
