Total Waiter Comisions (How to read JSON data fields)

Version 4 V4 Custom Reports
1

I know this is something very special for my setup, but I wanted to show people how to handle Tickets Tags…

I have several ticket tags…

Waiter (three sometimes more)
People sitting at the table
Dine In or To GO

So I was needing a report that could show me how much TAXA DE SERVIÇO I should pay to each Waiter at the end of the day, and wanted to know too, how much I payed at the end of the month… so I had to look into Tickets, search the TicketTag and do some calculations for TAXA DE SERVICO and DESCONTO (DIscount, that goes out of the waiter pocket, we dont do discounts, and when a discount is made, I AM THE WAITER).

Hope this helps others to see how to use TICKET TAGS in Custom Reports…

Report:

[Comisiones por Garçon:2, 1, 1, 1, 1, 1, 1]
>Garçon|Pessoas|Neto|Taxa|Desc|Ticket|Comision
@@TotalComisiones

SQL:

SELECT 

  min(substring(cast(TicketTags as nvarchar(4000)),
            CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37,
             CharIndex('"',cast(TicketTags as nvarchar(4000)),
                CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37
              )- CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))-37) 
        ) as [Garçon],

  SUM(CONVERT(INT,substring(cast(TicketTags as nvarchar(4000)),
        CharIndex('{"TN":"Pessoas","TT":1,"TV":"',cast(TicketTags as nvarchar(4000)))+29,
         CharIndex('"',cast(TicketTags as nvarchar(4000)),
            CharIndex('{"TN":"Pessoas","TT":1,"TV":"',cast(TicketTags as nvarchar(4000)))+29
          )- CharIndex('{"TN":"Pessoas","TT":1,"TV":"',cast(TicketTags as nvarchar(4000)))-29))) 
     as [Pessoas],

  FORMAT(SUM(T.TotalAmount)-SUM(Cal.CalculationAmount),'C') as NetoTicket,
  FORMAT(SUM(case 
      when Cal.CalculationTypeId = 4 then Cal.CalculationAmount
	  else 0 end),'C') as Taxa,
  FORMAT(SUM(case 
      when Cal.CalculationTypeId = 1 then Cal.CalculationAmount 
	  else 0 end),'C') as Desconto,
  FORMAT(SUM(T.TotalAmount),'C') as TotalTicket,

  FORMAT(SUM(Cal.CalculationAmount),'C') as TotalTaxa

FROM Tickets AS T

LEFT OUTER JOIN TicketEntities as TE on TE.Ticket_Id = T.Id
LEFT OUTER JOIN Calculations as Cal on Cal.TicketId = T.Id

WHERE Date > '{Start}' and Date < '{End}'

GROUP BY (substring(cast(TicketTags as nvarchar(4000)),
            CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37,
             CharIndex('"',cast(TicketTags as nvarchar(4000)),
                CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37
              )- CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))-37) 
        )

ORDER BY (substring(cast(TicketTags as nvarchar(4000)),
          CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37,
          CharIndex('"',cast(TicketTags as nvarchar(4000)),
          CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37
          )- CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))-37) 
          )

Just to make shure you all understand…
this portion of the SQL does the trick:

substring(cast(TicketTags as nvarchar(4000)), CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37

So you have to replace {"TN":"Garçon Pizzaria","TT":0,"TV":" with whatever TAG you are looking for and replace the +37 with the lenght of the string you are looking for.

Thanks!!!

G.

2 Likes
2

@gerlandog i have been trying to understand the query but cant know how you put the syntax for substr can you please explain “Garcon” select.

3

I can try…
Garçon means wiater. Here waiters do not work for a salary but for 10% comision of whatever they sell.

min(substring(cast(TicketTags as nvarchar(4000)),
            CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37,
             CharIndex('"',cast(TicketTags as nvarchar(4000)),
                CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37
              )- CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))-37) 
        ) as [Garçon],

This part of SQL retrieves the TAG Garçon from the JSON field of the database and converts it into a columns so we can group by this column in order to get a total by this column.

G.

4

thanks i am trying to retrieve information from ticket tag which at the moment is giving me problem.

5

@gerlandog did you tried creating a report tag using version of it?

6

Sorry. Did not understand.

G

7

Sorry. I mean did you tried creating same report by using reporting tags? For example by using {REPORT TICKET DETAILS} tag and a pivot table.

1 Like
8

yes, here it is!!!

Comisiones TAG.zip (1.7 KB)

its not a pivot table, its just a table…

G.

2 Likes