Salesmatrix

 

Creating Scripts

Planning your import

Salesmatrix was originally designed to cater for the business transaction

‘Customer A buys Product B from Sales Channel C in Period D’ with values for the sale value, the sale cost and the units of product sold analysed.

This transaction while wide-ranging does not cover all aspects of the analysis requirements of all business. Hence we have designed Salesmatrix to cater for other business transactions. For example:

Client A has Staff Person B perform Work type C in Period D.

Customer A buys a component of Product-Group B from Sales Channel C in Period D.

Customer from Postcode A buys Product B etc.

Or even analysing purchases.

Supplier A provides Product B to Location C in Period D.

The import wizard and the stored scripts provide the ability to change modify the imported data to suit any of the above scenarios and of course any others where there are 4 key dimensions.

The Script files (*.SMD) files are text documents that store the rules for importing data. All dimensions of the import can be modified in these scripts and the details of what the contents are is outlined in the section Using Scripts to Automate Imports

On each of the 4 data pages in the import wizard is the option check-box ‘Use Custom Script?’ If data fields are entered into the Links screen, clicking this button will display the current SQL select statement that is to be sent to the ODBC driver and then to the accounting software database.

Once the custom scripts entry area is shown and has text in it, this text is not modified until it is cleared completely. If changes have been made to the linking fields, the query must be refreshed by completely deleting the custom query text in the entry area. Recheck the ‘Use Custom Script’ box and the modified custom script will be displayed.

The only limitations to the complexity of the selection statement are the limitations of the particular ODBC driver that is used to query the accounting data. If the driver supports multiple joins and union statements etc, then they can be employed when selecting datasets into Salesmatrix.

Default column names.

To enable Salesmatrix to discern where each column of data returned is mapped to, the default script use an ‘AS’ clause to name the column returned.

The following table lists the AS clause names used by Salesmatrix.

Transaction Selection:
  • CCUSTID Field that links to Customer ID .
  • CSMANID Field that links to Sales Channel ID.
  • DTRANDATE Transaction Date Field.
  • CPRODID Field that links to Product ID.
  • NVALUE Value of Transaction.
  • NUNITS No of Units in Transaction.
  • NCOST Cost of Transaction.
Customer Selection:
  • CID Unique ID for Customer.
  • CNAME Customer description.
  • CCAT1 First Analysis Category for Customer.
  • CCAT2, CCAT3, CCAT4, CCAT5 Second etc Analysis Categories for Customer.
Product Selection:
  • CID Unique ID for Product.
  • CNAME Product description.
  • PCAT1 First Analysis Category for Product.
  • PCAT2 Second Analysis Category for Product.
Sales Person/Channel Selection:
  • CID Unique ID for Sales Person.
  • CNAME Sales Person description.
  • SCAT1 First Analysis Category for Sales Person.
  • SCAT2 Second Analysis Category for Sales Person.

What to do if the data doesn’t import?

Once a script has been written or modified, very often there are issues with the data. During the import you may see a dialog ‘Errors occurred during Loading. Continue?’ This is a sign that one or more of the four selection scripts did not complete correctly.

Checking the data returned may give some clues as to where the issue lies.

If no value data comes into Salesmatrix there are some steps you can take to check where the issue is.

  1. Check to see if there are transactions in the accounting database for the date range entered.
  2. After the import go to the File Properties menu item. That will show various information regarding the import including details of the SQL used during the import process. If this screen is unable to be viewed, Salesmatrix writes a file to disk named ‘SQL.log’. This is a list of the selection statements and the errors that are returned. This may give you an idea of where the errors are originating.
  3. You can test the SQL in a database query tool like MS-Query or MS Access to determine whether the query is correctly returning data.
Copyright Infyra Pty Ltd