Print Custom Reports Operation

 

Touch > Tools > Select right arrow once > Server Tools > Print Custom Reports

 

Description

 

This menu item allows for the creation of custom SQL scripts that will print out a column formatted report on Receipt Printers directly from the SwiftPOS Touch terminal. Sometimes, there may be a requirement for more information than there is available in the standard supplied X/Z Reports. This feature makes a request to the SwiftPOS Back Office and returns the data back to the SwiftPOS Touch terminal to print on the Receipt Printer.

 

To Be Considered

 

  1. SwiftPOS is available to write custom scripts, please contact your reseller to obtain a quote.

 

Pre-Requisites

 

  1. The SwiftPOS Connect service must be running.
  2. One or more Custom Reports in the form of a SQL Scripts (.SQL) must be available in the C:\Users\Public\Documents\SwiftPOS Connect\Custom Reports folder.

 

Custom Reports

 

 

This screen is accessed from here.

 

  • Report List - Select from the list of Custom Reports available. Once a report is selected, it can be edited/printed or deleted.
  • Report Name - Displays/Enter an appropriate Report Name.
  • Report - Displays the SQL Script (.SQL) that was selected to generate the selected Report. Use the Select Report button to open the Custom Report Search screen to select the SQL Script (.SQL) that are currently available in the folder mentioned above in the Pre-requisites.
  • Date Range - Select from the drop down list of Periods, or select a From/To date range.
  • Family - Select to open the Family search screen and select a Family of Products to filter the report by.
  • Range - Select to open the Product Range search screen and select a Product Range to filter the report by.
  • First/Last PLU - Select to filter by a range of Products.

 

Footer

 

  • Add Report - Select to add a report.
  • Remove - Select to remove the selected report.
  • Print - Select to print the report.
  • Close - Select to exit.

 

Operation

 

  1. Create a SQL Script (see below) and place the SQL file in the folder mentioned above in Pre-requisites.
  2. Send a Full Update to the SwiftPOS Touch terminals. This will ensure the reports placed in the above folder will be available to be added at SwiftPOS Touch terminals.
  3. To add a report and include it as part of, for example the Z Report, do as follows:
     
    1. In Touch > Tools > Select right arrow once > Server Tools > Print Custom Reports.
       
      1. Select Add Report.
      2. Enter a Report Name.
      3. Select the Select Report button and select one of the reports in the Custom Reports search screen.
      4. Select a Date Range or From/To dates.
      5. Select a Family, Product Range and/or PLU Range to filter the report.
      6. Select the Print button to request data to be retrieved from the Back Office and retuned to the SwiftPOS Touch Terminal.
      7. Select Close to exit the Custom Reports screen.
         
    2. Now using the Add Custom button Report Settings screen, select the Report to be added from the Custom Reports search screen.
    3. Save the changes to the Report Settings.
       
  4. Select the Print Report POS Key to print the Z Report.
  5. The selected Custom Report is now included in the Z Report.
     

 

How it works

 

  • The SQL is passed back to the SwiftPOS Back Office via the Connect service (which needs to be running).
  • The data is generated in SwiftPOS Back Office and then zipped and placed in the Outbox for the SwiftPOS Touch terminal.
  • This is then picked up and sent to the SwiftPOS Touch terminal's Inbox at which point it is seen, extracted and the .dat file created in the Print Files folder.
  • From there it is extracted and sent to the Receipt Printer in the same manner as a normal sales receipt.

 

Writing custom SQL Scripts

 

Rules for Custom Report SQL Scripts

 

  1. Columns - There is a limit of up to 4 columns with set lengths:

    1. For 2 or 4 columns the column sizes (in characters) are as follows:

      1. First column = 16
      2. Last column = 12
      3. Other columns = 6


    1. For 3 columns column sizes (in characters) are as follows:

      1. First column = 20
      2. Middle column = 8
      3. Last column = 12


  1. Filters - The following are available:

    1. Customer Set filters are available in the Custom report filters screen:

      1. @FromDate
      2. @ToDate
      3. @FromPLU
      4. @ToPLU
      5. @Filter
      6. @FamilyID


    1. POS Set filters automatically set be SwiftPOS Touch:

      1. @LocationGroupID
      2. @LocationNumber
      3. @TerminalID

 

Below are examples of SQL scripts for the following reports:

 

Volume Sales By Master Groups Report

 

SELECT
[MASTERGROUP] AS [MASTER GROUP],
[QTY],
[VOL],
[SALES]
FROM
(

SELECT
0 AS ID,
LEFT(MasterProductGroupTable.MasterGroupDescription,16) AS [MASTERGROUP],
SUM(EJITEMSTABLE.QTY) AS [QTY],
SUM(EJITEMSTABLE.QTY * PRODUCTTABLE.UNITSPERCASE) AS [VOL],
SUM(EJITEMSTABLE.SALES) AS [SALES]
FROM
EJTABLE WITH(NOLOCK) INNER JOIN EJITEMSTABLE WITH(NOLOCK) ON EJTABLE.
TRANSACTION_NUMBER = EJITEMSTABLE.TRANSACTION_NUMBER
INNER JOIN PRODUCTTABLE WITH(NOLOCK) ON EJITEMSTABLE.INVENTORYCODE = PRODUCTTABLE.INVENTORY_CODE
INNER JOIN INVENTORYGRPTABLE WITH(NOLOCK) ON PRODUCTTABLE.PRODUCT_GROUP = INVENTORYGRPTABLE.GROUP_NUMBER
INNER JOIN MasterProductGroupTable WITH(NOLOCK) ON INVENTORYGRPTABLE.MasterGroup = MasterProductGroupTable.MasterGroup
WHERE
(EJTABLE.RECEIPT_DATE_TIME BETWEEN @FROMDATE AND @TODATE) AND
(EJTABLE.LOCATION_NUMBER = @LOCATIONNUMBER) AND
EJITEMSTABLE.SALES <> 0
GROUP BY
LEFT(MasterProductGroupTable.MasterGroupDescription,16)

UNION ALL

SELECT
1 AS ID,
'TOTAL' AS [MASTER GROUP],
SUM(EJITEMSTABLE.QTY) AS [QTY],
SUM(EJITEMSTABLE.QTY * PRODUCTTABLE.UNITSPERCASE) AS [VOL],
SUM(EJITEMSTABLE.SALES) AS [SALES]
FROM
EJTABLE WITH(NOLOCK) INNER JOIN EJITEMSTABLE WITH(NOLOCK) ON EJTABLE.
TRANSACTION_NUMBER = EJITEMSTABLE.TRANSACTION_NUMBER
INNER JOIN PRODUCTTABLE WITH(NOLOCK) ON EJITEMSTABLE.INVENTORYCODE = PRODUCTTABLE.INVENTORY_CODE
INNER JOIN INVENTORYGRPTABLE WITH(NOLOCK) ON PRODUCTTABLE.PRODUCT_GROUP = INVENTORYGRPTABLE.GROUP_NUMBER
INNER JOIN MasterProductGroupTable WITH(NOLOCK) ON INVENTORYGRPTABLE.MasterGroup = MasterProductGroupTable.MasterGroup
WHERE
(EJTABLE.RECEIPT_DATE_TIME BETWEEN @FROMDATE AND @TODATE) AND
(EJTABLE.LOCATION_NUMBER = @LOCATIONNUMBER) AND
EJITEMSTABLE.SALES <> 0

) AS DATA
ORDER BY
DATA.ID,
DATA.MASTERGROUP
 

Volume Sales By Price Levels Report

 

SELECT
[PRICELEVEL] AS [PRICE LEVEL],
[QTY],
[VOL],
[SALES]
FROM
(

SELECT
0 AS ID,
LEFT(PriceLevelNamesTable.PriceLevelDescription,16) AS [PRICELEVEL],
SUM(EJITEMSTABLE.QTY) AS [QTY],
SUM(EJITEMSTABLE.QTY * PRODUCTTABLE.UNITSPERCASE) AS [VOL],
SUM(EJITEMSTABLE.SALES) AS [SALES]
FROM
EJTABLE WITH(NOLOCK) INNER JOIN EJITEMSTABLE WITH(NOLOCK) ON EJTABLE.
TRANSACTION_NUMBER = EJITEMSTABLE.TRANSACTION_NUMBER
INNER JOIN PRODUCTTABLE WITH(NOLOCK) ON EJITEMSTABLE.INVENTORYCODE = PRODUCTTABLE.INVENTORY_CODE
INNER JOIN PriceLevelNamesTable WITH(NOLOCK) ON EJITEMSTABLE.Price_Level = PriceLevelNamesTable.PriceLevel
WHERE
(EJTABLE.RECEIPT_DATE_TIME BETWEEN @FROMDATE AND @TODATE) AND
(EJTABLE.LOCATION_NUMBER = @LOCATIONNUMBER) AND
EJITEMSTABLE.SALES <> 0
GROUP BY
LEFT(PriceLevelNamesTable.PriceLevelDescription,16)

UNION ALL

SELECT
1 AS ID,
'TOTAL' AS [PRICELEVEL],
SUM(EJITEMSTABLE.QTY) AS [QTY],
SUM(EJITEMSTABLE.QTY * PRODUCTTABLE.UNITSPERCASE) AS [VOL],
SUM(EJITEMSTABLE.SALES) AS [SALES]
FROM
EJTABLE WITH(NOLOCK) INNER JOIN EJITEMSTABLE WITH(NOLOCK) ON EJTABLE.
TRANSACTION_NUMBER = EJITEMSTABLE.TRANSACTION_NUMBER
INNER JOIN PRODUCTTABLE WITH(NOLOCK) ON EJITEMSTABLE.INVENTORYCODE = PRODUCTTABLE.INVENTORY_CODE
INNER JOIN PriceLevelNamesTable WITH(NOLOCK) ON EJITEMSTABLE.Price_Level = PriceLevelNamesTable.PriceLevel
WHERE
(EJTABLE.RECEIPT_DATE_TIME BETWEEN @FROMDATE AND @TODATE) AND
(EJTABLE.LOCATION_NUMBER = @LOCATIONNUMBER) AND
EJITEMSTABLE.SALES <> 0

) AS DATA
ORDER BY
DATA.ID,
DATA.PRICELEVEL
 

Volume Sales By Product Groups Report

 

SELECT
[PRODUCTGROUP] AS [PRODUCT GROUP],
[QTY],
[VOL],
[SALES]
FROM
(

SELECT
0 AS ID,
LEFT(INVENTORYGRPTABLE.GROUP_DESCRIPTION,16) AS [PRODUCTGROUP],
SUM(EJITEMSTABLE.QTY) AS [QTY],
SUM(EJITEMSTABLE.QTY * PRODUCTTABLE.UNITSPERCASE) AS [VOL],
SUM(EJITEMSTABLE.SALES) AS [SALES]
FROM
EJTABLE WITH(NOLOCK)

INNER JOIN EJITEMSTABLE WITH(NOLOCK) ON EJTABLE.TRANSACTION_NUMBER = EJITEMSTABLE.TRANSACTION_NUMBER

INNER JOIN PRODUCTTABLE WITH(NOLOCK) ON EJITEMSTABLE.INVENTORYCODE = PRODUCTTABLE.INVENTORY_CODE

INNER JOIN INVENTORYGRPTABLE WITH(NOLOCK) ON PRODUCTTABLE.PRODUCT_GROUP = INVENTORYGRPTABLE.GROUP_NUMBER
WHERE
(EJTABLE.RECEIPT_DATE_TIME BETWEEN @FROMDATE AND @TODATE) AND
(EJTABLE.LOCATION_NUMBER = @LOCATIONNUMBER) AND
EJITEMSTABLE.SALES <> 0
GROUP BY
LEFT(INVENTORYGRPTABLE.GROUP_DESCRIPTION,16)

UNION ALL

SELECT
1 AS ID,
'TOTAL' AS [PRODUCTGROUP],
SUM(EJITEMSTABLE.QTY) AS [QTY],
SUM(EJITEMSTABLE.QTY * PRODUCTTABLE.UNITSPERCASE) AS [VOL],
SUM(EJITEMSTABLE.SALES) AS [SALES]
FROM
EJTABLE WITH(NOLOCK)

INNER JOIN EJITEMSTABLE WITH(NOLOCK) ON EJTABLE.TRANSACTION_NUMBER = EJITEMSTABLE.TRANSACTION_NUMBER

INNER JOIN PRODUCTTABLE WITH(NOLOCK) ON EJITEMSTABLE.INVENTORYCODE = PRODUCTTABLE.INVENTORY_CODE

INNER JOIN INVENTORYGRPTABLE WITH(NOLOCK) ON PRODUCTTABLE.PRODUCT_GROUP = INVENTORYGRPTABLE.GROUP_NUMBER
WHERE
(EJTABLE.RECEIPT_DATE_TIME BETWEEN @FROMDATE AND @TODATE) AND
(EJTABLE.LOCATION_NUMBER = @LOCATIONNUMBER) AND
EJITEMSTABLE.SALES <> 0

) AS DATA
ORDER BY
DATA.ID,
DATA.PRODUCTGROUP

 

Account Charges by Product

 

Note : This report by default only reports on charges associated with Account/Member No 1. To report on another Account/Member, ensure the set @MEMBERNO = 1 statement below is changed. For example, set @MEMBERNO = 11

 

declare @MEMBERNO int
set @MEMBERNO = 1
SELECT
[PRODUCT],
[QTY],
[SALES]
FROM
(

SELECT
0 AS ID,
LEFT(ProductTable.DESCRIPTION,20) AS [PRODUCT],
SUM(EJITEMSTABLE.QTY) AS [QTY],
SUM(EJITEMSTABLE.SALES) AS [SALES]
FROM
EJTABLE WITH(NOLOCK) INNER JOIN
EJITEMSTABLE WITH(NOLOCK) ON EJTABLE.TRANSACTION_NUMBER = EJITEMSTABLE.TRANSACTION_NUMBER

INNER JOIN PRODUCTTABLE WITH(NOLOCK) ON EJITEMSTABLE.INVENTORYCODE = PRODUCTTABLE.INVENTORY_CODE
WHERE
(EJTABLE.RECEIPT_DATE_TIME BETWEEN @FROMDATE AND @TODATE) AND
(EJTABLE.LOCATION_NUMBER = @LOCATIONNUMBER) AND
(EJTABLE.MEMBER_NUMBER = @MEMBERNO) AND
EJITEMSTABLE.SALES <> 0
GROUP BY
LEFT(ProductTable.DESCRIPTION,20)


UNION ALL


SELECT
1 AS ID,
'TOTAL' AS [PRODUCT],
SUM(EJITEMSTABLE.QTY) AS [QTY],
SUM(EJITEMSTABLE.SALES) AS [SALES]
FROM
EJTABLE WITH(NOLOCK) INNER JOIN
EJITEMSTABLE WITH(NOLOCK) ON EJTABLE.TRANSACTION_NUMBER = EJITEMSTABLE.TRANSACTION_NUMBER

INNER JOIN PRODUCTTABLE WITH(NOLOCK) ON EJITEMSTABLE.INVENTORYCODE = PRODUCTTABLE.INVENTORY_CODE
WHERE
(EJTABLE.RECEIPT_DATE_TIME BETWEEN @FROMDATE AND @TODATE) AND
(EJTABLE.LOCATION_NUMBER = @LOCATIONNUMBER) AND
EJITEMSTABLE.SALES <> 0 AND
(EJTABLE.MEMBER_NUMBER = @MEMBERNO)

) AS DATA
ORDER
BY DATA.ID,
DATA.PRODUCT

 

Clerks Clocked On

 

Below is an example of a Report that will show all Clerks currently Clocked On.

 

SELECT
CONVERT(NCHAR(23),
LEFT(CLERK_NAME,23)) + ' - ' + CONVERT(NVARCHAR,CLOCKON,3) + ' ' + left(CONVERT(NVARCHAR,CLOCKON,108),5) As [Clerks Clocked On]
FROM
CLERKTABLE WITH(NOLOCK)

INNER JOIN CLERKTIMECLOCKTABLE WITH(NOLOCK) ON CLERKTABLE.CLERK_NUMBER = CLERKTIMECLOCKTABLE.CLERK
WHERE
ISNULL(CLERKTIMECLOCKTABLE.CLOCKOFF,'2050-1-1') = '2050-1-1' AND
CLERKTIMECLOCKTABLE.LOCATION = @LOCATIONNUMBER
ORDER BY
CLERKTABLE.CLERK_NAME

 

Related Topics

 

  1. POS Configuration Index - Reporting Configuration
  2. POS Operation
  3. POS Operation Index - Reporting Features
  4. Reports

 

Published 28 Mar 2024