Help with customer ticket detailed report

V5 Question
3

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:

image

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

image

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:

image
image706×718 19.1 KB

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}.

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 ? :wink: