Relational operator in REPORT

Hi everybody,

Just to be sure.

Which relational operator (=; !=; <; >, &; or) can we use in in the following report?

{REPORT TICKET DETAILS:<fields>:<expression>}

Currently trying to report list of tickets matching TicketTAG not NULL:

(TicketTAG = SerialNumber in this exemple)

[Table Custom:2,1,1,1,1]

{REPORT TICKET DETAILS:T.Date,T.TicketNumber,TT.SerialNumber:(TT.SerialNumber != '')}

I’d rather use the standard REPORT functions instead of SQL.


Generally its =, not tried < or >.
Have used != but that was not in a bracketed expression (see the grouped item sales report I put together).

There are 3 different types of filters/constraints for Reports.


These are the most cryptic because documentation lacks detail and they can be different from one Report Tag to another. Uses “known short-hand syntax” as I call it. In this example, TT represents TagGroupName and SerialNumber represents the Tag itself.


##Not Bracketed:

Used when short-hand syntax is not available. Uses Report Field Names. Notice there are no Brackets around this, and the string is "doublequoted" instead of 'singlequoted'


##Server-side Expressions

These are NEW in 5.1.62 (and IMO, the most powerful, but they also have limitations). Uses Column Names from a Database Table. Notice that the entire expression is 'singlequoted'. The following is akin to a SQL LIKE operator, but you can use other operators as well.


##All Together Now

You can use all 3 variations together, strung together with &&, AND, ||, OR operators. This is a Report that I made recently for Event Logging using Tasks …

[ELG Event Log:1,9]
@{REPORT TASK DETAILS:T.Id.asc:(TST=ELG Event Log) && 'content.Contains("Cancel")' OR 'content.Contains("Void")' OR 'content.Contains("Gift")' OR 'content.Contains("Price Change")' OR 'content.Contains("Reopen")':,}

>>{REPORT TASK DETAILS:T.Id,[T.Identifier]+' (Task Id/Ident)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.terminalName,[TSC.userName]+' (Terminal/User)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.workperiodId,[TSC.eventCount]+' (WPID/eventCount)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.ticketId,[TSC.ticketNo]+' (Ticket Id/No)':(TST=ELG Event Log) && T.Id=$1}
>{REPORT TASK DETAILS:[T.Name]:(TST=ELG Event Log) && T.Id=$1}

{REPORT TASK DETAILS:T.ContentText:(TST=ELG Event Log) && T.Id=$1}

The first line shows using bracketed and server-side together. It is responsible for creating a comma-separated list of Task Ids that we can feed into subsequent Report Tags as a $1 variable. We only want the “ELG Event Log” Task Type specified by the bracketed expression, and we only want Tasks that have “Cancel” or “Void”, etc. in the [Content] column in the [Tasks] table in the Database, specified by the server-side expressions.

(TST=ELG Event Log) && 'content.Contains("Cancel")' OR 'content.Contains("Void")'

The other lines in the Report show using bracketed and non-bracketed (report Field names) together: In this case, T.Id contains a number (Task Id) so we don’t need to "doublequote" the $1 variable (but we probably could have done so):

(TST=ELG Event Log) && T.Id=$1

More info on Report Tags, Fields, Expressions …


I hold on … but I already like it …

BOOKMARKED :wink: nice explanation