Item Sales report ODI = True strange results

silentbob · March 15, 2018, 10:06pm

Has anyone else come across this issue

Some items are being excluded.
In this example I have Sold 1 Bacardi Rum and 1 Hennessy Brandy and also voided 1 Bacardi Rum

Capture

[Sales by Group non VOID:2, 1, 1, 1]
{REPORT ORDER DETAILS:O.ItemGroup,O.ExactTotal.Percent.desc,O.Quantity.Sum,O.ExactTotal.Sum:(ODI=True)}
>Total|{REPORT ORDER DETAILS:O.Blank,O.Quantity.Sum,O.ExactTotal.Sum:(ODI=True)}

[Item List:5, 2, 2,1]
{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:|}

Both Items report correctly here with ODI=True
Item list also shows both items.

It gets strange when showing the MenuItem Name.
The Hennessy Brandy only shows when I remove the ODI Filter

[Sales ODI=True:5, 2, 2,1]
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:|}
>{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and O.MenuItemName="$1"}
{REPORT ORDER DETAILS:'     '+[O.PortionName],O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and O.MenuItemName="$1":2}

[Sales ODI = False:5, 2, 2,1]
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:|}
>{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=False) and O.MenuItemName="$1"}
{REPORT ORDER DETAILS:'     '+[O.PortionName],O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=False) and O.MenuItemName="$1":2}

[Sales No ODI Filter:5, 2, 2,1]
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:|}
>{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:O.MenuItemName="$1"}
{REPORT ORDER DETAILS:'     '+[O.PortionName],O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:O.MenuItemName="$1":2}

This is happening for different random products. Has anyone come across this before?

silentbob · March 15, 2018, 10:34pm

Ok I think I figured this out, it seems to be the “and” in the MenuItemName that is breaking it Removing the “Br’and’y” from the “Hennessy Brandy” fixes the issue. Surely this is a bug.

As you can see here anything with an “and” in the name does not report under ODI=True or ODI=False
Capture

JTRTech · March 15, 2018, 10:49pm

What version.
Doesnt seem to be issue on mine;

silentbob · March 15, 2018, 10:50pm

Try this

@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:|}
>{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:O.MenuItemName="$1"}
{REPORT ORDER DETAILS:'     '+[O.PortionName],O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:O.MenuItemName="$1":2}

JTRTech · March 15, 2018, 10:50pm

Cancel that,
yes it does when you delair a list;

JTRTech · March 15, 2018, 10:59pm

Explains why the stocktake was showing as short on XO Brandy…

I would call it a bug, should be able to have products with and in the name…

silentbob · March 15, 2018, 11:04pm

Hopefully it can be fixed fairly soon, If it was just " and " on its own we could just replace it with & but not when it is in the middle of the name its not really and option “Br&y” wouldn’t look right on a receipt.

It seems like samba sees it as another expression but removing the ODI = True and fixes it which makes it strange.

JTRTech · March 15, 2018, 11:17pm

Workarround would probably be tricky as even changing product name would still leave the issue for backdated reports given the use of order name - the order name is not changed with menuitem name change.
And using menuitem name for the @list will leave orders of menu items which has since been changed would be missed.

Have changed to issue unless someone has any bright ideas for a work arround.

silentbob · March 15, 2018, 11:21pm

We could update everything via SQL if there was a workaround but its not ideal.

Its a problem that would definitely raise its ugly head again in down the line.

JTRTech · March 15, 2018, 11:23pm

If you loose the non normal portion bit and allow order not by sales desc you could use;

@{REPORT ORDER DETAILS:O.ItemGroup.asc,O.ExactTotal.Sum::{0}:,}
[$1 Sales:8,4 , 2, 2]
{REPORT ORDER DETAILS:O.MenuItemName.asc,O.PortionName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and (MG=$1)}
>>Total $1|{REPORT ORDER DETAILS:O.Quantity.Sum,O.ExactTotal.Sum:(ODI=True) and (MG=$1)}

silentbob · March 15, 2018, 11:34pm

Unless you have a group called “Sandwiches”

I might try just building the report via SQL, It might be quicker too for the grouped report.

JTRTech · March 15, 2018, 11:36pm

OK, ditch the grouping all together and order by group, then name.
Probably would have been the simplest solution in the first place.

JTRTech · March 15, 2018, 11:39pm

[Sales:4 ,5 ,4 , 2, 2,1]
{REPORT ORDER DETAILS:M.GroupCode.asc,O.MenuItemName.asc,O.PortionName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True)}

silentbob · March 20, 2018, 4:25pm

Its not as clean as the original method but it works.

Hopefully they can fix it so we don’t need a workaround.

emre · August 5, 2018, 9:29pm

It is a shame that my all my automated tests uses upper case AND. So using AND or && as the operator will probably solve the issue but on next update using lower case and will also work fine.

stephencoduor · May 4, 2019, 1:04pm

Hi, was this fixed in the recent updates SambaPOS release or we still need the fix

JTRTech · May 4, 2019, 1:35pm

No, don’t think so. It is an unfortunate side effect of the reporting system emre made to help people that didn’t know SQL.
You could create the report in SQL and bypass the report expressions or use alternative layout which doesn’t get effected by this issue.

stephencoduor · May 5, 2019, 9:51pm

Thanks @JTRTech all my Sandwiches items are missing in the reports and I cant use alternative characters. Is there any person with a work round, and anyone who has sql template/scripts for this problem that we can learn from

JTRTech · May 5, 2019, 9:52pm

Can you demonstrait/elaborate exact issue? and show the report currently used

stephencoduor · May 5, 2019, 10:07pm

[Group Sales by Amount:2, 1, 1]
{REPORT ORDER DETAILS:O.ItemGroup,O.ExactTotal.Percent.desc,O.ExactTotal.Sum:(ODI=True)}
>Total|{REPORT ORDER DETAILS:O.ExactTotal.Sum:(ODI=True)}

[Group Sales by Quantity:2, 1, 1]
{REPORT ORDER DETAILS:O.ItemGroup,O.Quantity.Percent.desc,O.Quantity.Sum:(ODI=True)}
>Total||{REPORT ORDER DETAILS:O.Quantity.Sum:(ODI=True)}

[Sales:5, 2, 2,1]
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:,}
>{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and O.MenuItemName="$1"}
{REPORT ORDER DETAILS:'     '+[O.PortionName],O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and O.MenuItemName="$1":2}