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

The group report I got the sq from didnt take portions into account just did menu item qty and value…

I want;

Drinks

  • Beer A
    – Pint
    – Half
  • Beer B
    – Pint
  • Beer C
    – Pint
    – Half
    Starters
  • A
  • B
  • C
    Mains
  • A

  • Bar Meals
  • Sandwiches
    – Cheese (Choice is portion)
    – Ham (Choice is portion)
  • Burgers
    – Beef (Choice is portion)
    – Chicken (Choice is portion)
    etc etc etc

Remove the “>” before your first Report Tag line.

1 Like

Is that it! LOL, was close :slightly_smiling:

Can I ask what is it that is making the last line ignore the items with non normal portions?
Whats the 2 doing on the end of the tag?

No idea. I was going to ask you the same.


Hmm, it isn’t just ignoring Normal portions. It’s not grouping the portions under the correct Item.

But that makes sense…We are in fact running 2 separate Reports. You’ll need to find another way to format it like you want.

That part came from the default item sales report, I didnt put the :2 in…

I use this (perhaps its usefull for you too):

[!Totales por Grupo con TAGS:2,2,2, 1, 1,1]
>Grupo|Porcion|MenuItemName|[N]Cantidad|[C]Total|Status
{REPORT ORDER DETAILS:O.ItemGroup,O.PortionName,O.MenuItemName,O.Quantity,O.Total,OS.GStatus}

Thanks!!!

G.

2 Likes

Ive tried to use the pivot table and seems to do the job thanks.
However total is total submitted which not ideal for this report.

What I ideally would like would be the following;

Group|Product|Portion|[N]Submitted|[N]Gifted|[N]Refund|[N]Void|[C]Revenue

Attempted this without luck…

[!Item Sales by Group:2 ,2 ,2 , 1, 1, 1, 1, ]
@{REPORT ORDER DETAILS:O.MenuItemName}
>Group|Product|Portion|[N]Submitted|[N]Gifted|[N]Refund|[N]Void|[C]Revenue
{REPORT ORDER DETAILS:O.ItemGroup,O.MenuItemName,O.PortionName:(O.MenuItemName=$1)}|{REPORT ORDER DETAILS:O.Quantity:(O.MenuItemName=$1) AND (OS.Status=Submitted)}|{REPORT ORDER DETAILS:O.Quantity:(O.MenuItemName=$1) AND (OS.GStatus=Gift)}|{REPORT ORDER DETAILS:O.Quantity:(O.MenuItemName=$1) AND (OS.GStatus=Refund)}|{REPORT ORDER DETAILS:O.Quantity:(O.MenuItemName=$1) AND (OS.GStatus=Void)}|{REPORT ORDER DETAILS:O.Total:(O.MenuItemName=$1)}

We cant ser constraints/expressions on each field can we? Ie to give a count of submitted, void, refund qty etc in the columns?

I think from playing around its a constraint that that line only shows if there are more than one line in the report…

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)}

2 Likes

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

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

BTW Nice work @JTRTech

2 Likes

:blush: Yes! Thanks kendash…

@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.

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?

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

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.

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

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…

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…

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