Help with customer ticket detailed report

[markjw]

I’m trying to create a report that will show detailed tickets for all customers, that shows the ticket details (total, etc) and all the ordered items on that ticket. It’s basically to allow monthly invoices to be prepared for customer accounts.

At the moment, I have got this far - getting the ticket details and order details for tickets where a customer entity is selected:

[Customer Ticket Details:3,2,2,4,4,1, 2]
>Date|Time|Ticket|Customer|Item|Qty|Total
@{REPORT TICKET DETAILS:T.TicketNumber:(TCET=Customer):|}
{REPORT TICKET DETAILS:T.Date,T.Time,T.TicketNumber,[EN.Customer]+'-'+[EC.Customer.First Name]+' '+[EC.Customer.Last Name],'','','':T.TicketNumber=="$1"}
{REPORT ORDER DETAILS:'','','','',O.MenuItemName,O.Quantity,O.Price:T.TicketNumber=="$1"}
>||||TOTAL||{REPORT TICKET DETAILS:T.TotalAmount:T.TicketNumber=="$1"}

What I am struggling with is 2 things:

1. Some orders have order tags with a price, so currently that amount is not included (it is included in the total but the order tag would need to show so things add up). I have tried to use (REPORT ORDER TAG DETAILS:X) tag to show these, but struggling to get it to link to the orders and show under each order in sequence. This is what I tried so far but doesn’t work:

[Customer Ticket Details:3,2,2,4,4,1, 2]
>Date|Time|Ticket|Customer|Item|Qty|Total
@{REPORT TICKET DETAILS:T.TicketNumber:(TCET=Customer):|}
@{REPORT ORDER DETAILS:O.OrderNumber:T.TicketNumber=="$1":|}
{REPORT TICKET DETAILS:T.Date,T.Time,T.TicketNumber,[EN.Customer]+'-'+[EC.Customer.First Name]+' '+[EC.Customer.Last Name],'','','':T.TicketNumber=="$1"}
{REPORT ORDER DETAILS:'','','','',O.MenuItemName,O.Quantity,O.Price:T.TicketNumber=="$1"}
{REPORT ORDER TAG DETAILS:'','','','',T.Name,T.Qty,T.Price:O.OrderNumber=="$2"}
>||||Total||{REPORT TICKET DETAILS:T.TotalAmount:T.TicketNumber=="$1"}

2. I would like to only show tickets where they have been paid on customer account. Currently it shows any that are linked to a customer entity. I’m not sure how to incorporate that within the same report.

After this, I probably will need to look at including calculations so I can account for any discounts that have been applied at ticket level. But I can probably work that out once I have the 2 points above dealt with.

[Jesse]

You may need to build this in SQL or use visual reports.

[markjw]

@Jesse

You may need to build this in SQL

Ok so I have tried displaying the order tags using SQL but I’m struggling to get it working.

I’m using SQL 2017, so as the Order Tags are stored in the Orders table as JSON, I have been able to extract that using the JSON functions in SQL 2017. I am only interested in order tags that have a price associated.

For example, this ticket:

Doing a test in SSMS, I can successfully extract the order tag:

Caramel    0.40

I need to display the order tags with prices under the order for each ticket in the report.

Adding this SQL into the report:

[Customer Ticket Details:3,2,2,4,4,1, 2]
>Date|Time|Ticket|Customer|Item|Qty|Total
@{REPORT TICKET DETAILS:T.TicketNumber:(TCET=Customer):|}
@{REPORT ORDER DETAILS:O.OrderNumber:T.TicketNumber=="$1":|}
{REPORT TICKET DETAILS:T.Date,T.Time,T.TicketNumber,[EN.Customer]+'-'+[EC.Customer.First Name]+' '+[EC.Customer.Last Name],'','','':T.TicketNumber=="$1"}
{REPORT ORDER DETAILS:'','','','',O.MenuItemName,O.Quantity,O.Price:T.TicketNumber=="$1"}
{REPORT SQL DETAILS:
	SELECT 
	  '  - ' + tagValue AS OrderTag
	  ,tagQty AS Quantity
	  ,(tagPrice * tagQty) AS Total
	  ,tagRate
  	FROM [Orders]
	CROSS APPLY OPENJSON([OrderTags]) WITH (
		tagPrice decimal(16, 2) '$.PR', 
		tagValue varchar(255) '$.TV', 
		tagQty decimal(16, 3) '$.Q', 
		tagRate decimal(16, 2) '$.RT'
	)
	WHERE OrderNumber = '@1'
	AND (tagPrice IS NOT NULL OR tagRate IS NOT NULL);$2
	:F.OrderTag,F.Quantity,F.Total::''|''|''|''|{0}| {1}| {2}
}
>||||TOTAL||{REPORT TICKET DETAILS:T.TotalAmount:T.TicketNumber=="$1"}

But the result is the order tags are not displayed and the total rows are no longer grouped:

I think it may have something to do with how the {REPORT SQL DETAILS:X} tag handles parameter lists - how it takes the whole list as a comma separated list instead of cycling through each individual parameter.

I read and tried to follow the what Q had posted here previously but I’m not sure if the trick with the comma works with report tags other than {REPORT SQL DETAILS:X}.

REPORT SQL DETAILS to create a Simple Menu Price List

I got it to work with a very small tweak! @JTRTech The elusive secret mystery of the terminating comma has finally been revealed! [Price List:5 ,3,3, 1, 2] @{REPORT SQL DETAILS:SELECT DISTINCT [GroupCode] FROM [MenuItems] ORDER BY [GroupCode]:F.GroupCode::{0},} >Product | Portion | Band | Price $1 ============== {REPORT SQL DETAILS: SELECT product.[GroupCode] AS [GC], product.[Name] AS [Product], portion.[Name] AS [Portion], price.[PriceTag] AS [Price Band],…

For the parameter list

@{REPORT ORDER DETAILS:O.OrderNumber:T.TicketNumber=="$1":|}

I tried putting various different formats at the end instead of | but none worked - |,, ,, {0},. Possibly this trick only works with SQL tags?

Could really do with some advice… @QMcKay ?

[Jesse]

I really meant build entire report in SQL but I’m not very good with SQL.

[markjw]

@Jesse

I really meant build entire report in SQL

Yeah I thought so, but I also thought I could try to just do the part I needed in SQL since the rest worked…

@Jesse

but I’m not very good with SQL

No worries, I tagged @QMcKay in the last post as I used his tips on extracting the JSON and the trick he mentioned about parameter lists. Hope he might be able to see if what I am trying is possible or if I need to do the whole report in SQL.