Custom Report Tags in detail

gerlandog · October 12, 2014, 3:47pm

Ilike this post better, its 101… LOL!!!

guys, a little help here…

This (the filter part):

{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Customer,EC.Customer.Phone,EC.Customer.Points,T.TotalAmount:(EN.customer<>'')}

is not working…
I tried all imaginable, EC, EN, EC.xxx, EN.xxx, EC.xxx.xx, EN.xxx.xx but allways te same error:

Thanks!!!

g.

Jesse · October 12, 2014, 3:52pm

That is wrong syntax for the expression.

{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Customer,EC.Customer.Phone,EC.Customer.Points,T.TotalAmount:(X=X)}

(X=X) is the expression

I can’t tell by your syntax what are you wanting to display?

The way it works is everything before the : is the fields and the expression after the : tells it Fields of X so if you type

{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Customer,EC.Customer.Phone,EC.Customer.Points,T.TotalAmount:(TEN.Customer=John)}

It will display Ticket Number, Ticket Date, Ticket Time, Customer Name, Customer PHone, Customer Points, Ticket Total for Entity John.

Or

{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Customer,EC.Customer.Phone,EC.Customer.Points,T.TotalAmount:(TE=Terminal 1)}

Will display Ticket Number, Ticket Date, Ticket Time, Entity Customer Name, Entity Customer Phone, Entity Customer Points, Ticket Total for terminal 1.

Or

{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Customer,EC.Customer.Phone,EC.Customer.Points,T.TotalAmount:(TEN.Customer=John) AND (TE=Terminal 1)}

Will display Ticket Number, Ticket Date, Ticket Time, Entity Customer Name, Entity Customer Phone, Entity Customer Points, Ticket Total for Entity Customer John and Terminal 1

QMcKay · October 12, 2014, 4:08pm

Just a guess, but in a round-about way, @Jesse may have answered your question here:

(TEN.Customer=John)

So maybe try?

(TEN.customer<>'')

Jesse · October 12, 2014, 4:10pm

If your wanting it to list details for all customers on Terminal 1 then just leave out (TEN.Customer=John) expression… just put

{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Customer,EC.Customer.Phone,EC.Customer.Points,T.TotalAmount:(TE=Terminal 1)}

Remember this is Ticket Details… so it requires an expression.

No that is wrong… the field EN.Customer already does this… he just needs to tell it a different expression like terminal #

He can put all terminals in it… like this (TE=Terminal 1) AND (TE=Terminal 2) AND (TE=Terminal 3) for example and not put an entity expression at all… then it will allow field EN.Customer to pull all customer entities for those terminals.

Or he can use department name it will show only for specific department… etc (DE=Pizzaria)

gerlandog · October 12, 2014, 4:16pm

NO, the EN.Customer does not filter records, just shows the customer if it has or blank if its not there…
I want to filter all records that do have something in EN.Customer

Thanks!!!

g.

QMcKay · October 12, 2014, 4:18pm

@Jesse, this criteria:

(TEN.customer<>'')

is not the same as this criteria:

(TE=Terminal 1)

They will give you different results. I assume @gerlandog wants all tickets that have a Customer Entity, not all Tickets for a particular Terminal. Your suggestion will pull all Tickets, not just those that have or have not a Customer Entity.

The error indicates that EN is not part of a Ticket, while perhaps TEN is.

Jesse · October 12, 2014, 4:18pm

So try (TEN.Customer= ) there is a space after =

gerlandog · October 12, 2014, 4:20pm

exact same error

No option to list all tickets that have a Customer Entity.

It seems that whenever an error occurs, it shows that xx is not part of a Ticket…

Thanks!!!

G.

Jesse · October 12, 2014, 4:21pm

May need a different syntax than ticket details. We can to it just got to get it right.

EDIT try (TEN.Customers=Customer) I was reading the syntax wrong I think this should work.

QMcKay · October 12, 2014, 4:26pm

Is it true that the only available operator is =?

Would be nice if it supported most regular operators such as != , <> , > , < , >= , <=, as well as LIKE…

Jesse · October 12, 2014, 4:27pm

Yes that is true. That is how he built it. He mentioned that in a previous post. You can do (TEN.Customers=Customer) AND (TEN.Customers=VIP Customer)

or

(TEN.Customers=Customer) OR (TEN.Customers=VIP Customer)

Jesse · October 12, 2014, 4:29pm

An example he gave… This is not details tag but the expression syntax is same.

QMcKay · October 12, 2014, 4:31pm

If all of your Customers have a <space> in their name, would this work?

TENC.<entity type>                 Ticket Entity Name Contains

like:

(TENC.Customers= ) … contains a space

Jesse · October 12, 2014, 4:32pm

I really think (TEN.Customers=Customer) is what he is wanting. I will run some tests.

EDIT: Nope thats wrong… (TEN.Customer=Jason) works tho…

Jesse · October 12, 2014, 4:49pm

I tested this and it works… and @gerlandog your right we need another expression for filtering just Entity Type not just the entities in that type. Maybe @emre will see this.

I am wondering if this info is available at ticket level…I am looking but does Ticket contain the Entity Type?

EDIT: EntityTypeId maybe we can get a (ET=Customer) syntax or TET=X

QMcKay · October 12, 2014, 5:07pm

It should be. In @emre’s post, he indicates you can access Entities with EN or TEN, depending on the type of report… I assume the leading T indicates Ticket.

Also, the DB Table called [TicketEntities] links the Entities (Customers, Tables, Servers, etc.) to Tickets via [Ticket_Id].

Jesse · October 12, 2014, 5:07pm

I found it… on the TicketEntities table… EntityTypeId So we are just missing some syntax…

EDIT I tried all those to see if it was there just hidden… so far no go.

gerlandog · October 13, 2014, 12:47pm

It seems I cannot get the grip with PICOT REPORTS… I must be loosing my grip, LOL!!!

this report:

[!Totales por Grupo con TAGS):1,1,1]
>Grupo|Porcion|Total
{REPORT ORDER DETAILS:O.ItemGroup,O.PortionName,O.Quantity}

should give me total grouped in GROUPS and PORTIONS

What am I doing wrong??

Thanks!!!

G

EDIT: I got this working!!! was simple, had to add the [N] before Total column:

[!Totales por Grupo con TAGS):2,2,2,1]
>Grupo|Porcion|MenuItemName|[N]Total
{REPORT ORDER DETAILS:O.ItemGroup,O.PortionName,O.MenuItemName,O.Quantity}

gerlandog · October 15, 2014, 9:05pm

I am still struggling with this one…
I want to list all tickets that DO have a Customer… not a specific customer, any customer…

[Tickets por Cliente (TAGS):2,2,2,2,2,3, 2]
>Ticket|Fecha|Hora|Cliente|Telefono|Puntos|Total
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Customer,EC.Customer.Phone,EC.Customer.Points,T.TotalAmount:(TEN.Customer=Miro)}
>Total Ventas||||||${REPORT TICKET TOTAL:(TEN.Customer=Miro)}

Instead of (TEN.Customer=Miro) I should use a (TEM.Customer<>NULL) or something like that…

Thanks!!!

G.

gerlandog · October 15, 2014, 9:16pm

I found a small issue that for sure can be solved via Custom report tags, but do not know how…

I have a ticket that has only two orders and both are gifted…

and this report (sold items):

[!Totales por Grupo con TAGS):2,2,2, 1, 1]
>Grupo|Porcion|MenuItemName|[N]Cantidad|[C]Total
{REPORT ORDER DETAILS:O.ItemGroup,O.PortionName,O.MenuItemName,O.Quantity,O.TotalPrice}

shows me:

which is ok on items but not ok on the Total Price… t should be CERO.
When I had that report done with sql sentences, I had a case to calculate this kind of cases… is there any way to do it here too?

THANKS!!!

G.