Custom Report - How to ticket seperated Item Sales Report by Ticket Type?

Good morning everyone, I have many Ticket Types (Ex: TicketA, TicketB…) running con-current in my restaurant.

The build-in Item Sales Report will combine all sale data of all Ticket Types into 1 report which make it hard for me to analyze sale data.

Ex: TicketA sold 2 items ApplePie
TicketB sold 1 item ApplePie
Item Sales Report will show ApplePie sold 3 items.

In my case I really need a report that be able to separate the ApplePie into 2 group for each Ticket Type.

I wonder if there were any report template published already? Thank you so much for your help.

You should really study this:

http://www.sambapos.com/wiki/doku.php/custom_reporting

If you spend the time to really study it and practice the concepts you will be able to support your business better by creating any report you want.

Grouping Item Sales report by ticket type might be a little tricky. This is the report you need.

[Sales:5, 2, 2,1]
@{TICKET TYPE LIST}
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:,}
>>Ticket Type|| $1
>{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and (TY=$1) and O.MenuItemName="$2"}
{REPORT ORDER DETAILS:'     '+[O.PortionName],O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and (TY=$1) and O.MenuItemName="$2":2}

The idea is simple but as this report already groups items by portions it might be complex to explain that so I’ll use another report to explain it. This is another part of Items Sales report and it shows sale amounts by item group. Please follow this step by step and run all reports to understand how grouping works.

[Group Sales by Amount:2, 1, 1]
{REPORT ORDER DETAILS:O.ItemGroup,O.ExactTotal.Percent.desc,O.ExactTotal.Sum:(ODI=True)}
>Total|{REPORT ORDER DETAILS:O.ExactTotal.Sum:(ODI=True)}

To be able to report a single Ticket Type I can use (TY=Ticket Type Name) expression to filter items.

[Group Sales by Amount:2, 1, 1]
{REPORT ORDER DETAILS:O.ItemGroup,O.ExactTotal.Percent.desc,O.ExactTotal.Sum:(ODI=True) and (TY=Ticket)}
>Total|{REPORT ORDER DETAILS:O.ExactTotal.Sum:(ODI=True) and (TY=Ticket)}

You’ll notice how I’ve added and (TY=Ticket) part to expression.

I have two ticket types named Ticket and Delivery. Now I’ll append Delivery ticket report under that report.

[Group Sales by Amount:2, 1, 1]
{REPORT ORDER DETAILS:O.ItemGroup,O.ExactTotal.Percent.desc,O.ExactTotal.Sum:(ODI=True) and (TY=Ticket)}
>Total|{REPORT ORDER DETAILS:O.ExactTotal.Sum:(ODI=True) and (TY=Ticket)}
{REPORT ORDER DETAILS:O.ItemGroup,O.ExactTotal.Percent.desc,O.ExactTotal.Sum:(ODI=True) and (TY=Delivery)}
>Total|{REPORT ORDER DETAILS:O.ExactTotal.Sum:(ODI=True) and (TY=Delivery)}

I sold few items to simplify result so it lists only Wraps. Two reports should merge like that.

Now I can type ticket type names manually to separate these reports.

Seems like what I need but not a good solution as we need to modify all related reports when we add a new ticket type. We can use a different feature to make it more flexible.

Instead of merging two reports I’ll supply a parameter name list and use these parameters inside reports.

[Group Sales by Amount:2, 1, 1]
@Ticket,Delivery
>- $1 -
{REPORT ORDER DETAILS:O.ItemGroup,O.ExactTotal.Percent.desc,O.ExactTotal.Sum:(ODI=True) and (TY=$1)}
>Total|{REPORT ORDER DETAILS:O.ExactTotal.Sum:(ODI=True) and (TY=$1)}

So @Ticket,Delivery values replaces with $1’s one by one and produces same report without duplicating ticket type sections. If I add another ticket type I can just add it to the list like @Ticket,Delivery,New Ticket Type or better I can use {TICKET TYPE LIST} tag to read ticket type names dynamically.

[Group Sales by Amount:2, 1, 1]
@{TICKET TYPE LIST}
>- $1 -
{REPORT ORDER DETAILS:O.ItemGroup,O.ExactTotal.Percent.desc,O.ExactTotal.Sum:(ODI=True) and (TY=$1)}
>Total|{REPORT ORDER DETAILS:O.ExactTotal.Sum:(ODI=True) and (TY=$1)}

###BONUS!!!

While implementing grouping feature I didn’t intended it but that syntax invented by @Jesse to have separate tables for each groups. What you’ll do here is moving parameter list to top and include $1 in table definition section. That may not work fine for some report structures but I thought it worths mentioning.

I intentionally demonstrated both static and dynamic grouping features. Preparing reports by grouping sections statically (manually typing & merging ticket type reports) is not a preferred method but still might be useful for some cases. I thought it is good to know all alternatives.

3 Likes

Dear emre,

Thank you so much for the detailed reply. But I have a question: How would you make a “Total” sub-row for each Ticket Type?

1 Like

This seems to be working for me

[Item Sales By:5, 2, 2,1]
@{TICKET TYPE LIST}
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:,}
>>Department  :  $1
{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and (TY=$1) and O.MenuItemName="$2"}
{REPORT ORDER DETAILS:'     '+[O.PortionName],O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and (TY=$1) and O.MenuItemName="$2":2}
>Sub Total||{REPORT ORDER DETAILS:O.ExactTotal.Sum.desc.sum:(ODI=True) and (TY=$1)}
>>Total Sales||{REPORT ORDER DETAILS:O.ExactTotal.Sum.desc.sum}