In any new custom reports pulling the SalesPrice field (which is the unit price) from the Non-Inventory Item - the value of the SalesPrice column is being multiplied by the number of transactions in the date range of the report.
Since Sales Price and Purchase price columns are UNIT prices these should always simply appear as the price per unit and not be multiplied.
For example: In the below screenshot:
The first column is NonInventoryItem.SalesPrice, the second is Qty and the third amount.
The
14 were sold at a unit price of $5 for a total amount of $70;
However the unit price column is showing $55 as there were 11 (11 x 5 = 55) transactions that resulted in the sale of 14 units.
This is clearly wrong. The unit price columns coming from the Non-Invenotry items (and Likely Inventory items too although I haven’t build a report using them yet - should always be the Unit Price)
Nothing is being multiplied. The report is summing the numbers for the variables, which you have grouped.
In other words, 55 appears because there were 14 separate transactions at $5 each that were summed up. Likewise, -14 appears because there were 14 separate transactions, each subtracting 1, that were summed. Similarly with the amounts.
A bad choice of wording on my part. Yes, technically there are indeed 11 transactions and therefore $55 is indeed the sum is 5+5+5+5+5+5+5+5+5+5+5 = 55 and not the product of 11 x 5 = $55.
I do understand that the group by is essentially creating a select sum(non-inventory-items.salesprice)
However, the point remains there is no way to show the unit price when grouping by item. Remember that this price field is being pulled from the Master Data table (non-inventory items) and is a unit price. 55 is a meaningless number.
So either we need to be able to add a min, max, first, average etc. to the select statement (select first(non-inventory-item.salesprice) or we need to recognize that price fields from the master data are not transaction based values to be summed.
In summary, I agree 100% that the current result (55) is a sum not a product. However Unit Price should either never be summed or the select should allow for qualifiers (min, max, average etc).
Honestly, I simply want to get a price list for my non-inventory items. Cannot be done.
I initially did a select where all select lines were from the non-inventory item (master data) table;
This resulted in multiple records for each item, one per transaction, so I was ‘forced’ to group by item to get back to a single record per item;
Having done that it summed (not multiplied) the unit price to a meaningless $55.
I added the Qty and amount simply to highlight the issue (these are transaction based and make sense to sum for a group by).
I would email price lists directly from Manager often. That capability is now gone with the new reports.
Basically - Cutting to the root of the issue. Give us either:
Ability to select and report on master data without it being transaction driven (by unspecified behind the scenes joins) or
The ability to specify min, max etc. (as you could in SQL) if the base select is always forced (behind the scenes) to transaction level even when we don’t ever select a single field from transaction tables and we have no choice but to use a group statement.
I won’t share or trouble you with the ridiculous amount of grouping that had to be done to produce a simple Member Roster (customer listing) report to get it to one record per customer rather than repeating the customer record for every transaction.
