Back Office >Reports > Custom Batch Report Scripts

Description

This feature provides the opportunity to create Custom Batch Report Scripts 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 in the Reports menu in SwiftPOS Back Office.

To Be Considered

1. Note : There is no FREE support or assistance available for those customers that choose to develop their own Custom Batch Report Scripts.
   Custom Development of Gadgets. Any reports developed would need to be tested against a 10G database to make sure they do not effect performance of SwiftPOS. Any support work created are a result of third party development would also be chargeable.
2. All Custom Batch Report Scripts created must be named with the prefix "BR_".
3. All Custom Batch Report Scripts must NOT include a . in the name of the Stored Procedure.
4. 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.
5. 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.
6. If required. feel free to contact SwiftPOS for a quote to cover the consulting and development of Custom Batch Report Scripts.

Setup

1. For Stored Procedure (.SQL) files provided by SwiftPOS to generate a Custom Batch Report Script, follow the steps below:
   
   
1. Unzip the Stored Procedure (.SQL) to a directory on your PC.
2. Double click the Stored Procedure (.SQL). This should open Microsoft SQL Server Management Studio.
3. In Microsoft SQL Server Management Studio ensure the correct database is selected and a connection established. This should be the database (eg. SwiftPOS) against which the Stored Procedure (.SQL) is to be executed.
4. Click the ???! Execute??? button to execute the Stored Procedure (.SQL). This will add it to the selected database.
5. Ensure that the new custom batch report permission is set in Back Office>Clerks>Clerk Security Groups>Clerk Group Reports> Custom Batch Report Scripts.
6. In SwiftPOS Back Office > Administration > Batch Reports - Select the New button to open the Batch Report Full Edit screen.
7. Select the Select Report button to open the Reports screen below and expand the Custom Batch Report Scripts menu item. This should list all BR_ prefixed Custom Batch Report Scripts created as Stored Procedures (scripts).
8. Select the Custom Batch Report Script created above and then select the Select button in the bottom left hand corner of the Reports screen.
9. Back in the Batch Report Full Edit screen specify the Report Frequency, Destination and File Properties.
10. Select Save to save the Batch Report Settings.
11. 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.

Reports

Custom Batch Report Scripts

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

Related Topics

1. Import/Export Overview
2. Reports