JTRTech is correct, but there is more to consider to make it work.
In your field selection, if you want to use .sum
(or other aggregates such as .avg
) on 1 (or more) of the fields, then the other fields need to be “groupable” or omitted entirely.
That means your other fields must either be omitted completely, or at the very least, they need to return the exact same value for every record/row of data. For example, you cannot use .asc
on 1 field and .sum
on another field in the same list. So this will not work:
{REPORT CONSUMPTION DETAILS:C.Name.asc,C.Cost,C.InStock.Sum,C.Consumption,C.Added,C.Prediction,C.PhysicalInventory: ...}
You want the Sum of C.InStock
but the other fields violate the aggregate grouping. These fields will all return a different value for each row, so no Sum can be made:
C.Name.asc (even without .asc, this still returns a different value for every record/row)
C.Cost
C.Consumption
C.Added
C.Prediction
C.PhysicalInventory
You can omit all of those fields, or do an aggregate (.sum
or .avg
) or a limit (.min
or .max
) on each of them, except for C.Name
because it is a STRING that cannot be summed or averaged (you could do min/max on C.Name, but that doesn’t make much sense here).
One of these would work:
{REPORT CONSUMPTION DETAILS:C.InStock.Sum: ...}
{REPORT CONSUMPTION DETAILS:C.Cost.sum,C.InStock.Sum,C.Consumption.sum,C.Added.sum,C.Prediction.sum,C.PhysicalInventory.sum: ...}