Custom

Back Office - Reports

Provides a list of Custom reports available in Back Office. To access, select the Reports option in any one of the menus in Back Office, to open the Reports screen. Then expand any one of the Custom Batch Report Scripts or Custom Reports options listed on the left-hand side of the screen, to view the list of Custom reports.

Custom Reports ▲ ▼

Account Charges Listing	CTM046	Reports on charges made to Account/s within a selected period. Lists the Account, Date/Time, Receipt #, Clerk, Description, Qty and Amount. This report can be sorted Date/Time or Receipt #.
Average Table Turnover by Time Period	CTM040	Reports on the Average Sales per Hour per Table grouped by Location and Time Periods. Lists the Location, Time Period, Table, Time Seated/Finished, Total Time, No. of Customers, Average Spend / Hour, Sales value (either incl./excl. of GST) and the Sales value as a percentage of the Total Sales.
Average Time Period Sales by Location and Terminal	CTM001	Reports on sales summarised by Master Group and grouped by Trading Date. Lists the Trading Date, Master Group, Tax, Amounts per Media Total.
Bank Reconciliation and GL Posting	CTM049	Reports on Sales and Tips data grouped by Location and then Clerk. Lists the Location, Clerk (and the first and last Sale date/times), Sales Inc, Sales Inc (Without Tip), Sales Inc (With Tip), Covers, Transactions, Avg per Cover, Avg per Transaction, Media Total, Surcharges, EFT Tips, Other Media Tips, Room Charge Tips, Tip %, Total Tips, Tips Paid and Tips Held.
Benchmarking PLU Sales	CTM002	Reports on PLU Sales for a single Location and compares it with the sales for all Locations by Product Group. It includes comparisons for Total Qty Sold, Total Cost Price, Total Sales and Averages, for the selected Location, all Locations and the Difference as a % of the Total. Note: It is recommended that the Location filter be used in order for this report to show useful information. If the filter is not used then the report will be comparing the sales for all Locations against no other Location. The Detailed version reports by Product. It lists the Product Code and Product Description.
Clerk Sales and Tips by Location	CTM048	Reports on Sales and Tips data grouped by Location and then Clerk. Lists the Location, Clerk (and the first and last Sale date/times), Sales Inc, Sales Inc (Without Tip), Sales Inc (With Tip), Covers, Transactions, Avg per Cover, Avg per Transaction, Media Total, Surcharges, EFT Tips, Other Media Tips, Room Charge Tips, Tip %, Total Tips, Tips Paid and Tips Held.
Consignment Sales by Member	CTM003	The Consignment Sales by Member report shows consignment sales by member in NetPOS.
Custom String-Points Audit	CTM004	Reports on Points Purchases/Redemptions. This report can be filtered by Customer/Member, Clerk, Location, Product and Product Group. In addition, this report also uses the string PLU filter field which is a requirement for this report to produce any data. Note: This report will only work when the selected Customer/Member ID is contained within a string field of a purchasing transaction.
Daily Sales Sheet	CTM009	Reports on each day's trade for the selected period and provides a summary of transactions. Lists the Date, GST (Tax) Sales, GST Free (Tax Free) Sales, Total Sales (Gross Sales on Till), GST (Tax on Till), Net Sales and multiple Media Type Totals. This report has been specifically designed for use in franchise environments.
Electronic Journal Summaries	CTM005	Reports on the number of transactions per calendar Month per year for all Sales Transactions. Lists the Year, No of Transactions by calendar Month and the Total number of Transactions. This report is particularly useful for checking the Sales Transactions after importing a Task Manager database into Swiftpos. It is also useful if it is required to know how many Sales Transactions exist in the database over the entire sales history. Note: This report has no filters and will search the entire Sales Transactions, so please be aware that this report may take some time to load.
Expired Vouchers	CTM043	Reports on Vouchers that have expired grouped by Location and Voucher. Lists Location, Voucher, Barcode, Mapping String, Issued Date, Date Expired, Stored Value, Max Uses and Actual Uses.
Expiry Date Manual Stock Check		This report has been renamed to Stock Expiry Checklist
Food Beverage Other Media	CTM007	Reports on Sales by Master Groups (ie. Food, Beverage and Other) by Media across all Locations.
Food Beverage Other Media By Location	CTM008	Reports on Sales by Master Groups (ie. Food, Beverage and Other) by Media for the selected Locations.
Key Performance Indicators	CTM017	Reports on Key Performance Indicators in the areas of Sales, Transactions Hourly per Day, Royalty and Advertising Payments Calculation and Audit Trail. This report has been specifically designed for use in franchise environments.
Location Coupon Sales Detail	CTM044	This report displays a list of Products that have comments attached to them that a clerk has entered against the Product. Note: A Coupon PLU must be selected in the Report Filters.
Location Coupon Sales Summary	CTM010	This report is much the same as the Location Coupon Sales Detail report but it only has a summary of the transactions not a detailed listing of them. Note: A Coupon PLU must be selected in the Report Filters.
Marketing Sales Summary	CTM011	Reports on Sales transactions by Marketing regions. List the Marketing Description, Period (in calendar month), Transaction Count, Sales Inc GST and the Sales value as a percentage of the Totals Sales for the selected region. Marketing Regions/Post/Zip Codes can be setup here.
Order Form	CTM012	Provides a form listing Products required to be ordered, with a Order Qty entry box for manual entry. Lists the Master Group, Product Description, PLU # and Price (Incl. GST). This report can be sorted by Product Description or PLU.
PLU Sales by Category	CTM013	Reports on PLU Sales by Location, by Price Level and by Category. Lists Location # and Description, Price Level # and Description, Category # and Description, Product Code, PLU and Description, Qty, Cost, Sales (Incl. GST), Gross Profit as a dollar and percentage amount. The report is specifically desicgend for reporting on tax in the UK. .This report can be sorted by Price Level or Category.
Price Difference Report	CTM032	Reports on the difference in Price of Sales between the Price Levels selected. Lists Location, Total Sales at the 1st selected Price Level, Total Sales at the 2nd selected Price Level, Total Difference and the Difference as a percentage. value at the sales price, total sales value at the Default Price, the Difference between the two sales values as a dollar and percentage amount. The Detailed version reports by Product. It lists the Terminal, Receipt #, Date/Time of the sale, Clerk, Sale Total, PLU/Product Code and Description.
Production Report (30min Intervals)	CTM014	Exports a file that summarises item sales in 30 minute intervals. Note: that this report can be exported to CSV or TSV only.
Region Sales Detail	CTM015	Reports on Sales data summarised by Period and Region and then by Post/Zip Code. Lists the Period, Region, Post/Zip Code and Description, Clerk # and Name, Item Count, Transaction Count, Sales Incl GST and the Sales value as a percentage of the Totals Sales. Marketing Regions/Post/Zip Codes can be setup here. Note: The Item Count column displays the number of Postcodes captured in a sale.
Region Sales Summary	CTM016	Reports on Sales data summarised by Period and Region and then by Post/Zip Code. Lists the Period, Region, Transaction Count, Sales Incl GST and the Sales value as a percentage of the Totals Sales. Marketing Regions/Post/Zip Codes can be setup here.
Retail Sales with SOH	CTM018	Reports on Product/PLU Sales by Master Group/Product Group. Lists Master Group/Product Group, Product, Qty, Cost, Sales Ex/Inc, Gross Profit $ and %, and the Stock On Hand (SOH). The report is designed to report all SOH regardless of the sales. This report can be sorted by Product Description or Product Code/PLU.
Retail Sales with SOH by Location	CTM019	Reports on the same information as the Retail Sales with SOH report above. Only this report, reports by Location and provides the ability to generate a Graph.
Retailer Points Contribution	CTM045	Reports on Sales and Points awarded for Locations that are contributing points towards the Loyalty system. Lists Location # and Description, Receipt Date/Time, Customer/Member # and Name, Sales Value, Points Awarded. and Invoice Amount.
Scan Data	CTM020	The Scan Data report is a file export that is used with the OnTap Data / Liquor File interface. This report can be exported to CSV or TSV only.
Stock Expiry Checklist	CTM029	Reports on Stock that have expired or are due to expire (see Days to Expire filter) grouped by Location and then either Master Group/Product Group. Lists the Location, Master Group/Product Group, Product, SOH, last date Purchased, Shelf Life (Days), SOH Expired/Expiring and the date Expired/Expiring.
Supplier Detailed Transaction Listing with GP	CTM047	Reports on Supplier Invoices in detail depending on the Status selected. Lists the Supplier, Posting Clerk, Reverence (Invoice #), Transaction Date, Posted Date, Posted Location, Total Received value, Discount value, Freight value, Discount Apportioned (True/False), Transaction Total, Rounding value, Freight Dispersal Method. Product, Supplier Code, # Case (Case Qty), Cases, New Cost, Old Cost, Variance $, Tax, Total (Ex), Total (Inc) Freight (Ex), C/M (Costing Method), Price and GP%. This report can be sorted by As Ordered, Product Description or Code. Note: A Price Level must be selected for the report to be generated.
Table Performance Report	CTM021	Reports on all Tables by Location and data related to their performance. Lists Location # and Description, , Terminal ID and Description (if selected in the sort options), Table #, No. of Customers, Average Sales (either incl./excl. of GST), total Sales (either incl./excl. of GST) and the sales value as a % of the total sales. This report can be sorted by Location of Terminal. Product Description or PLU.
Volume Sales	CTM022	Reports on the Volume of Sales by Product Group. Originally designed to report on the volume of sales for cartons of cigarettes. Lists Product Group # and Description, Product Code, PLU and Description, Size, Qty, Total Volume, Cost and Sales (either incl./excl. of GST). value All of the volume sales reports can be filtered by member/account, clerk, Location, terminal, PLU, Product Group, Category and master group. This report can be sorted by Description (Product), Inventory Code (Product Code), Size and Description or Size and Inventory Code.
Volume Sales by Category	CTM023	Reports on the same information as the Volume Sales report above, only this report, reports by Category grouped by Location. This report can also be grouped by Terminals. This report can be sorted by Description (Product), Inventory Code (Product Code), Size and Description or Size and Inventory Code.
Volume Sales by Category by Period	CTM024	Reports on the same information as the Volume Sales report above, only this report, reports by Time Periods grouped by Category and then Location. This report can also be grouped by Terminals. This report can be sorted by Description (Product), Inventory Code (Product Code), Size and Description or Size and Inventory Code.
Volume Sales by Location	CTM041	Reports on the same information as the Volume Sales report above, only this report, reports by Product Group and then Location. This report can also be grouped by Terminals. This report can be sorted by Description (Product), Inventory Code (Product Code), Size and Description or Size and Inventory Code.
Volume Sales by Location by Time Period	CTM042	Reports on the same information as the Volume Sales report above, only this report, reports by Product Group, Price Level and then by Location. This report can also be grouped by Terminals. This report can be sorted by Description (Product), Inventory Code (Product Code), Size and Description or Size and Inventory Code.
Volume Sales by Period	CTM025	Reports on the same information as the Volume Sales report above, only this report, reports by Time Periods and then by Category. This report can be sorted by Description (Product), Inventory Code (Product Code), Size and Description or Size and Inventory Code.
Volume Sales by Price Level	CTM026	Reports on the same information as the Volume Sales report above, only this report, reports by Time Periods, by Product Group and then Location. This report can also be grouped by Terminals. This report can be sorted by Description (Product), Inventory Code (Product Code), Size and Description or Size and Inventory Code.
Volume Sales by Price Level by Period	CTM027	Reports on the same information as the Volume Sales report above, only this report, reports by Time Periods, by Product Group, by Price Level and then Location. This report can also be grouped by Terminals. This report can be sorted by Description (Product), Inventory Code (Product Code), Size and Description or Size and Inventory Code.
Volume Sales by Product Filter	CTM028	Reports on the same information as the Volume Sales report above, only this report, reports by Product Range and then by Location. This report can also be grouped by Terminals. This report can be sorted by Description (Product), Inventory Code (Product Code), Size and Description or Size and Inventory Code.
Volume Sales by Product Filter by Period	CTM030	Reports on the same information as the Volume Sales report above, only this report, reports by Time Periods, then Product Range and then by Location. This report can also be grouped by Terminals. This report can be sorted by Description (Product), Inventory Code (Product Code), Size and Description or Size and Inventory Code.
Voucher Status	CTM031	Reports on all Vouchers issued (Voucher Ledger) grouped by either Location and/or Terminal or no grouping. Lists the Voucher, Qty Issued and Stored Value. The Detailed version reports by Voucher. It lists the Barcode, Issue Date, Clerk, Max Uses, Actual Uses and Last Used.

Custom Batch Report ▲ ▼

Provide Venues with the opportunity to create Custom Batch Reports in the form of SQL Stored Procedures (.SQL). Once created, or provided by Swiftpos, these can then be executed using Microsoft SQL Server Management Studio. Once executed they will appear as one of the Custom Batch Report Scripts listed in the Reports menu in Swiftpos Back Office.

Commonly Used Parameters
Custom Batch Report Scripts
How To?
What To Consider?

Commonly Used Parameters ▲ ▼

@FromDate DATETIME
@ToDate DATETIME
@FromMember INT
@ToMember INT
@FromType SMALLINT
@ToType SMALLINT
@FromClerk INT
@ToClerk INT
@FromClerkGroup INT
@ToClerkGroup INT
@FromTerminal INT
@ToTerminal INT
@FromReceipt INT
@ToReceipt INT
@FromTable INT
@ToTable INT
@FromMedia INT
@ToMedia INT
@FromLocation INT
@ToLocation INT
@LocationGroup NVARCHAR(100)
@LocFilter1 NVARCHAR(1)
@LocFilter2 NVARCHAR(1)
@LocFilter3 NVARCHAR(1)
@LocFilter4 NVARCHAR(1)
@ProductFamily INT
@ProductFilter NVARCHAR(16)
@MasterGroups INT
@FromMasterGroup SMALLINT
@ToMasterGroup SMALLINT
@FromGroup REAL
@ToGroup REAL
@FromCategory SMALLINT
@ToCategory SMALLINT
@FromPLU INT
@ToPLU INT
@FromProduct NVARCHAR(13)
@ToProduct NVARCHAR(13)
@FromSupplier NVARCHAR(10)
@ToSupplier NVARCHAR(10)
@Discontinued INT
@NilInventory INT
@Inactive INT
@ShowSalesInc INT
@ShowStockAsCasesUnits INT
@ShowDetail INT

Custom Batch Report Scripts ▲ ▼

Bottle Mart	SQL001	Refer HERE for a sample report.
Cash Drawer Variance	SQL002	Exports Cash Draw Declarations. Exports Location, Terminal, Reconcile From/To Dates, Media , Expected Value, Counted Value ,Variance, Drawer, Clerk, Clerk Name , Clerk Media Expected.
Cooking The Books	SQL003	Exports sales Data (excluding Rounding).
Event Invoice Export	SQL004	Exports Event Session sales related data.
Export Scales PLU List CL5000	SQL005	To be continued ...
Flow	SQL006	Exports Data for Flow Control liquor reporting.
Fuel Data Export	SQL007	Exports sales Data specific for Fuel sites.
Impact Data	SQL008	Exports sales Data. Exports Transaction #, Terminal, Location, Receipt Date/Time, Product, Product Group, Default Price, Cost, Price Level, Qty sold, Tax, Sales Value and Member.
Location Discount Summary	SQL009	Exports Discount Summary information per Location.
Media Analysis Export	SQL010	To be continued ...
Member Activity	SQL011	Refer HERE for a sample report.
Member Fees By Sub Classifications	SQL012	Refer HERE for a sample report.
MLF Export	SQL013	Data export for My Local Foodie. Refer HERE for a sample report.
Multi Screen	SQL014	Exports Member data for those Members whose Membership has not expired. Lists the Member Join date, Surname, First Name and Member Number.
My Food Link	SQL015	Exports Product data for MyFoodLink by Location.
Online Group Sales By Location	SQL016	Refer HERE for a sample report.
Online Sales By Product Group And Location	SQL017	Export sales related data grouped by Location and then Product Group. Lists the Location, Product Group, Qty sold, Sales amount, Sales Exc amount, Cost, Tax, GP Value and GP Percent.
PLU Sales By Location	SQL018	Refer HERE for a sample report.
PLU Sales Export	SQL019	Refer HERE for a sample report.
Product Export	SQL020	To be continued ...
Product List	SQL021	Exports Product data. Exports PLU as PLU_Number, Product Code as Inventory Code, Product as Description, Case Qty as StockSize, Category ID as Dept#, Category as Department, Product Group ID as Group #, Product Group as Group, Master Group ID as Master Group #, Master Group, Supplier ID as Supplier #, Supplier Name, Manufacturer Name.
Retailer Contributon	SQL022	Refer HERE for a sample report.
Riteq Export	SQL023	Refer HERE for a sample report.
Sales Data Export	SQL024	Exports sales data. Exports Transaction Date, Location, Department, Shift, Forecast Sales, Actual Sales, Actual Sales Ex.
Sales Single Media	SQL025	Exports sales data.
Single_Media_w_Discount_Analysis	SQL026	Refer HERE for a sample report.
Single_Media_w_Discount_Analysis_Export	SQL027	Refer HERE for a sample report.
SOH by Bin Number	SQL028	Exports Stock on Hand (SOH) information by Bin number. Exports Product Code, PLU, Bin #, SOH, Qty, Order and Picked. Note: The Location(s) selected must be a Warehouse type Location.
Summit Products	SQL029	Exports Product data. Note: The export file generated will need to be named Products.csv in order for it to be processed by Summit. This also, applies to when it is included as a Batch Report. Be sure to give it the Products.csv name here as well.
Summit Stores	SQL030	Exports Location data. Note: The export file generated will need to be named Stores.csv in order for it to be processed by Summit. This also, applies to when it is included as a Batch Report. Be sure to give it the Stores.csv name here as well.
Summit Transactions	SQL031	Exports Transaction data. Note: The export file generated will need to be named Transactions.csv in order for it to be processed by Summit. This also, applies to when it is included as a Batch Report. Be sure to give it the Transactions.csv name here as well.
Supplier Ordering	SQL032	Refer HERE for a sample report.
Supplier Rebate Export	SQL033	Exports Supplier Rebate related data. Note: The DISC column will be populated with the Rebate Description.
Transaction_Single_Media_w_Discount_Analysis_Export	SQL034	Refer HERE for a sample report.

How To Install A Custom Report Batch Script? ▲ ▼

For a Custom Batch Report Script provided by Swiftpos in the form of a Stored Procedure (.SQL) file, do as follows:

Unzip the Stored Procedure (.SQL) provided, to a local folder on the PC running Swiftpos.
Select the Stored Procedure (.SQL). This should open Microsoft SQL Server Management Studio.
In Microsoft SQL Server Management Studio select the correct database to establish a connection. This should be the Swiftpos database against which the Stored Procedure (.SQL) is to be executed.
Select the Execute option to execute the Stored Procedure (.SQL). This will add it to the selected database.
Ensure that the new custom batch report permission is set in Back Office>Clerks>Clerk Security Groups>Clerk Group Reports> Custom Batch Report Scripts.
In Swiftpos Back Office > Administration > Batch Reports - Select the New option to open the Batch Report Full Edit screen.
Select the Select option to the right of the Report Name field to open the Reports screen and expand the Custom Batch Report Scripts option. This should list all BR_ prefixed Custom Batch Report Scripts created as Stored Procedures (scripts).
Select the Custom Batch Report Script unzipped above and then select the Select option in the bottom left-hand corner of the Reports screen.
Back in the Batch Report Full Edit screen specify the Report Frequency, Destination and File Properties.

Notes ...

Custom Batch Report Scripts can only be exported and NOT previewed.

Select Save to save the Batch Report Settings.
Select Run Now to test. If File was selected as the Destination, then the file created can be found in the path specified in the Batch Report Full Edit screen.

What To Consider? ▲ ▼

Notes ...

FREE support or assistance is NOT available for those customers that choose to develop their own Custom Batch Report Scripts. Any reports developed would need to be tested against a 10GB database to make sure they do not effect the performance of Swiftpos. Any support work carried out as a result of a 3rd party developed report will incur charges.

A maximum of 50 characters is allowed for the Report name.
All Custom Batch Report Scripts created must be named with the prefix "BR_".
All Custom Batch Report Scripts must NOT include a dot (.) in the name of the Stored Procedure.
The Custom Batch Report Scripts can take full advantage of the Report Filter screen as long as the user names the Parameters in the stored procedure correctly. For example, @FROMDATE. Refer HERE for more information.
When creating Stored Procedures (scripts) to generate Custom Batch Report Scripts, it is important to use the WITH(NOLOCK) argument on each Table in the FROM section of the Stored Procedure (.SQL). This is recommended to ensure that Tables used are not locked while the Stored Procedure (.SQL) is executing. This will avoid a deadlock situation from occurring and thus have no adverse impacts on the day to day operations of the site while the Stored Procedure (.SQL) is executing.
If required. feel free to contact Swiftpos for a quote to cover the consulting and development of Custom Batch Report Scripts

End of article ▲