I’m trying to create the AR invoice report that our users want at BAQ. They want only the total amount to be displayed on one line, not the invoice details. So I took InvcHead instead of InvcDtl.
But, they want this report to include the Sales category in SO. This field is in OrderDtl, and when I connect OrderDtl, the invoice line increases by the number of order lines.
Can’t we make the Sales category in this OrderDtl appear in one line of InvcHead? For reference, our Sales category is text
Thanks in advance!
If you check the “Group By” checkbox on all the fields in the query, that will reduce it to one line per InvcHead & OrderDtl record as long as all related OrderDtl lines have the same Sales Category. You can also get the same result by changing the subquery option to “Distinct”.
If you have different Sales Categories on the same InvcHead, you’ll get one row for each, and both will have the full invoice total. It’s one of the dangers on reporting data in tables with One-to-many relationship. There are a few ways around that* if you need it, but if you’re expecting all the same category per Invoice, you should be okay.
*For example: You could create an inner Subquery with OrderDtl with only OrderNum and Sales Category, using the same Group By method. Then connect that subquery to InvcHead. You’d still use the groupby on all fields except Sales Category. Instead of adding the Sales Category, add a calculated field (type varchar) with the following SQL code:
STRING_AGG( SubQuery2.OrderDtl_SalesCat, ', ')
This would give you only a single row per invoice, but have a list of sales categories if there are multiple per invoice.
Thank you. With your help, I have done some additional work on BAQ. However, I received the message below. Could you please help me find out what the problem is?
