Individual Work Period Report Per Till

V5 Question
1

Currently, I’ve hooked up 2 tills to a sql database, and the message server hooked up and it’s all working.

What currently happens is that no matter which till you are on, when you view the work period report, it shows the grand total of everything that both tills have done.

I’ve managed to follow the amount per terminal tutorial which shows individual amount of sales made per till, but If someone could point me in the right direction, in creating a report which shows everything as it does in work period report, except it’s unique to that till. This helps to break down what items were sold on what till, and helps how many tickets were generated per till, and gives a a better idea of overall performance per till.

2

You will need to edit the report and add expressions to filter that till. I’ll give you example in moment.

Here is the raw syntax to change its the simplest form of edit to do what you want the only issue is you need to know your terminal ID which can be found using MSSMS

[Sales:1, 1]

{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0 && TerminalId == 1}

Note I added && TerminalId == 1 to end of that report.

Pasted image1428×197 9.87 KB

Same report when I changed to show TerminalId == 2

Pasted image1375×160 8.38 KB

Untitled.png1152×648 44.9 KB

3

So find out your terminal Id’s then edit each report line to include that syntax I showed above. You should create a copy of the WP report and name it for each terminal.

Alternative dynamic version coming up…

4

Hi @Jesse

This is sort of still relevant - but how to you know or decide that you can reference “TerminalId” from dbo.Terminals? Like would you not need to know that dbo.Terminals was “open” and used in the source code SQL Select for REPORT TICKET TYPES?

This would solve quite a few issues I have with generic REPORT TAGS…

5

In this case I’m referencing it from tickets not terminals. REPORT TICKET reports mostly from dbo.Tickets so I look at those fields. TerminalId was a field in dbo.Tickets

6

Ok so Tickets have TerminalID - but how do you decide to take the punt that Emre has used that in his SQL?

7

He follows a pattern. If you look at it enough you see his patterns and can tell based on his naming.

1 Like
8

Umm ok - that what I thought, so it goes to my issue with JSON data in the situation for Custom Tags alike. There is no way to handle JSON unless you go to script correct?

The example is to filter a report by Custom Field Contents…

9

I was about to suggest scripting or creative report parameter use. When I get home I can show few examples.

I was planning to show in this post how to use SQL helper and a script to populate terminal ID automatically based on {:CURRENTTERMINAL} tag.

That would allow you to make single report for all terminals.

So if you pull up WP report on terminal A it only shows Terminal A data. Instead of requiring separate reports.

1 Like
10

Nice, look forward to it if you find the time :smile:

11

Terminal A

Pasted image1392×82 5.96 KB

Terminal B

Pasted image1469×76 5.67 KB

Report:

[Sales:1, 1]
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0 && TerminalId == {CALL:TerminalId.getterminal('{SETTING:CURRENTTERMINAL}')}}

Script:

function getterminal(id){
var qry = "SELECT [Id] FROM [Terminals] WHERE [Name] = '"+id+"'";
var r = sql.Query(qry).Join(',');
return r;
}

That is the SAME report for both terminals as in only a single report exists it dynamically adjusts based on what terminal is used.

1 Like
12

Gees you make it look so easy! Now a question I wanted to ask is can you use PROMPT ? somewhere (Script or REPORT) to get the Terminal ID?

Also how are you using .Join?

13

Here is the entire report customized for specific terminal. There are a couple tags I couldnt filter and its probably currently impossible so we need to rebuild those with other methods.

Those tags were:

[Refund Payments:2, 1, 2]
{REPORT REFUND PAYMENTS}

>>Ticket Counts per State
{REPORT TICKET STATES}
>>Order Counts per State
{REPORT ORDER STATES}

[Ticket Tags:1, 1, 1]
{REPORT TICKET TAGS}

Final customized report not including for the tags mentioned above:

[Sales:1, 1]
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0 && TerminalId == {CALL:TerminalId.getterminal('{SETTING:CURRENTTERMINAL}')}}


[Pre Orders:1, 1]
{REPORT TICKET TYPES:PreOrder && TotalAmount >= 0 && TerminalId == {CALL:TerminalId.getterminal('{SETTING:CURRENTTERMINAL}')}}


[Refunds:1, 1]
{REPORT TICKET TYPES:TotalAmount < 0 && TerminalId == {CALL:TerminalId.getterminal('{SETTING:CURRENTTERMINAL}')}}


[Payments:2, 1, 2]
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum:(TE={SETTING:CURRENTTERMINAL}) AND Payment.Amount > 0}
>Total|{REPORT PAYMENT DETAILS:P.Amount.Sum:(TE={SETTING:CURRENTTERMINAL}) AND Payment.Amount > 0}


[Refund Payments:2, 1, 2]
{REPORT REFUND PAYMENTS}


[Ticket Details:2, 1, 2]
>>Ticket Counts
@!{TICKET TYPE LIST}
$1|{REPORT TICKET COUNT:(TY=$1)}|{REPORT TICKET TOTAL:(TY=$1) AND (TE={SETTING:CURRENTTERMINAL}) }
Total|{REPORT TICKET COUNT:(TE={SETTING:CURRENTTERMINAL})}|{REPORT TICKET TOTAL:(TE={SETTING:CURRENTTERMINAL})}
Amount per Ticket||[=F(TN('{REPORT TICKET TOTAL:(TE={SETTING:CURRENTTERMINAL})}')/TN('{REPORT TICKET COUNT:(TE={SETTING:CURRENTTERMINAL})}'))]
>>Order Counts
@!{TICKET TYPE LIST}
$1|{REPORT ORDER COUNT:(TY=$1) AND (TE={SETTING:CURRENTTERMINAL})}|{REPORT ORDER TOTAL:(TY=$1) AND (TE={SETTING:CURRENTTERMINAL})}
Total|{REPORT ORDER COUNT:(TE={SETTING:CURRENTTERMINAL}) }|{REPORT ORDER TOTAL:(TE={SETTING:CURRENTTERMINAL}) }
Orders per Ticket||[=F(TN('{REPORT ORDER COUNT:(TE={SETTING:CURRENTTERMINAL}) }')/TN('{REPORT TICKET COUNT:(TE={SETTING:CURRENTTERMINAL}) }'))]
Amount per Order||[=F(TN('{REPORT ORDER TOTAL:(TE={SETTING:CURRENTTERMINAL}) }')/TN('{REPORT ORDER COUNT:(TE={SETTING:CURRENTTERMINAL}) }'))]
>>Ticket Counts per State
{REPORT TICKET STATES}
>>Order Counts per State
{REPORT ORDER STATES}


[Ticket Tags:1, 1, 1]
{REPORT TICKET TAGS}


[Payment Details:2, 1, 2]
@{TICKET TYPE LIST}
@{TAX TYPE LIST}
>$1
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum:(TY=$1) AND (TE={SETTING:CURRENTTERMINAL})}
{REPORT CALCULATION DETAILS:C.Name,C.X,C.CalculationAmount.Sum:(TY=$1) AND (TE={SETTING:CURRENTTERMINAL})}
$2||{REPORT TICKET DETAILS:TX.$2.Sum:(TY=$1) AND (TE={SETTING:CURRENTTERMINAL})}


[User Sales:1, 1] 
{REPORT ORDER DETAILS:O.User,O.ExactTotal.Sum}
@{REPORT PAYMENT DETAILS:P.User,P.Amount.Sum::{0}:,} 
[Settled by $1:1, 1, 1] 
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum:(PU=$1) AND (TE={SETTING:CURRENTTERMINAL})} 
>Total Income|{REPORT PAYMENT DETAILS:P.Amount.Sum:(PU=$1) AND (TE={SETTING:CURRENTTERMINAL})}


[Item Sales:2, 1, 2]
{REPORT ORDER DETAILS:O.ItemGroup,O.ExactTotal.Percent,O.ExactTotal.Sum:(TE={SETTING:CURRENTTERMINAL})}
Total||{REPORT ORDER DETAILS:O.ExactTotal.Sum:(TE={SETTING:CURRENTTERMINAL})}
1 Like
14

Its not really required it was there to comma separate multiple returns but there is no reason to do that since we are returning only one value so you can take that out. I copied and pasted from other code I had for simplicity and left that there since it didnt really mess with results.

1 Like
15

You can use whatever method you want to retrieve the current terminal name. I chose to use {SETTING:CURRENTTERMINAL} tag.

If you want to create a prompt to feed a terminal name into the script it will work just fine as well.

16

If you look at the full report above some of the tags didnt require the same syntax. Some of the tags had built in expressions for terminal like this:(TE={SETTING:CURRENTTERMINAL})

THe expression would be (TE=<terminal name>)

17

@pauln You could use report widget and editor widget in an entity screen and feed the input from editor widget into the script which would then reflect on the report widget.

This would be a nice way to look at multiple terminal work period reports with only having to use a single report. You could use label widget to call the editor widget “Enter Terminal Name” and then when you enter the terminal you want to view report for it will pull up that terminals report in the same screen.

1 Like
18

This is great as at some stage I need to look at Departments. We may have 3 Terminals Department “A” and 1 Terminal Department “B”.

Thinking I could use .contains to search a string of Terminals ID?
Is “2” found within “123”…

19

LOL :laughing: I have my browser open on several pages with references to exactly what you have said! Report Widgets and Editor Widgets. Just getting my head how they pass the parameters…

(The only bugger is that I currently being distracted doing other duties :unamused: - just not enough time)

1 Like
20

Scripting makes it so we can do just about anything.

1 Like