{"id":14011,"date":"2018-05-08T07:41:05","date_gmt":"2018-05-08T07:41:05","guid":{"rendered":"https:\/\/kb.sambapos.com\/?p=14011"},"modified":"2021-01-29T16:23:39","modified_gmt":"2021-01-29T16:23:39","slug":"9-1-3-custom-reporting-tags","status":"publish","type":"post","link":"https:\/\/kb.sambapos.com\/en\/9-1-3-custom-reporting-tags\/","title":{"rendered":"9.1.3. Custom Reporting Tags"},"content":{"rendered":"

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.<\/p>\n

http:\/\/forum.sambapos.com\/t\/new-how-to-use-custom-reports-module-1-0\/2142<\/a><\/p>\n

Introduction<\/h2>\n
\n

Basically there are four types of tags.<\/p>\n\n\n\n\n\n\n
DETAILS TAGS<\/span><\/th>\nThese tags contains DETAILS<\/code> keyword in it and allows us to prepare table reports.<\/span><\/td>\n<\/tr>\n
TOTAL TAGS<\/span><\/th>\nIf a tag contains TOTAL<\/code> keyword in it we can use this tag read sum of a value.<\/span><\/td>\n<\/tr>\n
COUNT TAGS<\/span><\/th>\nCOUNT <\/code>keyword indicates that tag returns count of something.<\/span><\/td>\n<\/tr>\n
LIST TAGS<\/span><\/th>\nReturns a comma separated value list. Useful for building sub reports.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Note: Accounting related tags does not follow these rules as they don’t support expressions by nature.
\nSyntax
\nDETAILS:{TAG:<fields>:<expression>}
\nTOTAL:{TAG:<expression>}
\nCOUNT:{TAG:<expression>}
\nLIST:{TAG:<expression>}
\n<\/code><\/p>\n

SUPPORTED TAGS<\/p>\n

Ticket Related<\/code>
\n
\n{REPORT TICKET DETAILS:<fields>:<expression>}
\n{REPORT TICKET TOTAL:<expression>}
\n{REPORT TICKET COUNT:<expression>}
\n{REPORT ORDER DETAILS:<fields>:<expression>}
\n{REPORT ORDER COUNT:<expression>}
\n{REPORT ORDER TOTAL:<expression>}
\n{REPORT PAYMENT DETAILS:<fields>:<expression>}
\n{REPORT PAYMENT COUNT:<expression>}
\n{REPORT PAYMENT TOTAL:<expression>}
\n{REPORT CALCULATION DETAILS:<fields>:<expression>}
\n{REPORT CALCULATION COUNT:<expression>}
\n{REPORT CALCULATION TOTAL:<expression>}
\n{REPORT TICKET PAYMENT TOTAL:<expression>}
\n{REPORT TICKET CALCULATION TOTAL:<expression>}
\n{TICKET TAG LIST:<expression>}<\/code><\/p>\n


\nEntity State Log Related<\/code><\/p>\n


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

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.<\/p>\n

{TICKET ORDER TOTAL EXP:<expression>}
\n{TICKET ORDER COUNT EXP:<expression>}
\n{TICKET ORDER QUANTITY TOTAL EXP:<expression>}
\n{TICKET ORDER TAG TOTAL EXP:<expression>}
\n{TICKET ORDER STATE VALUE TOTAL EXP:<expression>}<\/code><\/p>\n

<\/code><\/code><\/p>\n

Expressions<\/h2>\n

<\/code><\/code><\/p>\n

\n

It will be used expressions to filter results returned by tags. If you are familiar with SQL, you can think these as `Where` sections.<\/p>\n

For Example\u2026<\/strong><\/p>\n

{REPORT PAYMENT TOTAL:}<\/code>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><\/code> for more precise total. For reading a total of only Cash<\/code> payments the syntax should be.<\/p>\n

{REPORT PAYMENT TOTAL:(PT=Cash)}<\/code><\/p>\n

Syntax<\/span> (<Field>=<Value>)<\/strong><\/code><\/p>\n

<\/code><\/code>This syntax allows you to easily create filter expressions without thinking about common syntax features such as quotes, spaces, etc. <Field><\/code> 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.<\/p>\n

You can use multiple filters by using AND \/ OR<\/code> keywords. For example
\n{REPORT PAYMENT TOTAL:(PT=Cash) OR (PT=Credit Card)}<\/code><\/p>\n

\u2026 will display total of Cash + Credit Card<\/code> payments.<\/p>\n

It is necessary to know what fields each tag type supports. For example
\nCalculation related tags supports CT<\/code> field.<\/p>\n

{REPORT CALCULATION TOTAL:(CT=Discount)}<\/code><\/p>\n

Some fields need additional parameters. For example think about your Customers have a Customer Type<\/code>Custom Field and you want to read total of all Cash payments VIP<\/code> customers made.<\/p>\n

{REPORT PAYMENT TOTAL:(PT=Cash) AND (EC.Customer.Customer Type=VIP)}<\/code><\/p>\n

EC<\/code> field needs an Entity Type Name<\/code> and the name of the Custom Field<\/code>.<\/p>\n<\/div>\n

List of all possible expression fields<\/h3>\n
\n

Supported by REPORT TICKET, ORDER, PAYMENT, CALCULATION<\/code> and TICKET ORDER EXP<\/code> tags.<\/p>\n\n\n\n
TEN.<entity type> Ticket Entity Name Equals
\nTENC.<entity type> Ticket Entity Name Contains
\nTEC.<entity type>.<custom field> Ticket Entity Field Equals
\nTECC.<entity type>.<custom field> Ticket Entity Field Contains
\nTU Ticket User Name Equals
\nDE Ticket Department Name Equals
\nTE Ticket Terminal Name
\nTY Ticket Type
\nPA Ticket Contains a Payment *
\nCA Ticket Contains a Calculation
\nTCET Ticket Contains an Entity Type
\nTT.<tag name> Ticket Tagged With
\nTS.<state name> Ticket is in State<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

* Use the Payment Account Name to filter by Payment Type.<\/p>\n

Supported by REPORT ORDER<\/code> tags<\/p>\n\n\n\n
OT.<tag name> Order Tagged With
\nOS.<state name> Order is in State
\nOU Order User Name Equals
\nMT.<tag name> Order Menu Item Custom tag Equals
\nMG Order Menu Item Group Equals<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Supported by ENTITY STATE<\/code> tags.<\/p>\n\n\n\n
ELN Entity State Log Name Equals
\nELSN State Name Equals
\nELSS Start State Equals
\nELES End State Equals
\nELEN Entity Name Equals
\nELEC.<field name> Entity Custom Field Equals
\nELSD Start Date Equals
\nELED End Date Equals
\nELCD.<field name> Custom Data Equals<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Supported by REPORT PAYMENT<\/code> tags<\/p>\n\n\n\n
PT Payment Type Name Equals
\nPU Payment User Name Equals<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Supported by REPORT CALCULATION<\/code> tags<\/p>\n\n\n\n
CT Calculation Type Name Equals<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

These are the supported DETAILS<\/code> tags.<\/p>\n\n\n\n
{REPORT TICKET DETAILS:<fields>:<expression>}
\n{REPORT ORDER DETAILS:<fields>:<expression>}
\n{REPORT PAYMENT DETAILS:<fields>:<expression>}
\n{REPORT CALCULATION DETAILS:<fields>:<expression>}
\n{REPORT ENTITY STATE DETAILS:<fields>:<expression>}<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

These tags are useful to create table reports. In this post it will be explained how <fields><\/code> section works.<\/p>\n

This sample tag lists all tickets created by user named Nizam<\/p>\n\n\n\n
{REPORT TICKET DETAILS:<fields>:(TU=Nizam)}<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

By using fields section you can configure what data your report displays. There are three types of fields.<\/p>\n

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

    Tip; <\/span>T.DepartmentId<\/code> field returns DepartmentId field value of Tickets table. For some Id fields there is a predefined field named as T.Department<\/code>. That’s why we have both predefined and database fields.<\/p>\n

    Now it will be shown how to configure some fields for the sample tag.<\/p>\n\n\n\n
    {REPORT TICKET DETAILS:T.TicketNumber,T.Time,TN.TotalAmount:(TU=Nizam)}<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    This report will display Ticket Number, Time and TotalAmount<\/code> values.<\/p>\n

    Tip;<\/span> You’ll notice Total Amount value uses TN. prefix instead of T. Adding N<\/code> next to identifier will format it as a number. That means T.TotalAmount<\/code> field will display it without formatting.<\/p>\n

    List of all predefined fields.<\/h3>\n
    \n

    TICKET DETAILS<\/strong><\/p>\n\n\n\n
    EN.<entity type> Entity Name
    \nEC.<entity type>.<field name> Entity Custom Field Value
    \nTS.<state name> Ticket State
    \nTT.<tag group name> Ticket Tag Value
    \nCA.<calculation type name> Calculation Amount
    \nPA.<payment account name> Payment Amount
    \nT.Department Department Name
    \nT.TicketType Ticket Type Name
    \nT.User User Name
    \nT.Date Ticket Date
    \nT.Time Ticket Time
    \nT.Terminal Terminal Name
    \nT.Tax Total Tax Amount
    \nT.TotalAmount Total Amount
    \nT.RemainingAmount Remaining Amount<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    ORDER DETAILS<\/strong><\/p>\n\n\n\n
    OS.<state name> Order State
    \nOSD.<state name> Order State Date
    \nOST.<state name> Order State Time
    \nOSV.<state name> Order State Value
    \nOSU.<state name> Order State User
    \nOT<tag name> Order Tag
    \nOTN.<tag name> Order Tag Note
    \nOTU.<tag name> Order Tag User
    \nOTP.<tag name> Order Tag Price
    \nMT.<tag name> Menu Item Custom Tag Value
    \nO.MenuItemName Menu Item Name
    \nO.Department Department Name
    \nO.Date Order Date
    \nO.Time Order Time
    \nO.User User Name
    \nO.Terminal Terminal Name
    \nO.Tax Tax Amount
    \nO.ItemGroup Menu Item Group Name
    \nO.ItemTag Menu Item Tag
    \nO.Price Price
    \nO.Quantity Quantity
    \nO.OrderNumber Order Number
    \nO.Total Order Total
    \nO.TotalPrice Order Price * Order Quantity<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    PAYMENT DETAILS<\/strong><\/p>\n\n\n\n
    P.Department Department Name
    \nP.Date Payment Date
    \nP.Time Payment Time
    \nP.User User Name
    \nP.Terminal Terminal Name
    \nP.Type Payment Type
    \nP.Amount Payment Amount
    \nP.TenderedAmount Tendered Amount<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    CALCULATION DETAILS<\/strong><\/p>\n\n\n\n
    C.Date Calculation Date
    \nC.Time Calculation Time
    \nC.User User Name
    \nC.Type Calculation Type
    \nC.Amount Amount
    \nC.CalculationAmount Calculation Amount<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    ENTITY STATE DETAILS<\/strong><\/p>\n\n\n\n
    EC.<field name> Entity Custom Field Value
    \nCD.<field name> Custom Data Value
    \nL.EntityName Entity Name
    \nL.StartDate State Start Date
    \nL.StartTime State Start Time
    \nL.EndDate State End Date
    \nL.EndTime State End Time
    \nL.Seconds Total Seconds
    \nL.Minutes Total Minutes
    \nL.Hours Total Hours
    \nL.Days Total Days
    \nL.Second Duration Second
    \nL.Minute Duration Minute
    \nL.Hour Duration Hour
    \nL.Day Duration Day<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \n

    Tip;<\/span> Log custom data syntax is <field1>=<value1>;<field2>=<value2>;\u2026<\/code><\/p>\n<\/div>\n

    Field Calculations<\/h3>\n
    \n

    This great feature allows you to make calculations by using field values.<\/p>\n

    Syntax:<\/span> =<calculation><\/strong><\/p>\n

    Examples<\/p>\n\n\n\n
    {REPORT TICKET DETAILS:T.TicketNumber,T.Time,TN.TotalAmount,=2+2}<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    This tag displays the result of 2+2 on all lines.<\/p>\n\n\n\n
    {REPORT TICKET DETAILS:T.TicketNumber,T.Time,=[T.TotalAmount]*2}<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    This tag displays Ticket’s Total Amount multiplied by 2.<\/p>\n

    When you use fields in calculations you need to enclose them in square brackets. For example:<\/p>\n\n\n\n
    {REPORT TICKET DETAILS:T.TicketNumber,=[T.TotalAmount]*[EC.Employee.Comission]}<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Other Entity State Log Tags<\/h2>\n\n\n\n
    {REPORT ENTITY STATE TOTAL DURATION:\"<format>\":<expression>}
    \nDisplays total duration of logs that matches expression. Format is the display format of duration. For example \"dd.hh:mm:ss\"<\/code><\/p>\n

    {REPORT ENTITY STATE TOTAL HOURS:<expression>}
    \nTotal Hours for matching expression.<\/code><\/p>\n

    {REPORT ENTITY STATE TOTAL MINUTES:<expression>}
    \nTotal Minutes for matching expression.<\/code><\/p>\n

    {REPORT ENTITY STATE TOTAL SECONDS:<expression>}
    \nTotal Seconds for Matching expression.<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Formatting Syntax<\/h2>\n
    \n

    M<\/strong>oney function =M([<field>])<\/code> will format value as windows regional currency format. F,C,N functions to format results as numbers. For example =C(2500*2)<\/code><\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

    <\/code><\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

    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 […]<\/p>\n","protected":false},"author":11,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-14011","post","type-post","status-publish","format-standard","hentry","category-reports"],"_links":{"self":[{"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/posts\/14011","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/comments?post=14011"}],"version-history":[{"count":25,"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/posts\/14011\/revisions"}],"predecessor-version":[{"id":25538,"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/posts\/14011\/revisions\/25538"}],"wp:attachment":[{"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/media?parent=14011"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/categories?post=14011"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/tags?post=14011"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}