Custom Report: Sale By Table Listing

i want to have a detailed sale report for each floor with order details and total. But i am stuck in the begining.

i cant retrieve tables details by floor . i used this format

[Table Listing:1,2,2,2, 2]
Ticket No|Date|Time|Table|Amount
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,T.TotalAmount:(EN.Tables=B%)}

and i received this error

Please indicate where i am wrong?

thanks

What does it intended to do?

Probably a wildcard for a LIKE statement in SQL. He wants all tables that start with β€˜B’.

yes filtering all tables with starting with B.

i can do it in sql like this:

SELECT
T.TicketNumber,
MIN(CONVERT(nvarchar,T.DATE,105)) AS DATE,
MIN(CONVERT(nvarchar,T.DATE,108)) AS TIME,
COALESCE(MIN(CASE WHEN TE.EntityTypeId = 2 THEN TE.EntityName ELSE NULL END),β€˜-’) AS >[TABLE Name],
MIN(T.TotalAmount) AS TotalAmount
FROM Tickets AS T
LEFT OUTER JOIN TicketEntities AS TE ON TE.Ticket_Id = T.Id
WHERE DATE > β€˜{Start}’ AND DATE < β€˜{End}’ And TE.EntityName like β€˜@1’
GROUP BY T.TicketNumber
ORDER BY DATE,TIME

Try (TENC.Tables=B)

Here are the supported expression tags.

TEN.<entity type>                  Ticket Entity Name Equals
TENC.<entity type>                 Ticket Entity Name Contains
TEC.<entity type>.<custom field>   Ticket Entity Field Equals
TECC.<entity type>.<custom field>  Ticket Entity Field Contains
TU                                 Ticket User Name Equals
DE                                 Ticket Department Name Equals
TE                                 Ticket Terminal Name
TY                                 Ticket Type
PA                                 Ticket Contains a Payment
CA                                 Ticket Contains a Calculation
TCET                               Ticket Contains an Entity Type
TT.<tag name>                      Ticket Tagged With
TS.<state name>                    Ticket is in State

More info

http://sambapos.com/wiki/doku.php?id=custom_reporting_tags

thanku @emre i overlooked the TENC tag but i still have one problem i can use in this case

table:
A1 to A10
B1 to B10
but how to do if i want to put a range
1 to 10
for example:
TE.EntityName between β€˜1’ and β€˜20’

What kind of a table naming scheme you have ? I’m afraid your next request will be how to list odd table numbers etc… :slight_smile: OK j/k. Create a custom field for table entity type something like β€œTable Group”. Set 1-20 table’s group names as something related (eg. garden, upstairs, 1-20, etc) and use
(TEC.Tables.Table Group=garden) expression.

2 Likes

lol but in custom field i can add one value against one entity what i want is something like this

SELECT
T.TicketNumber,
MIN(CONVERT(nvarchar,T.DATE,105)) AS DATE,
MIN(CONVERT(nvarchar,T.DATE,108)) AS TIME,
COALESCE(MIN(CASE WHEN TE.EntityTypeId = 2 THEN TE.EntityName ELSE NULL END),β€˜-’) AS [TABLE Name],
MIN(T.TotalAmount) AS TotalAmount
FROM Tickets AS T
LEFT OUTER JOIN TicketEntities AS TE ON TE.Ticket_Id = T.Id
WHERE DATE > β€˜{Start}’ AND DATE < β€˜{End}’ And TE.EntityName between β€˜1’ and β€˜20’
GROUP BY T.TicketNumber
ORDER BY DATE,TIME

Why you need to specifically filter tables from 1 to 20?

i need to know sales by table.

OK. I mean why tables 1 to 20? I’m trying to understand why you need to see these tables separately. For example are these VIP tables? Located somewhere important?

@emre i hope this gives a better understanding
i have achieved the result by using sql and ticket tag but the result are not matching. can you help to know where things went wrong.

Table Sale Listing Via SQL:

Query:

SELECT T.TicketNumber,
MIN(CONVERT(nvarchar,T.DATE,105)) AS DATE,
MIN(CONVERT(nvarchar,T.DATE,108)) AS TIME,
COALESCE(MIN(CASE WHEN TE.EntityTypeId = 2 THEN TE.EntityName ELSE NULL END),β€˜-’) AS >[TABLE Name],
min(substring(cast(TicketTags as nvarchar(4000)),
CharIndex(β€˜β€œSharing β€˜,cast(TicketTags as nvarchar(4000)))+8,
CharIndex(’”’,cast(TicketTags as nvarchar(4000)),
CharIndex('"Sharing β€˜,cast(TicketTags as nvarchar(4000)))+8
)- CharIndex(’"Sharing ',cast(TicketTags as nvarchar(4000)))-8)),
MIN(T.TotalAmount) AS TotalAmount
FROM Tickets AS T
LEFT OUTER JOIN TicketEntities AS TE ON TE.Ticket_Id = T.Id
WHERE DATE > β€˜{Start}’ AND DATE < β€˜{End}’ And TE.EntityName like β€˜@1’
GROUP BY T.TicketNumber
ORDER BY DATE,TIME

Query2

SELECT
T.TicketNumber,
MIN(CONVERT(nvarchar,T.DATE,105)) AS DATE,
MIN(CONVERT(nvarchar,T.DATE,108)) AS TIME,
COALESCE(MIN(CASE WHEN TE.EntityTypeId = 2 THEN TE.EntityName ELSE NULL END),β€˜-’) AS [TABLE Name],
min(substring(cast(TicketTags as nvarchar(4000)),
CharIndex(β€˜β€œSharing β€˜,cast(TicketTags as nvarchar(4000)))+8,
CharIndex(’”’,cast(TicketTags as nvarchar(4000)),
CharIndex('"Sharing β€˜,cast(TicketTags as nvarchar(4000)))+8
)- CharIndex(’"Sharing ',cast(TicketTags as nvarchar(4000)))-8)),
MIN(T.TotalAmount) AS TotalAmount
FROM Tickets AS T
LEFT OUTER JOIN TicketEntities AS TE ON TE.Ticket_Id = T.Id
WHERE DATE > β€˜{Start}’ AND DATE < β€˜{End}’ And (TE.EntityName between β€˜1’ and β€˜20’ or TE.EntityName like β€˜Room’)
GROUP BY T.TicketNumber
ORDER BY DATE,TIME

Total:

SELECT β€˜Totals’,β€˜β€™,β€˜β€™,β€˜β€™,β€˜β€™,β€˜β€™,
SUM(T.TotalAmount) AS TotalAmount
FROM Tickets AS T
LEFT OUTER JOIN TicketEntities AS TE ON TE.Ticket_Id = T.Id
WHERE T.DATE > β€˜{Start}’ AND T.DATE < β€˜{End}’ And TE.EntityName like β€˜@1’

SELECT
β€˜Totals’,β€˜β€™,β€˜β€™,β€˜β€™,β€˜β€™,β€˜β€™,
SUM(T.TotalAmount) AS TotalAmount
FROM Tickets AS T
LEFT OUTER JOIN TicketEntities AS TE ON TE.Ticket_Id = T.Id
WHERE DATE > β€˜{Start}’ AND DATE < β€˜{End}’ And (TE.EntityName between β€˜1’ and β€˜20’ or TE.EntityName like β€˜Room’)

Using Report Tag:

[Table Listing:2,2,2,2,2,2, 2]
Ticket No|Date|Time|Table|Sharing|Individual|Amount
1st Floor
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=1)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=2)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=3)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=4)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=5)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=6)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=7)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=8)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=9)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=10)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=11)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=12)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TENC.Tables=Room)}
@@TableSaleByNoTotal
2nd Floor
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TENC.Tables=A)}
@@TableSaleTotal:A%
BB
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TENC.Tables=B)}

@@TableSaleTotal:B%
Grant Total||||||{REPORT TICKET TOTAL}

i am calling sql function to get the subtotal

That was my point. Table names are alphanumeric data and you can’t make numeric comparisons without casting table names to to numeric. When alphabetically sorted 3 is greater than 20. That’s why only tables that starts with 1 or 2 appears on your SQL report.

Casting table name to numeric is a little tricky as your table names also contains non numerical names. Simplest solution will be chaining all possible values with OR like you did for report tag solution. For example TE.EnitityName = '1' OR TE.EntityName='2' OR ...

… also you can summarize that

{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=1)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=2)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=3)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=4)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=5)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=6)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=7)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=8)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=9)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=10)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=11)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=12)}

as…

[Table Listing:2,2,2,2,2,2, 2]
Ticket No|Date|Time|Table|Sharing|Individual|Amount
1st Floor
@1,2,3,4,5,6,7,8,9,10,11,12
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=$1)}
....
<rest of the report beginning with Room.>

…also

{REPORT TICKET TOTAL:(TENC.Tables=B)}

Should display total of tickets that table name contains B. However I’ll strongly recommend creating a custom field (for example named as Group Code) for table entity type. Set group code as 1st Floor for all tables located at first floor and use (TEC.Tables.Group Code=1st Floor) as expression. Pleas note setting a custom field will not work for past tickets as we store custom field values inside tickets. New created tickets will appear fine.

1 Like

ok thankyou alot…cleared my concepts

[Table Listing:2,2,2,2,2,2, 2]
Ticket No|Date|Time|Table|Sharing|Individual|Amount
1st Floor
@1,2,3,4,5,6,7,8,9,10,11,12
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=$1)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TENC.Tables=Room)}

1st Floor Total:@@TableSaleByNoTotal
2nd Floor
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TENC.Tables=A)}
2nd Floor Total:||||||{REPORT TICKET TOTAL:(TENC.Tables=A)}
B B
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TENC.Tables=B)}
B B Total:||||||{REPORT TICKET TOTAL:(TENC.Tables=B)}
Grant Total||||||{REPORT TICKET TOTAL}