[!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}
[!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 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)}
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?
@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?
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.
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.
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…