Reports not Totalling Correctly

V5 Issue
1

Hi @emre
I have a concern with this syntax:

[Detailed Sales Product by Customer:7,5, 6, 3, 2, 3]
>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')]

The Report total LINE by LINE is different to the Total Line BUT the syntax is the same?

ReportError.JPG924×654 113 KB

I can only guess that there ae fewer records above because there is no field content?

I just spent 3 hours auditing reports and this report has 1,000 records for 6 days and is around $1549.82 out!

2

You said for 6 days but the report is only for few hours? Oh nm im still sleep lol

3

Yes I picked the smallest day so we did not need to see all the data like I just did!

I just modified the syntax to:

[Detailed Sales Product by Customer:7,5, 6, 3, 2, 3]
>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)}
{REPORT ORDER DETAILS:O.ExactTotal:(ODI=True) && (TCET=Customers)}
>> TOTAL|||||$[=F(TN('{REPORT ORDER DETAILS:O.ExactTotal.Sum:(ODI=True) && (TCET=Customers)}'),'#,#0.00')]

So we can see the difference between the short field list.

RESULTS:

ReportError2.JPG768×1034 95.8 KB

Ummm 17 Items Vs 19 Items…

4

So some of the items do not contain all of the fields you defined the report for. Do you know which ones?

5

That’s what I thought see my first post BUT all examples show to do totals this way??? I am doing a quick analysis now…

6

There are other tags for total. Try something other than ExactTotal

7

See I revised that wording and I did not find a {REPORT ORDER TOTAL} Tag?

8

No i mean O.ExactTotal try something else. O.Total maybe or O.TotalAmount i dont have it in front of me im still confined to phone. My modem went splat not replaced till tomorow

9

Can you PM me a backup?

1 Like
10

@emre it is the .ASC keyword, it dropping out individual records here is the results when I take out the .ASC:

FullReports.JPG738×1065 113 KB

Syntax currently is:

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

.asc may trigger a kind of grouping. Try adding .sum end of exact total and quantity.

12

I already have it OR do you mean is the detail lines?

13

He means O.ExactTotal.sum

Omg im still sleep im just gonna observe.

14

@emre nope, it reduced the results down from 19 records to 17. Here is the latest syntax:

[Detailed Sales Product by Customer:7,5, 6, 3, 2, 3]
>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.Sum:(ODI=True) && (TCET=Customers)}
{REPORT ORDER DETAILS:O.ExactTotal,O.TotalPrice,O.Total:(ODI=True) && (TCET=Customers)}
>> TOTAL|||||$[=F(TN('{REPORT ORDER DETAILS:O.ExactTotal.Sum:(ODI=True) && (TCET=Customers)}'),'#,#0.00')]

New Results now in ORder BUT 2 records less!

Reporterror3.JPG741×1028 108 KB

I have used all 3 TOTAL fields available in the bottom Line, can try to expand the details to include these fields…

Oh hang on Emre I just notice a new figure $21.20 umm that new now I got the .Sum keyword It may balance now…

15

Ok @emre what is the RULE here?

Gorden Millar has had 2 Rounds of same Beer at different times.
Jennifer Collins has had 2 Coffees at different times.

These records were dropped in the .ASC clause. What is the best practise here? Over 1,000 items would you believe it has made a difference of $1,500…

Note: Keyword .Sum must now be added to QTY as Gorden has had 4 Beers…

16

.sum, .count, .asc, .desc, keywords will trigger grouping mode. That is a different kind of field selection. You should either include a unique field like date / time, id or use sum keyword to sum grouped values.

1 Like
17

Ok thanks Emre - that’s a little trick which is good to know. Now to revise all my reports, as I am such a pain for myself - I mean who audits their own work :scream:

2 Likes