Server Side Expressions for Custom Reports
As you may already know SambaPOS fetches all tickets filtered by date when we create a report like this.
When we filter results SambaPOS still fetches all tickets from database (all workperiod tickets in this case) and applies filter on it.
SambaPOS does it for a good reason. If our report is a complex one like that it fetches all ticket at once and generates all parts of the report with that data.
This is the generated SQL script for that report. It only filters by date and applies ticket state filters while rendering the report.
As Custom Report features primarily built for reporting it works great.
However when we use this for non reporting cases that may fetch unnecessary data.
We can check @RickH’s post here.
That executes a report tag like that.
{REPORT TICKET COUNT:TS.Status=Hold) AND (TT.Hold=[:UserName])}
In fact we don’t need all tickets here. For example as these tickets are open tickets it will be better to fetch only open tickets.
I changed that tag a little bit and executed that.
{REPORT TICKET COUNT:(TS.Status=Unpaid) AND T.IsClosed=false}
That returns the correct result.
However it still fetches all work period tickets even I filter by IsClosed field.
I assume you already know you can filter by database table fields by using
T.<fieldname>syntax. For open tickets IsClosed field becomes true so we can use T.IsClosed=true to list closed ticket.
How can we improve that?
If we can execute IsClosed=false expression on server side it will greatly improve report speed and decrease server traffic. I implemented a feature called “Server Side Expressions” for that. To execute an expression on server side you need to put the expression in single quotes.
This feature only works for expressions that works with database fields. For example we can’t execute (TT.Status=Paid) expression on server side as it works with JSON data and there is no direct SQL translation for it.
So when I change IsClosed=false part to a server side script by putting it in single quotes the generated SQL Script will slightly change. For server side expressions I’ll also remove T. prefix and just use the database field name.
{REPORT TICKET COUNT:(TS.Status=Unpaid) AND 'IsClosed=false'}
That still shows the same result but now less data fetched from server. You can see IsClosed expression added to SQL script.
We have some more features. For example we can optimize it a little more by adding a text search.
{REPORT TICKET COUNT:(TS.Status=Unpaid) AND 'TicketStates.Contains("Unpaid")' and 'IsClosed=false'}
You can see that expression translated to a LIKE select.
Of course this is not the correct way to filter by ticket status but when used with (TS=) expression that will help fetching less data.
I primarily implemented it for {REPORT XXX DETAILS} like reports and also works fine for them. The last example I saw was a COUNT report so I demonstrated it.
It is possible to do complex things like that.
[REPORT:1, 1]
{REPORT TICKET DETAILS:T.TicketNumber,T.TotalAmount:'Orders.Any(MenuItemName.Contains("Bacon"))'}
Count of Tickets|{REPORT TICKET COUNT:'Orders.Any(MenuItemName.Contains("Bacon"))'}
That reports tickets that have at least one product that contains Bacon keyword. It also displays count of them and does great job by executing both report tags (details and count) by executing a single SQL script.
This is the generated SQL Script in case you’re interested.
