REPORT ORDER DETAILS Sum Error

V5 Issue
1

Hello @emre

Not sure if I have done something wrong but this syntax is very simple yet the “.sum” clause is not working?

Oexacttotalsum.JPG2187×500 125 KB

Hopefully I can change this to a question and break the trend :confused:
Your comments needed, thanks.

[Detailed Sales Product by Customer:6,5, 6, 3, 3, 1, 2]
>CUSTOMER|ITEM GROUP|ITEM|PORTION|QTY|TOTAL
{REPORT ORDER DETAILS:EC.Customer.Full Name.asc,O.ItemGroup,O.MenuItemName,O.PortionName,O.Quantity,O.ExactTotal:(ODI=True) && (TCET=Customers)}
>> TOTAL|||||$[=F(TN('{REPORT ORDER DETAILS:O.ExactTotal.sum:(ODI=True) && (TCET=Customers)}'),'#,#0.00')]

EDITED:

Ok Emre I know what it is doing, by the use of “.asc” it is eliminating duplicates and therefore SAME ORDER LINES are being removed. The use of “.asc” I thought would be to SORT ONLY and not sort + remove duplicates as customers will reorder the same Drink.

image.png1851×718 47.8 KB

Not sure is this is System wide?

Remove the “.asc” keyword and it works as expected but no sorting :frowning:

image.png1822×506 34.8 KB

Your further thoughts.
Need to know if I need to edit all reports or is is just restricted to this TAG?

2

Hi @emre

I also need to know if TAG like this below are effected?

>Customer Account
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum.desc:(PT=Customer Account) && Payment.Amount > 0:{0}|{1}|${2}:}

Therefore is payments are the SAME AMOUNT will they be omitted and only 1 amount shown, there my total not using “.desc” will be out as well :frowning:

Please let us know, thanks.

3

Think like SQL.

As soon as you use aggregate like SUM, you need to do GROUP BY other fields, or aggregate them as well with SUM, or COUNT, etc.

If you don’t SUM the other fields, they will be GROUP BY’d which has the appearance of “removing duplicates”.

4

That above syntax is a bit misleading I am really referring to the “.desc” or “.asc” - forget about SUM as this works ok for our reports. The Sorting by itself removes duplicates which is undesired as 2 drink orders by same customer for same drink need to be shown.

5

I wonder if there is a bug in there somewhere when it comes to ASC/DESC?

For example, maybe it is treating ASC (or any trailing .something) as though it is an aggregate when in fact this is not always the case. SUM and COUNT are aggregates, but ASC, DESC are not. Maybe that is the bug?

P.S. sorry, I just blurted out my first answer without actually looking at your Report to see what it was doing, because it was the first thing that popped in to my head when it comes to reporting with SUM that most people don’t understand. Still a good point to keep in mind.

1 Like
6

In the mean-time, though it might not be exactly what you want, maybe try the following to see if it adds up properly:

{REPORT ORDER DETAILS:EC.Customer.Full Name.asc,O.ItemGroup,O.MenuItemName,O.PortionName,O.Quantity.Sum,O.ExactTotal.Sum:(ODI=True) && (TCET=Customers)}
2 Likes
7

Ok a possibility but I think the Customer desired to se each order but then again summing O.Quantity why waist the paper :grinning: Nice could be a workaround for the time being while Emre emerges from wherever he is :confused:

EDIT:
Tested and works well! Not sure if I should mark it as a solution in case there is an issue and it just gets pasted over? Thanks as always Q!

1 Like