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

JTRTech · February 18, 2016, 5:24pm

I am trying to group my Item sales report by menu item group but am struggling.
Found a snippet for getting groups but am having difficulties getting it to group with portions.

This is what I have at the minute;

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

But its not coming out as expected…

What am I missing?

QMcKay · February 18, 2016, 5:33pm

It’s coming out the way you wrote it. What are you “expecting” to see instead?

Do you want to see Portions or not? If not, remove the O.PortionName tag from the report.

JTRTech · February 18, 2016, 5:35pm

I want it to be the same as the default item sales list but add the ‘header’ rows for groups and obviously the items under ech will be that group.

I was origionally trying to use a second @ so sql got group list and then the original item list just reported for each group as it went through but couldnt get the two @'s to work.

Jesse · February 18, 2016, 5:41pm

You can use jscript for the sql and just use call:x.

I am somewhat confused by what your wanting. I understand most of it but what I dont understand is what your trying to do with portions.

JTRTech · February 18, 2016, 5:46pm

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

QMcKay · February 18, 2016, 6:10pm

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

JTRTech · February 18, 2016, 6:13pm

Is that it! LOL, was close

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?

QMcKay · February 18, 2016, 6:28pm

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.

JTRTech · February 18, 2016, 6:30pm

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

gerlandog · February 18, 2016, 11:42pm

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.

JTRTech · February 26, 2016, 5:53pm

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;

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?

JTRTech · February 26, 2016, 6:33pm

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

JTRTech · February 26, 2016, 9:15pm

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

pauln · February 27, 2016, 1:56am

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
Thanks Paul.

Jesse · February 27, 2016, 2:29am

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

BTW Nice work @JTRTech

pauln · February 27, 2016, 2:40am

Yes! Thanks kendash…

JTRTech · February 27, 2016, 3:42pm

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

JTRTech · January 13, 2017, 12:40am

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?

emre · January 13, 2017, 8:55am

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

JTRTech · January 13, 2017, 10:10am

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.