This report lists tickets that have a selected customer and displays custom field values.
###@@XTicketListByCustomer
select
T.TicketNumber,
min(convert(nvarchar,T.Date,105)) as Date,
min(convert(nvarchar,T.Date,108)) as Time,
COALESCE(min(TE.EntityName),'-') as [Entity Name],
COALESCE(min(cast(TE.EntityCustomData as nvarchar(4000))),'-') as [Custom1],
COALESCE(min(cast(TE.EntityCustomData as nvarchar(4000))),'-') as [Custom2],
min(T.TotalAmount) as TotalAmount
from Tickets as T
left outer join TicketEntities as TE on TE.Ticket_Id = T.Id
Where TE.EntityTypeId in (Select Id from EntityTypes where Name='@1')
and Date > '{Start}' and Date < '{End}'
group by T.TicketNumber
###Template
[Tickets:1,2,2,2,2,3, 2]
>Ticket No|Date|Time|Customer|:Phone|:Address|Amount
@@XTicketListByCustomer:Customers
If your Customer Entity Type Name is different from Customers
replace @@XTicketListByCustomer:Customers
line parameter with your entity name. For example @@XTicketListByCustomer:Müşteriler
.
Replace :Phone
and :Address
headers with the custom field names you want.
###Result
###Database Tools Import File
Ticket List by Customer.zip (782 Bytes)
At least V 4.1.50 needed.