Custom Report Tags in detail

Version 4 V4 Tutorial
1

We can prepare any type of reports with SQL and display results as a Custom Report. Alternatively we can use Custom Report Tags to prepare reports. If you need more information about preparing a custom report you can review this tutorial.

###Introduction

Basically there are four types of tags.

DETAILS TAGS: These tags contains DETAILS keyword in it and allows us to prepare table reports.
TOTAL TAGS: If a tag contains TOTAL keyword in it we can use this tag read sum of a value.
COUNT TAGS COUNT keyword indicates that tag returns count of something.
LIST TAGS Returns a comma separated value list. Useful for building sub reports.

Accounting related tags does not follow these rules as they don’t support expressions by nature.

##Syntax

- DETAILS:{TAG:<fields>:<expression>} 
- TOTAL:{TAG:<expression>} 
- COUNT:{TAG:<expression>} 
- LIST:{TAG:<expression>}

##Supported Tags
###Ticket Related

{REPORT TICKET DETAILS:<fields>:<expression>}
{REPORT TICKET TOTAL:<expression>}
{REPORT TICKET COUNT:<expression>}
{REPORT ORDER DETAILS:<fields>:<expression>}
{REPORT ORDER COUNT:<expression>}
{REPORT ORDER TOTAL:<expression>}
{REPORT PAYMENT DETAILS:<fields>:<expression>}
{REPORT PAYMENT COUNT:<expression>}
{REPORT PAYMENT TOTAL:<expression>}
{REPORT CALCULATION DETAILS:<fields>:<expression>}
{REPORT CALCULATION COUNT:<expression>}
{REPORT CALCULATION TOTAL:<expression>}
{REPORT TICKET PAYMENT TOTAL:<expression>}
{REPORT TICKET CALCULATION TOTAL:<expression>}
{TICKET TAG LIST:<expression>}

###Entity State Log related

{REPORT ENTITY STATE DETAILS:<fields>:<expression>}
{REPORT ENTITY STATE TOTAL DURATION:<expression>}
{REPORT ENTITY STATE TOTAL HOURS:<expression>}
{REPORT ENTITY STATE TOTAL MINUTES:<expression>}
{REPORT ENTITY STATE TOTAL SECONDS:<expression>}
{REPORT ENTITY STATE COUNT:<expression>}
{ENTITY STATE ENTITY LIST:<expression>}
{ENTITY STATE START DATE LIST:<expression>}
{ENTITY STATE END DATE LIST:<expression>}

Additionally these tags that allows you to query single ticket values. Might not be useful for reporting but you can use these tag to query ticket values inside rule constraints or use them inside printer templates. As expression syntax is same I’ve included these in this topic.

{TICKET ORDER TOTAL EXP:<expression>}
{TICKET ORDER COUNT EXP:<expression>}
{TICKET ORDER QUANTITY TOTAL EXP:<expression>}
{TICKET ORDER TAG TOTAL EXP:<expression>}
{TICKET ORDER STATE VALUE TOTAL EXP:<expression>}

On next post I’ll give more details about <expression> syntax.

8 Likes
2

###Expressions

We’ll use expressions to filter results returned by tags. If you are familiar with SQL, you can think these as Where sections.

For Example…

{REPORT PAYMENT TOTAL:}

This tag will display total of all payments that created inside reporting period. As it will total of all cash, credit card or other type of payments we’ll need a <expression> for more precise total. For reading a total of only Cash payments the syntax should be…

{REPORT PAYMENT TOTAL:(PT=Cash)}

###Syntax (<Field>=<Value>)

This syntax allows us to easily create filter expressions without thinking about common syntax features such as quotes, spaces, etc. <Field> value defines what we want to filter and <Value> part defines the filter value. Finally we’ll surround them with parenthesis to differ a value from other filters.

We can use multiple filters by using AND / OR keywords. For example

{REPORT PAYMENT TOTAL:(PT=Cash) OR (PT=Credit Card)}

… will display total of Cash + Credit Card payments.

We need to know what fields each tag type supports. For example Calculation related tags supports CT field.

{REPORT CALCULATION TOTAL:(CT=Discount)}

Some fields needs additional parameters. For example think about our Customers have a Customer Type Custom Field and I want to read total of all Cash payments VIP customers made.

{REPORT PAYMENT TOTAL:(PT=Cash) AND (EC.Customer.Customer Type=VIP)}

EC field needs an Entity Type Name and the name of the Custom Field.

###List of all possible expression fields

Supported by REPORT TICKET, ORDER, PAYMENT, CALCULATION and TICKET ORDER EXP 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

Supported by REPORT ORDER tags

OT.<tag name>                      Order Tagged With
OS.<state name>                    Order is in State
OU                                 Order User Name Equals
MT.<tag name>                      Order Menu Item Custom tag Equals
MG                                 Order Menu Item Group Equals

Supported by ENTITY STATE tags.

ELN                                Entity State Log Name Equals
ELSN                               State Name Equals
ELSS                               Start State Equals
ELES                               End State Equals
ELEN                               Entity Name Equals
ELEC.<field name>                  Entity Custom Field Equals
ELSD                               Start Date Equals
ELED                               End Date Equals
ELCD.<field name>                  Custom Data Equals

Supported by REPORT PAYMENT tags

PT                                 Payment Type Name Equals

Supported by REPORT CALCULATION tags

CT                                 Calculation Type Name Equals

On next post I’ll explain how DETAILS tags works.

3 Likes
3

These are the supported DETAILS tags.

{REPORT TICKET DETAILS:<fields>:<expression>}
{REPORT ORDER DETAILS:<fields>:<expression>}
{REPORT PAYMENT DETAILS:<fields>:<expression>}
{REPORT CALCULATION DETAILS:<fields>:<expression>}
{REPORT ENTITY STATE DETAILS:<fields>:<expression>}

These tags are useful to create table reports. In this post I’ll explain how <fields> section works.

This sample tag lists all tickets created by user named Emre

{REPORT TICKET DETAILS:<fields>:(TU=Emre)}

By using fields section we can configure what data our report displays. There are three types of fields.

1- Predefined fields that requires a parameter. For example EC.Customer.Phone field requires 2 parameters and returns ticket’s customer phone.
2- Predefined with no parameter. These starts with T. identifier. For example T.Department returns department name.
3- Database Fields. These also starts with T. identifier. They are directly mapped to database fields. If you see a TicketNumber field in Tickets table you can read that value with T.TicketNumber. That means if a database value not listed here you can read it by using it’s field name.

Hint: T.DepartmentId field returns DepartmentId field value of Tickets table. For some Id fields there is a predefined field named as T.Department. That’s why we have both predefined and database fields.

Now I’ll configure some fields for the sample tag.

{REPORT TICKET DETAILS:T.TicketNumber,T.Time,TN.TotalAmount:(TU=Emre)}

This report will display Ticket Number, Time and TotalAmount values.

Hint. You’ll notice Total Amount value uses TN instead of T. Adding N next to identifier will format it as a number. That means T.TotalAmount field will display it without formatting.

###List of all predefined fields.

TICKET DETAILS

EN.<entity type>                    Entity Name
EC.<entity type>.<field name>       Entity Custom Field Value
TS.<state name>                     Ticket State
TT.<tag group name>                 Ticket Tag Value
CA.<calculation type name>          Calculation Amount
PA.<payment type name>              Payment Amount 
T.Department                        Department Name
T.TicketType                        Ticket Type Name
T.User                              User Name
T.Date                              Ticket Date  
T.Time                              Ticket Time
T.Terminal                          Terminal Name
T.Tax                               Total Tax Amount
T.TotalAmount                       Total Amount
T.RemainingAmount                   Remaining Amount

ORDER DETAILS

OS.<state name>                     Order State
OSD.<state name>                    Order State Date
OST.<state name>                    Order State Time
OSV.<state name>                    Order State Value
OSU.<state name>                    Order State User 
OT<tag name>                        Order Tag
OTN.<tag name>                      Order Tag Note
OTU.<tag name>                      Order Tag User 
OTP.<tag name>                      Order Tag Price
MT.<tag name>                       Menu Item Custom Tag Value
O.Department                        Department Name
O.Date                              Order Date
O.Time                              Order Time
O.User                              User Name 
O.Terminal                          Terminal Name
O.Tax                               Tax Amount
O.ItemGroup                         Menu Item Group Name
O.ItemTag                           Menu Item Tag
O.Price                             Price
O.Quantity                          Quantity
O.OrderNumber                       Order Number
O.Total                             Order Total
O.TotalPrice                        Order Price * Order Quantity

PAYMENT DETAILS

P.Department                        Department Name
P.Date                              Payment Date
P.Time                              Payment Time
P.User                              User Name 
P.Terminal                          Terminal Name
P.Type                              Payment Type      
P.Amount                            Payment Amount 
P.TenderedAmount                    Tendered Amount

CALCULATION DETAILS

C.Date                              Calculation Date
C.Time                              Calculation Time
C.User                              User Name 
C.Type                              Calculation Type      
C.Amount                            Amount
C.CalculationAmount                 Calculation Amount

ENTITY STATE DETAILS

EN                                  Entity Name
EC.<field name>                     Entity Custom Field Value
CD.<field name>                     Custom Data Value
L.StartDate                         State Start Date
L.StartTime                         State Start Time
L.EndDate                           State End Date
L.EndTime                           State End Time
L.Seconds                           Total Seconds
L.Minutes                           Total Minutes
L.Hours                             Total Hours
L.Days                              Total Days
L.Second                            Duration Second
L.Minute                            Duration Minute
L.Hour                              Duration Hour
L.Day                               Duration Day

Hint: Log custom data syntax is <field1>=<value1>;<field2>=<value2>;...

###Field Calculations

This great feature allows you to make calculations by using field values.

Syntax: =<calculation>

Examples

{REPORT TICKET DETAILS:T.TicketNumber,T.Time,TN.TotalAmount,=2+2}

This tag displays the result of 2+2 on all lines.

{REPORT TICKET DETAILS:T.TicketNumber,T.Time,=[T.TotalAmount]*2}

This tag displays Ticket’s Total Amount multiplied by 2.

When you use fields in calculations you need to enclose them in square brackets. For example:

{REPORT TICKET DETAILS:T.TicketNumber,=[T.TotalAmount]*[EC.Employee.Comission]}
4 Likes
4

###Other Entity State Log Tags

{REPORT ENTITY STATE TOTAL DURATION:"<format>":<expression>}
Displays total duration of logs that matches expression. Format is the display format of duration. For example "dd.hh:mm:ss"
      
{REPORT ENTITY STATE TOTAL HOURS:<expression>}    
Total Hours for matching expression.

{REPORT ENTITY STATE TOTAL MINUTES:<expression>}
Total Minutes for matching expression.

{REPORT ENTITY STATE TOTAL SECONDS:<expression>}
Total Seconds for Matching expression.
1 Like
5

Thank you for this Tutorial this explains a lot. I was up late late last night building my Entity Log Reports. Phew so many options its going to take some practice for me to get it right.

6

I am having trouble with:

{REPORT ENTITY STATE DETAILS:<fields>,<expression>}

I will keep trying but my thought process must be wrong I can not get it to give me the data I want. I can get it to put data down but its not what I want.

I need to be able to use the Field Calculations but first I need to get just the basics down.

7

Umm sorry for the mistake. It should be

{REPORT ENTITY STATE DETAILS:<fields>:<expression>}

If you let me know what you need I can help you to prepare the report.

8

I thought that might be a mistake. I am trying to understand how it works so I can build something from Trial and error. I am not 100% sure how I want the total report to look like…

An example of what I was trying to pull up… Date| Entity Names | Total Hours for Date Some of my issues I have had for example: I tried using EN but it produced nothing… when I typed EN. it pulled up some of the Entity Names…

I was able to pull up total hours… but it listed the Name and total hours for each line… I could not get it for just a specific date…

Mind you this was late last night and I have not spent enough time with it. I plan to do some more tonight

EDIT: I think I may have it now. I was just struggling in my mind with how it works so I could build the output how I want.

Does this look right? I think I got ELSS and ELES wrong I am not sure that is doing anything. And why do I have to type EN. for it to pull up Entity Name I thought just EN without the . would work?

7c8fdc8db7841df1.png739×642 17 KB

9

Yes EN. is a little weird here. Use L.Entity.Name for now instead. On next version I’ll try to implement better syntax for that.

OK. If we have real data to work on it might be a little easier. As I don’t have that setup atm I’ll try to help with some guesswork :smile: Can you add a screenshot of your Log data?

10

Yes when I get home I will add screenshots and dive in deeper.

a593ca2a2f9c2cf9.png1542×553 41.5 KB

11

@Jesse’s data is probably very similar…

69aa5bed55b2db71.png859×391 28.6 KB

12

I can do it in SQL but I want to learn the report functions.

13

I thought @emre wanted sample data… I see you inserted some, beat me to it :slight_smile:

1 Like
14

Ok @emre thank you it looks like this was the culprit. I am now getting the right data. Now I will work on formatting it and running some calculations.

What would be the format for

ELSD                               Start Date Equals

I tried (ELSD=2014-10-06) it does not work.

15

Here is my first Test Report. It is showing Employee names and Total Hours …It is showing hours between PunchIn and PunchOut I filtered the PunchOut-PunchIn out of it. It is also showing a Total Hours for all Employees. I have not formatted this at all so its crude and ugly. I wanted to filter it down to specific Date but I couldnt get that to work.

e4df9e108d71ccd3.png1091×899 24.7 KB

16

Date filter is the report’s filter :slight_smile: Do you mean grouping by date?

@QMcKay can you send me a backup?

17

Sure, I’ll send you a backup without Transaction data.

Or here is some sample data for September from one of my Employees that I injected.

P.S. I am very happy to report that my SQL Pay Reports are calculating exactly the same hours and pay as my Excel spreadsheet.

use [SambaPOS4]
-- set the Entity Id
declare @eid int = 172
-- (optional) delete all previous records
--delete from [EntityStateLogs] where [EntityId]=@eid

INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-01 00:00:00'),'PunchOut',convert(datetime,'2014-09-01 00:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-02 07:00:00'),'PunchOut',convert(datetime,'2014-09-02 17:30:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-03 00:00:00'),'PunchOut',convert(datetime,'2014-09-03 00:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-04 07:00:00'),'PunchOut',convert(datetime,'2014-09-04 17:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-05 07:00:00'),'PunchOut',convert(datetime,'2014-09-05 14:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-06 07:30:00'),'PunchOut',convert(datetime,'2014-09-06 14:30:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-07 07:30:00'),'PunchOut',convert(datetime,'2014-09-07 14:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-08 00:00:00'),'PunchOut',convert(datetime,'2014-09-08 00:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-09 07:00:00'),'PunchOut',convert(datetime,'2014-09-09 17:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-10 00:00:00'),'PunchOut',convert(datetime,'2014-09-10 00:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-11 07:00:00'),'PunchOut',convert(datetime,'2014-09-11 17:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-12 07:00:00'),'PunchOut',convert(datetime,'2014-09-12 17:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-13 07:30:00'),'PunchOut',convert(datetime,'2014-09-13 15:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-14 07:30:00'),'PunchOut',convert(datetime,'2014-09-14 16:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-15 00:00:00'),'PunchOut',convert(datetime,'2014-09-15 00:00:00'),'ManualPunch~Holiday')

INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-16 07:00:00'),'PunchOut',convert(datetime,'2014-09-16 17:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-17 00:00:00'),'PunchOut',convert(datetime,'2014-09-17 00:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-18 07:00:00'),'PunchOut',convert(datetime,'2014-09-18 17:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-19 07:00:00'),'PunchOut',convert(datetime,'2014-09-19 14:00:00'),'ManualPunch~Holiday')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-20 07:30:00'),'PunchOut',convert(datetime,'2014-09-20 14:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-21 07:30:00'),'PunchOut',convert(datetime,'2014-09-21 15:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-22 00:00:00'),'PunchOut',convert(datetime,'2014-09-22 00:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-23 07:00:00'),'PunchOut',convert(datetime,'2014-09-23 17:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-24 00:00:00'),'PunchOut',convert(datetime,'2014-09-24 00:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-25 07:00:00'),'PunchOut',convert(datetime,'2014-09-25 17:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-26 07:00:00'),'PunchOut',convert(datetime,'2014-09-26 14:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-27 07:30:00'),'PunchOut',convert(datetime,'2014-09-27 15:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-28 07:30:00'),'PunchOut',convert(datetime,'2014-09-28 14:30:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-29 00:00:00'),'PunchOut',convert(datetime,'2014-09-29 00:00:00'),'ManualPunch~')
INSERT INTO [EntityStateLogs] VALUES ('TimeClock',@eid,'TCStatus','PunchIn',convert(datetime,'2014-09-30 07:00:00'),'PunchOut',convert(datetime,'2014-09-30 17:00:00'),'ManualPunch~')

SELECT
 sl.[Id]
,sl.[Name]
,[EntityId]
,[StateName]
,[StartState]
,[StartStateDate]
,[EndState]
,[EndStateDate]
,sl.[CustomData]
FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
where [EntityId]=@eid
order by [StartStateDate]
18

I only need log data. An sql export will also do the job.

19

Ok, here is a scripted backup… it contains 4 Employees and their hours for the month of September.

EntityStateLogs_backup.zip (2.7 KB)

20

Yes sorry I meant Grouping by Date.