Custom Report Help - Last Order No

Hello everyone,

I have been trying to make a report to give me the last order no and the time of the selected department. I use {Department:Dept. Name} ticket tag to separate the depts and also {Order No:####} for my order numbers.

Basically what I need is something like below:

{REPORT TICKET DETAILS:T.Time,TT.Order No.max:TT.Department=Restaurant}

Result should be a single value : For example: 21:00 | 0001

I have been bursting my head and trying for hours but no luck. I would really appreciate if someone can give me a hand. I really dont have much SQL knowledge if not I am sure that would do it.

Regards,

Ekin

Why are you using ticket tags to separate departments instead of using departments? You would have to redefine all standard reports. Also why are you trying to get the last order in a report, I would think thats generally needed for some sort of recall not a report.

If you were able to use departments for this SQL would be the way to go. You can easily get the last order based on department and limit it to a single result. Such as:

SELECT Top 1	Orders.CreatedDateTime, Orders.OrderNumber
FROM            Orders INNER JOIN
                         Departments ON Orders.DepartmentId = Departments.Id
WHERE Departments.Name = 'Cafe'
ORDER BY Orders.Id DESC 

Then use {REPORT SQL DETAILS:<handler or sql>;<parameters,1,2>:<fields>:<expressions>:<format>} to define what you’re looking to get in your report.

See:

1 Like

Thanks for the further info on your config. Since you’re using ticket tags and you want the last order we need to join the tables and filter for the tag. Below should work, just change your ticket tag name to match whatever you’re comparing like “Departments”.

[Last Order Report:1,1]
{REPORT SQL DETAILS:SELECT TOP 1 Orders.Id
	,CONVERT(varchar,Orders.CreatedDateTime,100) as DateTime
	 FROM Tickets 
	 INNER JOIN Orders ON Tickets.Id = Orders.TicketId
	 
	 CROSS APPLY OPENJSON(TicketTags)
	 WITH (
		TagName varchar(50) '$.TN'
		,TagValue varchar(50) '$.TV'
		)

WHERE TagName = 'PickupID'
ORDER BY Tickets.Id DESC, Orders.Id DESC 
:F.Id,F.DateTime::{0}|{1}}