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.
Marc
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.
##Bracketed:
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.
(TT.SerialNumber!='')
##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'
TT.SerialNumber!=""
##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.
'TicketTags.Contains("someValue")'
##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 
nice explanation