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

http://forum.sambapos.com/t/new-how-to-use-custom-reports-module-1-0/2142

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.

Note: 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 allow 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 that already 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>}

Expressions

It will be used 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 it will be needed 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 you to easily create filter expressions without thinking about common syntax features such as quotes, spaces, etc. <Field> value defines what you want to filter and `<Value>` part defines the filter value. Finally you’ll surround them with parenthesis to differ a value from other filters.

You 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.

It is necessary to know what fields each tag type supports. For example
Calculation related tags supports CT field.

{REPORT CALCULATION TOTAL:(CT=Discount)}

Some fields need additional parameters. For example think about your Customers have a Customer TypeCustom Field and you 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

* Use the Payment Account Name to filter by Payment Type.

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
PU Payment User Name Equals

Supported by REPORT CALCULATION tags

CT Calculation Type Name Equals

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 it will be explained how <fields> section works.

This sample tag lists all tickets created by user named Nizam

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

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

  1. Predefined fields that require 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.

Tip; 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 it will be shown how to configure some fields for the sample tag.

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

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

Tip; You’ll notice Total Amount value uses TN. prefix 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 account 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.MenuItemName Menu Item Name
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

EC.<field name> Entity Custom Field Value
CD.<field name> Custom Data Value
L.EntityName Entity Name
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

Tip; 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]}

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.

Formatting Syntax

Money function =M([<field>]) will format value as windows regional currency format. F,C,N functions to format results as numbers. For example =C(2500*2)