Using Query Editor

The NextTrack Sales Analysis Tool requires a direct database connection to read the sales data. When opening the program or refreshing the data in the grid, a Query is run against the database. Default Queries for Baan IV, Baan V and ERP LN are shown in Appendix I. The Query Editor allows you to customize these queries. Editing Queries requires knowledge of the ERP Data Base table and field names, as found in the database and required by native SQL Queries. Follow examples of the existing queries and use the same syntax.

To access the Query Editor, choose the Data Connection pullout menu under Data Setup and click on Query Editor:

On startup this will contain default queries for Baan IV, V and ERP LN. The top line displays which query is currently being used by the application.

It is recommended that you choose the Copy Query Button before editing any Default information. Only limited editing is allowed of the Default Query.

A new query will be made with a copy of the Default for you to Edit.

Change the Name and Click the Green Check Box to Save. Click the Red X at any time to cancel edits.

Click the Row + Button to Expand a Query to all the Query Parts. Further Zooming by clicking on the Magnifying Glass improves navigation of the Query Parts.

Table Mapping

If the fields you need are on a table that does not exist in the list of Tables click the Green + Button. When done make sure to click the Green Checkbox to accept changes. Cancel edits by clicking the Red X button at any time.

Table Name: Must be unique. Not used, plain text version just to identify.

Table Clause: This field is substituted directly into the query and must be formatted exactly. Use

t in front to indicate Table

pp = Package in Baan/ERP LN

mmm = Module in Baan/ERP LN

999 = Table Number in Baan/ERP LN

Mandatory: This checkbox is not editable. Tables marked mandatory cannot be deleted.

Each new table you add must also have a Join Clause to include it.

Field Mapping

If the fields you need are not on the list click the Green + Button. When done make sure to click the Green Checkbox to accept changes. Cancel edits by clicking the Red X button at any time.

Field Name: Must be unique. Not used, plain text version just to identify.

Field Clause: This field is substituted directly into the query and must be formatted exactly. Use

t in front to indicate Table

pp = Package in Baan/ERP LN

mmm = Module in Baan/ERP LN

999 = Table Number in Baan/ERP LN

XXX or YYY = Use XXX for you base company. Use YYY for your shared data company. If not sharing data they are interchangeable.

.t_fnam = field name, preceded by ‘.t_’.

Data Type: Allowed Data Types are String, Double, Integer and Date. This definition can match the ERP System, or can be used here to ‘CAST’ the result as a different data type. This is especially useful when combining ERP Systems, such as Baan IV and ERP LN. When combining system queries, for a Column to match between both systems they must have the same Data Type and Length. So, for example, the Order Number in Baan IV is stored in the ERP System as Numeric. In ERP LN this is a string. For the 2 columns to merge into one datasheet, they must be defined as String with the same length. The Default ERP Queries provided by CrossroadsRMC are already compatible across versions.

Label: This is the label of the field as it appears in the Pivot Grid. This should be unique.

Length: For string and date fields it is necessary to give a length. The length of the string can be different than the ERP definition, but should most likely match. When combining data from multiple ERP Systems, such as Baan IV and ERP LN, the lengths of the fields must be the same. The Default ERP Queries provided by CrossroadsRMC are already compatible across versions.

The Length of all Date Fields should be set to 20.

Mandatory: This checkbox is not editable. Fields marked mandatory cannot be deleted because they are required for ERP Specific calculations, such as Amount (Calculated as Amount * Currency Rate * Kind of Order) and Profit (Amount * Cost). Etc.

Where Mapping

There where clause fields that pass parameters for Order Date and Kind of Order cannot be edited. The user can only add Where Clauses that filter data, such as the Position Number where clause below. No new conditions can be added that take parameters (?) as input.

If the conditions you need are not on the list click the Green + Button. When done make sure to click the Green Checkbox to accept changes. Cancel edits by clicking the Red X button at any time.

Tip: The Transaction Date From/To fields are the Dates the query will run off of. This can be changed to ‘odat’ instead of ‘trdt’ in both the From and To clauses to change the query to select on Order Date instead of Transaction Date.

Tip: The NoPosition0 Filter is a standard filter that will exclude Installment Invoices. Remove this option to include Installment Invoices.

Where ID: Must be unique. Not used, plain text version just to identify.

Where Clause: This field is substituted directly into the query and must be formatted exactly. Use the format of the existing lines. For table fields, use the format;

t in front to indicate Table

pp = Package in Baan/ERP LN

mmm = Module in Baan/ERP LN

999 = Table Number in Baan/ERP LN

XXX or YYY = Use XXX for you base company. Use YYY for your shared data company. If not sharing data they are interchangeable.

.t_fnam = field name, preceded by ‘.t_’.

Mandatory: This checkbox is not editable. Conditions marked mandatory cannot be deleted.

Join Mapping

Any tables added must include a JOIN clause. Note: Joining Tables creates the biggest query time hit. Remove unneeded joins and/or create multiple queries with just a few Joins to speed Query time up.

If the table you need is not Joined click the Green + Button. Copy formatting from existing JOINS. When done make sure to click the Green Checkbox to accept changes. Cancel edits by clicking the Red X button at any time.

Join ID: Must be unique. Not used, plain text version just to identify.

Join Clause: This field is substituted directly into the query and must be formatted exactly. Use the format of the existing lines. For table fields, use the format;

t in front to indicate Table

pp = Package in Baan/ERP LN

mmm = Module in Baan/ERP LN

999 = Table Number in Baan/ERP LN

XXX or YYY = Use XXX for you base company. Use YYY for your shared data company. If not sharing data they are interchangeable.

.t_fnam = field name, preceded by ‘.t_’.

Mandatory: This checkbox is not editable. Joins marked mandatory cannot be deleted.

Other options

Other options exist for each Query Row:

Sets that query as the Default Query for that ERP Version. If you are editing a query from your current ERP Version it will make that query the default Application.

Allows you to view the query string. You cannot modify the query here, but you may notice mistakes in typing. If query support is needed you can Copy and Paste the Query Text using this button.

Allows you to test the query. Results are shown in the grid below. Here you will also notice the Calculated Fields for Amount, Quantity, Cost and Profit. These are based on Baan / ERP LN Specific calculations, considering Cancellations and Currency Difference calculations. These numbers should match the Baan History Report Calculations.

Exporting the query

Once the query has been established and tested by the Administrator, it can be distributed to the Clients. Clients must get query from the Administrator, they cannot create or edit their own queries.

The option to Export Query File is seen under the File menu at the top of the screen.

This will create an XML file of your choice which you can then distribute to the Clients.

Clients can then run Import Query from the main screen, File menu to receive the query.

If the query has been marked ‘Default for Version’ by the administrator, the import will update all data connections for the same ERP Version to point to the new Query. To manually assign the query to use for any given data connection, the client can attach the query through the Manage Connections option under the Data Setup / Data Connections menu on the main form.

Any new queries will appear on the Drop Down option for Related Query.

Appendix 1: Default QUeries

Queries here are later interpreted for Microsoft SQL Server or Oracle connections. Substitutions are made for Infor Company Numbers, included in the Database Table Names, as follows:

XXX: Your Shared Company Data. If not using Shared Data, this is your Base Company.

YYY: Your Transaction Records Company, when using Shared Data.

If not using Shared Data, your Shared and Transaction Company will be the same, so they are interchangeable.

Baan IV

SELECT ttdsls051YYY.t_orno

, ttdsls051YYY.t_pono

, ttdsls051YYY.t_invn

, ttdsls051YYY.t_cuno

, ttccom010XXX.t_nama

, ttdsls040YYY.t_cbrn

, ttcmcs031XXX.t_dsca

, ttdsls040YYY.t_crep

, ttccom001YYY.t_nama

, ttdsls051YYY.t_cprj

, ttdsls051YYY.t_item

, ttiitm001XXX.t_dsca

, ttiitm001XXX.t_ctyo

, ttdsls051YYY.t_qana

, ttdsls051YYY.t_pric

, ttdsls051YYY.t_copr

, ttdsls051YYY.t_rats

, ttdsls051YYY.t_amta

, ttdsls051YYY.t_trdt

, ttdsls051YYY.t_odat

, ttdsls051YYY.t_ddta

, CAST(ttdsls051YYY.t_ddtb as varchar(19))

, CAST(ttdsls051YYY.t_invd as varchar(19))

, ttdsls051YYY.t_citg

, ttcmcs023XXX.t_dsca

, ttdsls051YYY.t_cwar

, ttcmcs003XXX.t_dsca

, ttdsls051YYY.t_ckor

FROM

ttdsls051YYY LEFT JOIN ttccom010XXX ON ttccom010XXX.t_cuno = ttdsls051YYY.t_cuno

LEFT JOIN ttdsls040YYY ON ttdsls040YYY.t_orno = ttdsls051YYY.t_orno

LEFT JOIN ttcmcs031XXX ON ttcmcs031XXX.t_cbrn = ttdsls040YYY.t_cbrn

LEFT JOIN ttccom001XXX ON ttccom001XXX.t_emno = ttdsls040YYY.t_crep

LEFT JOIN ttiitm001XXX ON ttiitm001XXX.t_item = ttdsls051YYY.t_item

LEFT JOIN ttcmcs023XXX ON ttcmcs023XXX.t_citg = ttdsls051YYY.t_citg

LEFT JOIN ttcmcs003XXX ON ttcmcs003XXX.t_cwar = ttdsls051YYY.t_cwar

WHERE ttdsls051YYY.t_odat >= ?

AND ttdsls051YYY.t_odat <= ?

AND ttdsls051YYY.t_ckor >= ?

AND ttdsls051YYY.t_ckor <= ?

AND ttdsls051YYY.t_pono <> 0

ORDER BY ttdsls051YYY.t_orno, ttdsls051YYY.t_pono, ttdsls051YYY.t_trdt

Baan V

SELECT ttdsls451YYY.t_orno

, ttdsls451YYY.t_pono

, ttdsls451YYY.t_invn

, ttdsls451YYY.t_ofbp

, ttccom100XXX.t_nama

, ttccom130XXX.t_name

, ttdsls450YYY.t_cbrn

, ttcmcs031XXX.t_dsca

, ttdsls450YYY.t_crep

, ttccom001YYY.t_nama

, ttdsls451YYY.t_cprj

, ttdsls451YYY.t_item

, ttcibd001XXX.t_dsca

, ttcibd001XXX.t_ctyo

, ttdsls451YYY.t_qana

, ttdsls451YYY.t_pric

, ttdsls451YYY.t_copr_1

, ttdsls451YYY.t_rats_1

, ttdsls451YYY.t_amta

, ttdsls451YYY.t_trdt

, ttdsls451YYY.t_odat

, ttdsls451YYY.t_ddta

, CAST(ttdsls451YYY.t_ddtb as varchar(19))

, CAST(ttdsls451YYY.t_invd as varchar(19))

, ttdsls451YYY.t_citg

, ttcmcs023XXX.t_dsca

, ttdsls451YYY.t_cwar

, ttcmcs003XXX.t_dsca

, ttdsls451YYY.t_ckor

FROM

ttdsls451YYY LEFT JOIN ttccom100XXX ON ttccom100XXX.t_bpid = ttdsls451YYY.t_ofbp

LEFT JOIN ttccom110XXX ON ttccom110XXX.t_ofbp = ttdsls451YYY.t_ofbp

LEFT JOIN ttdsls450YYY ON (ttdsls450YYY.t_trdt = (SELECT MAX(ttdsls450YYY.t_trdt) FROM ttdsls450YYY WHERE ttdsls450YYY.t_orno = ttdsls451YYY.t_orno) AND ttdsls450YYY.t_orno = ttdsls451YYY.t_orno)

LEFT JOIN ttcmcs031XXX ON ttcmcs031XXX.t_cbrn = ttdsls450YYY.t_cbrn

LEFT JOIN ttccom001XXX ON ttccom001XXX.t_emno = ttdsls450YYY.t_crep

LEFT JOIN ttcibd001XXX ON ttcibd001XXX.t_item = ttdsls451YYY.t_item

LEFT JOIN ttcmcs023XXX ON ttcmcs023XXX.t_citg = ttdsls451YYY.t_citg

LEFT JOIN ttcmcs003XXX ON ttcmcs003XXX.t_cwar = ttdsls451YYY.t_cwar

LEFT JOIN ttccom130XXX ON ttccom130XXX.t_cadr = ttdsls451YYY.t_stad

WHERE ttdsls451YYY.t_pono <> 0

AND ttdsls451YYY.t_trdt >= ?

AND ttdsls451YYY.t_trdt <= ?

AND ttdsls451YYY.t_ckor >= ?

AND ttdsls451YYY.t_ckor <= ?

ORDER BY ttdsls451YYY.t_orno, ttdsls451YYY.t_pono, ttdsls451YYY.t_trdt

ERP LN

SELECT ttdsls451YYY.t_orno

, ttdsls451YYY.t_pono

, ttdsls451YYY.t_invn

, ttdsls451YYY.t_ofbp

, ttccom100XXX.t_nama

, ttccom130XXX.t_ccit

, ttdsls450YYY.t_cbrn

, ttcmcs031XXX.t_dsca

, ttdsls450YYY.t_crep

, ttccom001YYY.t_nama

, ttdsls451YYY.t_cprj

, ttdsls451YYY.t_item

, ttcibd001XXX.t_dsca

, ttcibd001XXX.t_ctyo

, ttdsls451YYY.t_qoor