Help with Report for Item Sales grouped by Menu item Group but with portion breakdown

V5 Question
21

It would be better to think about that without trying to guess what it does but I was planning to implement a related feature for multiple parameter lists. Maybe it helps.

On next update you can define parameter lists that also have parameters like…

@{REPORT ORDER DETAILS:O.ItemGroup.asc::{0}:,}
@{REPORT ORDER DETAILS:O.MenuItemName.asc:(ODI=True) and (MG=$1):{0}:,}

Second parameter list have (MG=$1) expression so it will process less lines and hopefully it will work faster.

I’ll be glad if you can let me know if it helps or not.

lol

1 Like
22

Hmmm, my stock taker has just noticed an issue with some figures on this report…
The breakdown doesnt match the total…

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

White wines has a total on

>>Total $1|{REPORT ORDER DETAILS:O.Quantity.Sum,O.ExactTotal.Sum:(ODI=True) and (MG=$1)}

of £3,681.75

The portion lines match up to the product total lines ok however the manual adding of the product totals comes to £2999.35

Has ODI=True on all lines so shouldn’t be voids.
Thought it might be my wastage logging system however this sets ODI=False and is reported on a separate report.

img20170124_16401572.pdf (55.7 KB)

Really struggling to work out whats going on…

23

Has anyone got a Sec to offer a seccond opinion on this?

I plan to try and have a look and use some direct SQL to try and narrow down where the difference is coming from but can’t understand posible causes given the same expressions for the breakdown and report…

Stocktaker only mentioned wine as effected but not checked other figures… Something odd happening somewhere.

Slightly conserned as using this report at both properties although the same stocktaker not mentioned anything at the hotel…
It’s the same general setup for automation but a independent set of products…

24

You can PM me the database if you want me to check that.

25

No problem, not onsite at the moment and they are open so dont really want to teamviewer to till so will sort tomorrow.
Thanks emre, if you can see if you notice anything untoward it would be great.

26

Any Updates?

Btw did you tested this one? Was it what you’ve asked for?

27

Sorry emre, been bit busy last few days, will try and sort tomorrow.

28

Hi Emre,
Sorry for delay sorting this, been a bit busy with the baby side of things :slight_smile:
Just PM you a backup.
The date range I was reporting was 9/12/16 -> 22/01/17 which was our last stock take period.
There was a variance on this report between the product & portion breakdown and the group total.
£103.75 on Red Wines and £682.40 on White Wines…
Where the total for the group was more than the breakdown of the individual products…
Might be something stupid but am struggling to work out what was causing it…

29

Some product names breaks expressions. I updated it to work with menu item id’s instead of names.

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

[Item Sales:8, 2, 2, 2]
{REPORT ORDER DETAILS:O.ItemGroup,O.ExactTotal.Percent,O.Quantity.Sum,O.ExactTotal.Sum}
>>Total Sales||{REPORT ORDER DETAILS:O.ExactTotal.Sum}

also if you use latest update you can use this report for faster processing.

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

[Item Sales:8, 2, 2, 2]
{REPORT ORDER DETAILS:O.ItemGroup,O.ExactTotal.Percent,O.Quantity.Sum,O.ExactTotal.Sum}
>>Total Sales||{REPORT ORDER DETAILS:O.ExactTotal.Sum}

I’ll try to fix that expression issue on next updates. Thank you very much for helping me to spot the issue.

6 Likes
30

Thanks, its on live system running .60 so wasn’t planning to update just yet.
Will switch the report and get stock-taker sorted first.
Thanks for looking.
Was it some bad punctuation on the wines causing it? Which character and Ill update the names aswell.
That explains why its just new property and only wines… they have some more creativly names wines down there vs the hotel, plus more familiar with hotel selection so can abbreviate myself rather than just C&P from the wine list.

31

JTR I examined it in more detail. The problem is on that line.

@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:,}

This creates a comma separated list of products however some products also have comma in product names so it also splits these product names. Using menu item ids solves this issue but alternative solution will be using | (pipe) char as the separator like:

@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:|}

As we also support using pipe char as the parameter separator it will be a solution if pipe char is not used in a product name :slight_smile:

3 Likes
32

Awesome, thanks Emre, changing to pipe did the trick perfectly.

33

Is there something change in V5 about that ?

Running this:
{REPORT ORDER DETAILS:O.ItemGroup.asc}

Is giving me this:

I was indeed expecting one line per order and it is obviously not the case.
So I am wondering if I can use it like that or if it is an unexpected behaviour that will be correct in futur update.

Now, I like it, it makes it easier to get the groups that have been used during a working period.

Marc

34

The .asc groups I think.
What are you trying to achieve?

35

I have been live since June the 1st.
Basic functionalities rock !

Now I am making the reports I will need.

In this case, I am on a report for the barman shift change, so all bar items + portions that have been out of the “fridge”. Basically the included _Item sales repor_t but grouped by group :slight_smile:
So I am reading @Emre post

btw what is ODI=True?

36

Order Decrease Inventory

Its used in default void flow and easy option on reporting for items which count as a sale or not.

I did this already.
Only issue I hit was when items with name containing a comma was messing up the order name list as part of the group.
This is the report as is using comma seperator. it takes a while to load as loops through all orders for date range for each group x product but works nicely vs the default item sales;

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

:sunny:

GREAT !

I take indeed a while … I though I was in an endless loop and as I am currently with 3 waiters using it … I was sweating :slight_smile:

38

Not sure on your reporting a skills but so you understands this is as I understand;
List groups,
In a loop create a list of profucts for the group.
Report orders in same was as default item sales report based on that product list.

This is the reason this report takes time to load as its not looking at whole period at once like default its having to go through all tickets each time.
Emre did try and improve the report performace but have not had chance to test in live environment yet…

39

Just a not to anyone who used this report template, there is an issue also for products and product groups with ‘and’ in the name/group - ie ‘Brandy’.
A workarround is to just sort a block report by product/group as here;

40

Thanks Emre Aand JTR Tech for this wonderful tutorial,i am trying to follow all what you have illustrated.
I wanted to create a custom report to be accessed only by a particular department,i have managed to do that.My main issue though,is how to show the specific totals for the chosen menu items.
Please check below

@Breakfast,Deserts,Wraps,
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:|}
[$1 Sales:8, 2, 2]
>{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and O.MenuItemName="$2" and (MG=$1)}
{REPORT ORDER DETAILS:'        '+[O.PortionName],O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and O.MenuItemName="$2" and (MG=$1) and O.PortionName!="Normal"}
>>Total $1|{REPORT ORDER DETAILS:O.Quantity.Sum,O.ExactTotal.Sum:(ODI=True) and (MG=$1)}

[Item Sales:8, 2, 2, 2]
{REPORT ORDER DETAILS:O.ItemGroup,O.ExactTotal.Percent,O.Quantity.Sum,O.ExactTotal.Sum}
>>Total Sales||{REPORT ORDER DETAILS:O.ExactTotal.Sum}

Capture1

Capture2

I want this department to specifically see the sale for only 3 menu items

  • Wraps

  • Deserts

  • Breakfast