I have a BAQ with multiple subqueries. I am trying to get the total OurShipQty for each invoice but currently if an invoice has multiple packing slips it shows the same invoice multiple times with separate quantities. Is there a way to disregard the packing slips and combine all quantities for the distinct invoice num?
On the InvoiceDetails subquery I have group by on Company,PackNum and InvoiceNum
Please attach your BAQ here for review.
In your InvoiceDtl Subquery, change PackNum to a calculated field = max(PackNum).
Turning the pack number into a calculated field allows you to get your sum at the invoice level regardless of the number of packs that were shipped.
I hope this makes sense. Good luck!
Nate
I removed InvcDtl_PackNum and added a calc field max(InvcDtl_PackNum). Do I need to change any group by’s? I am getting a SQL error
Yes, in that InvoiceDtl subquery, only group by company and invoicenum. Do not group by either of your calculated fields.
Do I need to keep the original InvcDtl_PackNum in order to add a calculated field to that column? It gives me an error "invalid column name ‘InvcDtl_PackNum’ " when I try saving the calc field
Oh yeah. Sorry, I forgot you have to remove the link to pack num from the top level baq and replace it with your calculated pack num.
EWH-Invoiced.baq (59.3 KB)
I had to remove your customer filter to get it to run on my end. Here’s the update baq.
I do not have a Pack Num on the top level though. Only on OrderNum and InvoiceDetails subquery
I ran your updated baq and I am getting the same SQL error
I got it to work using your updated baq, thanks for the quick help
Similar issue with the same BAQ: some invoices/orders are shipped on the same pack. Is there a way to not include the entire pack items and only base it on the order number entered on the dashboard tracker? Currently for one invoice there are multiple orders pulling only because those orders are listed on the same pack.
It sounds like you just want to add a criteria to the OrderNum query. Create a parameter to enter your requested order number, then filter the OrderNum query by order number using your parameter.
EWH-Invoiced.baq (59.8 KB)
So we have a dashboard where we enter an order number in the tracker and it should display all of the invoiced order detail, so we don’t want to make the whole baq based on one order number. Currently when we enter an order, it is pulling other orders details as well just because the current baq has the same pack num attached to all of the order numbers. Do you think just removing the packnum display fields from the subqueries should take care of that?
I am not sure. Can you post up your dash?
I actually ended up changing it to invchead.ordernum instead of the orderdtl.ordernum and the issue fixed itself
