Ticket List by Customer report

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)

:warning: At least V 4.1.50 needed.

1 Like

I musdt be doing something wrong… It only happens when I select yesterday as a Date range.

[Tickets:2,2,2,2,2,3,2]
>Ticket No|Date|Time|Customer|:Phone|:Points|Amount
@@XTicketListByCustomer:Customers


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  
1 Like

Did you upgraded to latest version?

OK. I’ve reproduced it. That happens if there is a single result.

1 Like

Redownload and reinstall 4.1.50 for fix.

OK!!

Thank you very much!!!

g.

1 Like

Thank you @emre so much,
I knew you’ll find the perfect solution for people like me that have no knowledge of scripting and programming

1 Like

as more people gains interest on custom reports module I believe it will be even easier :slight_smile:

1 Like