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

V5 Question
13

I got there in the end, although my head hurts now LOL

@{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, 2]
>{REPORT ORDER DETAILS:O.MenuItemName,OS.NewBook GLA,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)}

Pasted image584×1109 27.2 KB

2 Likes
14

Nice work @JTRTech, real nice work!

Can I ask about your @{REPORT ORDER DETAILS:x} lines - they setup your first and second parameter right $1 & $2?
But they report on 2 fields, then you use 1 place marker {0}?

I think you are using both .Sum Statements simple extract 1 unique Item from the List?

Finally you are then creating a list for processing by adding :,} on the end of your syntax to produce
Pearsons,Carlesberg,Strongbow - comma separate list?

Did I at least get some right :worried:
Thanks Paul.

1 Like
15

You got just about all of it correct. You are promoted to Custom Reports Apprentice!

BTW Nice work @JTRTech

2 Likes
16

:blush: Yes! Thanks kendash…

17

@pauln yer, took some working out as couldn’t find any documentation for what they actually meant.
From my understanding the {0} means to take the first value/column and the comma if for the seperator, without that i got;
Pearsons,16.00,Carlesberg,19.30,Strongbow,1.90 etc.

The previously questioned 2 on the original item sales report meant that it only showed the second line if there was more than 2 rows in the report.
I was suprised that it worked but I out of curiosity tried O.PortionName!="Normal" so that pretty much it ignores normal portions as I found this is MUCH BETTER as strongbow for example only had a half but on default report as there was only one portion reported it didnt specify half and just said strongbox, 1, 1.90 which wasn’t ideal.

Pretty much I started to look at it more like SQL rather than printer tags & constrains and it started to make a bit more sense.

Getting which bits are repeated each product/portion and which is repeated for group was a bit of trail and error but from what I could gather it is dependent on which $ values are used a bit like state formatting that you NEED to have the $1 value in even if the content is fixed, hard to explain that bit but relieved I got it to work in the end.

18

Am finding this report takes a LONG time to generate when running for monthly stocktake, any suggestions of ways to improve speed?
I expect because its looping through ALL orders for reach portion of product so if 100 products thats 100 loops of all orders for the month…
Understand why that would be a lengthy process but perhaps a twerk could improve performance.
@emre - know your busy, any suggestions?

19

lol what do you mean by LONG. Seeing it in capitals gives an idea but Is it 10 minutes? 1 hour?

20

When you ask I feel stupid for not having an actual time but it’s long enough to that I go away and make coffe etc come back and minimise samba and do something else for a bit.
I think it feels maybe longer than is by the time I’ve liked have to adjust the dates when there ends up being an after midnight end work period on sart/end date meaning tweeking dats to get correct date and having to wait for reload each time.

Also feeling longer when conpaired to the default item sales which lacks the loops so just have to list and sort.

I will run a months worth on the clock and get a actual time taken.
Not expecting it to take seconds but seems like it takes longer than it should and maybe there might be a better way to get similar result.

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.