Hello All - Im trying to create a BAQ where the user can look up a Raw Material part number and see the total amount spent per Vendor. In other words, we might purchase the same material from multiple Vendors and they would like to see the total spent per Vendor. I know I have to do a Group By VendorID or VendorNum but I’m not quite sure how to do that. So if I have two PO’s from the same Vendor I want to add them together for the Total Spent.
I’m a little confused as to how Grouping works. Do I need to create a Subquery? I use the Business Activity Query Designer and know little SQL.
It’s a little bit more than just grouping. If you want a total spent by Part, by vendor… then you need to add the PartNum & Part Description (End Users rarely have all the Part Numbers memorized - and thus description is more meaningful to them), VendorID and Name (Vendor Num doesn’t do much for End Users, and name is a good inclusion); then you check the Group By checkbox for all of those fields, and add a calculated field that aggregates the total purchase cost (Use Sum).
Aggregate fields require all non-aggregated fields to be groupings.
Sometimes you need to pull the raw data into a subquery, and then perform your aggregate at the Top Level; other times you want to aggregate in the subquery and pull that into the top level, depending on how you are ultimately manipulating the data.
The groupings occur in the order you place the fields into the BAQ; so in this case you want to group by the part first, and then by the vendors. Since each part only has one description, and each vendor has only one name, you can get away with pulling all of them into where you are grouping.
Usually you have to branch things into subqueries where you eventually pull additional information in that would otherwise result in additional groupings if aggregated in a single query.
To avoid duplicate records, you want to get data down to a single row that can then be joined via common fields from all subqueries. Thus, that ultimately determines where aggregates are performed when constructing more complex BAQ’s.
Whether you are summing up PO’s or AP Invoices, never include the PONum\Line\Release or InvoiceNum\Line or else you’ll end up just getting an aggregate for each individual record.
Edit:
You can then set parameters for dates in which whoever is running the dashboard can set the date range - and setup criteria for the table that the costs are being pulled from = the parameters you set. You don’t want the dates in the display fields, otherwise the data will aggregate by date as well. You only want to filter by the dates via criteria/parameters.