Some Reports (Custom Reports)

Version 4 V4 Custom Reports
1

Some of mine… hope someone can use them!!!

  1. Tickets by hour

    [Tickets por Hora:2,1,1,1,1,1,1]

    Dia||Total|17-18|19-20|21-22|23-00
    @@TicketporHora
    @@TotalTicketsporHora

    [Totales por Hora:2,1,1,1,1,1,1]

    Dia||Total|17-18|19-20|21-22|23-00
    @@TotalesporHora
    @@TotalTotalesporHora

    @@TotalesporHora:
    SELECT
    min(datename(dw,[Date])) as Dia,
    min(convert(nvarchar,Tickets.Date,105)) as Date,
    COUNT(1),
    COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 17 AND 18 THEN 1 ELSE NULL END),
    COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 19 AND 20 THEN 1 ELSE NULL END),
    COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 21 AND 22 THEN 1 ELSE NULL END),
    COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 23 AND 24 THEN 1 ELSE NULL END)

     FROM Tickets
 
 Where Date > '{Start}' and Date < '{End}' 
 
 GROUP BY Convert(nvarchar(20),Date,105)

    @@TotalTotalesporHora:
    SELECT
    ‘Total de Tickets:’,
    ‘’,
    COUNT(1),
    COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 17 AND 18 THEN 1 ELSE NULL END),
    COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 19 AND 20 THEN 1 ELSE NULL END),
    COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 21 AND 22 THEN 1 ELSE NULL END),
    COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 23 AND 24 THEN 1 ELSE NULL END)

     FROM Tickets
 
 WHERE Date > '{Start}' and Date < '{End}'

  2. Sales Report

    [Ventas:1,2,2,2,2,2,2,3]

    Ticket|Fecha|Hora|Mesa|Neto Ticket|Taxa Serviço|Descuento|Total Ticket
    @@ReporteVentas
    @@TotalReporteVentas

    @@ReporteVentas
    SELECT
    T.TicketNumber as TicketNumber,
    min(convert(nvarchar,T.Date,105)) as Date,
    min(convert(nvarchar,T.Date,108)) as Time,
    min(TE.EntityName) as TableName,
    min(T.TotalAmount)-SUM(Cal.CalculationAmount) as NetoTicket,
    SUM(case
    when Cal.CalculationTypeId = 4 then Cal.CalculationAmount
    else 0 end) as Taxa,
    SUM(case
    when Cal.CalculationTypeId = 1 then Cal.CalculationAmount
    else 0 end) as Desconto,
    min(T.TotalAmount) as TotalTicket

    FROM Tickets AS T

    LEFT OUTER JOIN TicketEntities as TE on TE.Ticket_Id = T.Id
    LEFT OUTER JOIN Calculations as Cal on Cal.TicketId = T.Id

    WHERE Date > ‘{Start}’ and Date < ‘{End}’

    GROUP BY T.TicketNumber

    ORDER BY Date,Time

    @@TotalReporteVentas
    SELECT
    ‘TOTALES’,
    ‘’,
    ‘’,
    ‘’,
    SUM(case
    when Cal.CalculationTypeId = 1 then 0
    else T.TotalAmount end)-SUM(Cal.CalculationAmount) as NetoTicket,
    SUM(case
    when Cal.CalculationTypeId = 4 then Cal.CalculationAmount
    else 0 end) as Taxa,
    SUM(case
    when Cal.CalculationTypeId = 1 then Cal.CalculationAmount
    else 0 end) as Desconto,
    SUM(case
    when Cal.CalculationTypeId = 1 then 0
    else T.TotalAmount end) as TotalTicket

    FROM Tickets AS T

    LEFT OUTER JOIN Calculations as Cal on Cal.TicketId = T.Id

    WHERE Date > ‘{Start}’ and Date < ‘{End}’

  3. Stock with unit conversion:

    [Stock:2,1,1,1]

    Item|Stock Fisico|Stock UNI|Costo
    @@Stock

    SELECT [InventoryItemName],
    ([InStock]+[Added]-[Removed]-[Consumption]) as StockFisico,
    ([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier] as StockFisicoUN,
    [Cost]

    FROM [PeriodicConsumptionItems]

    WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)

    ORDER BY [InventoryItemId]

  4. Sales Summary:

    [Ventas:3,1]

    Resumen Ventas
    Ventas|R$ [=F(TN(‘{ACCOUNT CREDIT TOTAL:Sales}’),‘#,#0.00’)]
    Taxa Serviço|R$ [=F(TN(‘{ACCOUNT CREDIT TOTAL:Taxa Serviço}’),‘#,#0.00’)]
    Descuentos|R$ [=F(TN(‘{ACCOUNT DEBIT TOTAL:Discount}’),‘#,#0.00’)]
    Total|R$ [=F(TN(‘{ACCOUNT CREDIT TOTAL:Sales}’)+TN(‘{ACCOUNT CREDIT TOTAL:Taxa Serviço}’)-TN(‘{ACCOUNT DEBIT TOTAL:Discount}’),‘#,#0.00’)]

    [Resumen:3,1]

    Ingresos|
    Dinheiro|R$ [=F(TN(‘{ACCOUNT TRANSACTION TOTAL:Payment Transaction:Dinheiro}’),‘#,#0.00’)]
    Cartao Credito|R$ [=F(TN(‘{ACCOUNT TRANSACTION TOTAL:Payment Transaction:Cartao}’),‘#,#0.00’)]
    Cuenta Corriente|R$ [=F(TN(‘{ACCOUNT TOTAL:Customer Accounts}’),‘#,#0.00’)]
    Total|R$ [=F(TN(‘{ACCOUNT TRANSACTION TOTAL:Payment Transaction:Payment Accounts}’)+TN(‘{ACCOUNT TOTAL:Customer Accounts}’),‘#,#0.00’)]

G.

4 Likes
2

Thanks gerlandog for the custom reports, do you by chance have any custom report that would have these fields

default.JPG1035×437 139 KB

3

You’ll use {REPORT ORDER DETAILS:X} tag.

There is a sample here

Available fields for report order details tag.

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

1 Like
4

Thanks Emre…I have visited the links and have so many field details helpful, editing to my taste…is there any “expression field” dat displays customer name found in a ticket or order…

5

try EN.Customers field.

1 Like
6

Thanks Emre…that worked!

7

I can’t see a way to add cost prices to my custom report…searched the forum… please point me in the right direction…

8
1 Like