Connecting your on-premise ERP to Salesmatrix

In this guide, the dashboard.salesmatrix.com system is called Salesmatrix. If your accounting or ERP system runs on your own server rather than in the cloud, Salesmatrix collects its data through a small Windows service called the Salesmatrix Data Transfer Service. This guide takes you from registering your company, through installing the service, to setting up the script that tells it what to send.

Overview: The Connector explains how an ERP on your own server connects. Using Jiwa or ABM? Salesmatrix for Jiwa and Salesmatrix for ABM have ready-made queries for them.

1. Before you start
2. Register your company
3. Install the service
4. Set up your script
5. Check it is working
6. Troubleshooting

Before you start

Have these ready. The service cannot send anything until all of them are in place.

• A Windows PC or server inside your network that can reach your accounting database. It must be 64-bit and must stay switched on overnight, because that is when Salesmatrix collects your data.
• Local administrator rights on that machine, to install the service.
• .NET Framework 4.7.2 or later. Windows 10 (version 1803 onwards), Windows 11 and Windows Server 2019 onwards already include it.
• A way into your accounting database. For most systems this is an ODBC data source (DSN), created with the ODBC driver your accounting system supplies (see Create a 32-bit System DSN in Install the service). Systems that keep their data in Microsoft SQL Server, such as Jiwa, ABM and MYOB Exo, need no DSN, just a SQL Server login. Either way, a login that can only read is enough, and safer.
• Your queries for customers, products, sales transactions and salesmen, and optionally contacts. For most systems Salesmatrix has ready-made queries, including Attaché, Jiwa, ABM, MYOB Exo, Sage, SAP Business One, Sybiz, Accredo and Microsoft Dynamics. They are installed for you the first time you open the Connection page, so usually all you enter is your DSN or connection string.

Register your company

1. Open the registration page

   Go to dashboard.salesmatrix.com/Register/SWOTRegistration.

2. Enter your company details

   Fill in Company Name, Administration Email Address, Contact person name and, optionally, Contact No. Country is filled in from your location; change it if it is wrong.

   Use an email address you can check now. The administration address receives your registration token, and becomes the first administrator login for your dashboard.

3. Choose your accounting system

   Pick your system from Accounting Systems. For a system that runs on your own server, the form does not ask for any keys and does not show Test Connection. That is expected, because the connection is made by the service you install next.

4. Register and confirm your email

   Click Register. You are taken to the Confirm Registration page. Copy the token from the email we send you into Enter token received on email and click Confirm Token. You are then taken to the sign-in page.

Install the Salesmatrix Data Transfer Service

Do this on the machine that can reach your accounting database.

1. Download the installer

   Download SWOTServiceSetup 2.2.1.0 (Windows installer, .msi).

   You can also download it from the Salesmatrix downloads page, under Salesmatrix Dashboard Data Upload Service.

2. Run it as an administrator

   Double-click the file, or right-click it and choose Install. Accept the default installation folder.

3. Enter your company token

   On the SWOT Agent Configuration screen, replace Enter Token here with the company token Salesmatrix sent you. Every other setting is configured for you. Finish the installer.

4. Start the service

   The installer does not start the service the first time. Press Windows+R, type services.msc and press Enter. Find SWOTDataTransferService, right-click it and choose Start. From now on it starts by itself whenever the machine starts.

Set up your script on the Connection page

The script tells the service how to reach your database and which queries to run. It is stored in your dashboard, not on your server, so you can change it at any time without touching the machine. The service picks up the new version on its next run.

1. Open Connection

   Sign in at dashboard.salesmatrix.com with your administration email and open Connection from the menu. Only administrators can see this page.

2. Check the ready-made queries

   The first time you open the page, it installs the queries Salesmatrix has written for the accounting system you chose when you registered, and opens on the Standard Options tab. A note at the top confirms The ready-made … queries have been installed and tells you what is left to do.

   If the note says There are no ready-made … queries yet, or names a query that has not been written yet, Salesmatrix has no query for that part of your system. You can still enter your connection and save: the other queries import as normal, and nothing is imported for the missing ones until they are written. Talk to Salesmatrix and we will write them with you.

   To go back to the ready-made queries later, click Restore Default, then Yes. Your connection details are kept: only the queries are reset.

3. Enter your connection

   At the top of the tab, choose the Connection type, then click the edit icon next to DSN or connection string, type it in, and click outside the box. Every box on this tab saves itself when you click away from it, and a Successfully saved scripts message confirms it.

   Connection type	Use for	DSN or connection string
   ODBC	Any system with an ODBC driver, e.g. Attaché	DSN=YourDSNName, or DSN=YourDSNName;UID=username;PWD=password if the DSN does not store a login
   SQL Server	Systems on Microsoft SQL Server, e.g. Jiwa, ABM, MYOB Exo. No DSN needed.	Server=SQLSERVER01\INSTANCE;Database=YourDatabase;Uid=salesmatrix_reader;Pwd=YourPassword
   OLE DB (ADO)	Sources with an OLE DB provider, e.g. Microsoft Access	Provider=…;Data Source=…

   A ready-made template fills this box with placeholders such as ENTER_SERVER and ENTER_PASSWORD. Replace every one of them: nothing is imported until you do, and the note at the top of the page stays until the last ENTER_… is gone. Once saved, the password is shown as ********. Leave the asterisks as they are to keep the saved password, or type over them to change it.

4. Check the queries

   Each box holds one SELECT statement. To change one, click its edit icon, make the change and click outside the box.

   Box	Sends	Notes
   Product Script	Products	nothing else
   Customer Script	Customers	nothing else
   Transaction Script	Sales transactions	Also set Date Field and Date Format, see the next step
   Salesman Script *	Your sales reps	Required. The dashboard groups sales by rep, so its CID must match the CSMANID column of the Transaction Script. Without it, reps show as bare codes.
   Contact Script optional	Contacts	Leave it empty if you do not need contacts on the dashboard. Nothing is imported for it and no error is logged.

5. Set the transaction date

   The transaction query sends only what is new since the last import. Put the name of your transaction date column in Date Field, and choose how your database stores dates in Date Format. Do not add a date filter to the query yourself, because the service adds it.

   Date Format	Use when your dates look like
   DateTime	Microsoft SQL Server date columns
   TimeStamp	Date and time together, for drivers that need a full timestamp
   SQL Date	ISO dates, e.g. 2026-10-01
   SQL Text Date	Dates stored as text, e.g. '2026-10-01'
   Number	Plain 8-digit numbers, e.g. 20261001
   Default	Anything else. Uses the standard ODBC date format

   Below Date Format, tick Filter dates with >= and <= instead of BETWEEN if your database is slow with BETWEEN. Attaché is much faster with it ticked, and its ready-made queries come with it ticked.

Connection types

The connection type tells the service how to reach your data, and the connection string says where. On the Standard tab these are Connection type and DSN or connection string; on the Advanced tab they are Parameter2 and Parameter3 of Stage 1. CSV is only available on the Advanced tab.

Example connection strings

Replace the example names, paths and passwords with your own. The service only reads your data, so a database user with read-only access is enough, and safer.

ODBC with a DSN. Use the DSN's exact name from ODBC Data Sources (32-bit).

Parameter2  ODBC
Parameter3  DSN=AttacheData
            or, if the DSN does not store a login:
            DSN=AttacheData;UID=salesmatrix_reader;PWD=YourPassword

ODBC without a DSN. Name the driver directly. For example, Visual FoxPro tables in a folder:

Parameter2  ODBC
Parameter3  Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=C:\Data\;Exclusive=No;Collate=Machine;NULL=No;DELETED=Yes;BACKGROUNDFETCH=No

Or a MySQL database (e.g. Fishbowl), using the exact name of the MySQL driver you installed:

Parameter2  ODBC
Parameter3  Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;Port=3306;Database=fishbowl;Uid=salesmatrix_reader;Pwd=YourPassword

SQL Server. For a named instance, add it to the server name, e.g. SQLSERVER01\SQLEXPRESS.

Parameter2  SQL
Parameter3  Server=SQLSERVER01;Database=JiwaData;User Id=salesmatrix_reader;Password=YourPassword

ADO (OLE DB). A Microsoft Access database, using the Access Database Engine (32-bit):

Parameter2  ADO
Parameter3  Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Sales.accdb
            or, for an older .mdb file, with the provider built into Windows:
            Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Sales.mdb

CSV files. Point Parameter3 at one file in the export folder:

Parameter2  CSV
Parameter3  C:\Exports\Salesmatrix\customers.csv
Parameter4  YES

In each data stage, either leave Command empty to read the file named in Stage 1, or select from any other file in the same folder by its file name, e.g. SELECT * FROM [products.csv].

Your finished script looks like this:

Stage 1   rest   Connection    Parameter2 = ODBC   Parameter3 = DSN=YourDSNName
Stage 5   cust   Customers     Command = SELECT ... FROM customers
Stage 10  prod   Products      Command = SELECT ... FROM products
Stage 20  tran   Transactions  Command = SELECT ... FROM invoice_lines
                                Parameter2 = InvoiceDate   Parameter3 = SQLDATE
Stage 25  sman   Salesmen      Command = SELECT ... FROM sales_reps
Stage 30  cont   Contacts      Command = SELECT ... FROM contacts

Check it is working

Salesmatrix collects data once a night, in a time slot between 12:00 and 17:00 UTC (late evening to early morning in Australia and New Zealand). Your first data appears the morning after you finish setting up, as long as the machine is switched on overnight.

• Open Logs in the dashboard. Each stage records how many records it sent, and each night's run ends with an Update Completed entry.
• Your customers, products and sales then appear in the dashboard and reports.

Troubleshooting

Nothing appears in Logs the next morning

Check that the machine was on overnight and that SWOTDataTransferService shows Running in services.msc. If it is running, the company token entered during installation may be wrong. Contact Salesmatrix and we will check it with you.

"Data source name not found"

The service cannot see your DSN. Create it as a System DSN in ODBC Data Sources (32-bit), and check that the name in Parameter3 of Stage 1 matches it exactly.

A stage shows an SQL error

The log entry for that stage includes the query and your database's error message. Fix the query in that stage's Command. The other stages keep running in the meantime.

A stage reports 0 records

The query ran successfully but returned nothing. Run the same SELECT in your database's own query tool to check what it returns. A connection problem shows up as an error, never as 0 records.

Every stage fails with a connection error

Check the DSN or connection string on the Standard tab: every ENTER_… placeholder must be replaced, and the login must be able to read the database. On the Advanced tab, the script may have no connection stage, or the connection stage may have a higher stage number than the data stages, so they run before the connection is open. Make sure Stage 1 is the rest stage.

"Please enter the salesman script"

The Standard tab will not save without a Salesman Script, because the dashboard groups your sales by rep. Enter a query that returns your reps' code as CID and name as CNAME, using the same code your Transaction Script returns as CSMANID. Ask Salesmatrix if you are not sure which table holds your reps.

"… still placeholder text, so nothing will be imported for it"

The script saved, but one or more boxes still hold placeholder text such as Select Salesmen instead of a real query, because there is no ready-made query for that part of your system yet. The other queries import as normal. Talk to Salesmatrix and we will write the missing ones with you.

"This connection uses an advanced script"

Your script has stages the Standard tab cannot show, so it can only be changed on the Advanced Options tab. This protects those stages from being dropped.

"Unknown query type"

Parameter2 of Stage 1 is not a value the service recognises. It must be exactly ODBC, SQL, ADO or CSV. See Connection types.

Salesmatrix Data Transfer Service 2.2.1.0 · Windows service SWOTDataTransferService