Connecting Salesmatrix v5.xx to a SWOT data repository

At the creation of the SWOT dataset an email is sent to the admin user detail database access to the database behind SWOT. The email will look something like this (Highlights added):
Thank you for verifying your email address. Here are your access credentials for the Salesmatrix SWOT app.

Login name: hostmaster@Mydataset.com

Password: MDP6ODQ3QU

Company Database Name: MyDataset

Company Database Admin Login: MyDataset_dbo

Company Database Admin Password: WdJKtffR677

Company Data Access Token: xxxxxxx-10E3-462A-8F3A-AD4DFD3F3EC8
Using this we can create a script to retrieve the SWOT data into the Salesmatrix application easily. The data transfers from SQL server and the 4 selection statements in the Salesmatrix SMD script file can consist of the extensive range of SQL syntax and functions.
 
Replace the yellow highlighted items in the script below with the matching items in your acces credentials email and save it as a text file with the extension 'SMD'. eg MyScript.smd
Trans Customer Link =
Trans Sman Link =
Trans Date =T.dtrandate
Trans Inv No =
Trans Prod Link =
Trans Value =
Trans Cost =
Trans Units =
Prod Link =
Prod Name =
Prod Value =
Prod Cost =
Prod Cat1 =
Prod Cat2 =
Cust Link =
Cust Name =
Cust Cat1 =
Cust Cat2 =
Sman Link =
Sman Name =
Sman Cat2 =
Sman Cat1 =
ProdXSQL =SELECT DISTINCT PRODUCT.CID, PRODUCT.CNAME as CNAME, PRODUCT.PCAT1 as PCAT1, PRODUCT.PCAT2 as PCAT2,PRODUCT.PCAT3 as PCAT3, ExternalRef1 as MDesc FROM DBO.PRODUCT PRODUCT
CustXSQL =SELECT DISTINCT CUSTOMER.CID, CUSTOMER.CNAME as CNAME, CUSTOMER.CCAT1, CUSTOMER.CCAT2, CUSTOMER.CCAT4, CUSTOMER.CCAT3 FROM CUSTOMER CUSTOMER
SmanXSQL =SELECT distinct upper(left(ExternalRef1,3)) as cid, ExternalRef1 as cname  from dbo.transactions 
TranXSQL =Select T.ccustid, T.cprodid,isnull(Upper(left(T.ExternalRef1,3)),'0') as csmanid,T.nvalue,T.ncost,T.nunits,convert(nvarchar,year(T.dtrandate))+right('0'+convert(nvarchar,month(T.dtrandate)),2)+right('0'+convert(nvarchar,day(T.dtrandate)),2) as dtrandate from dbo.transactions T
Dates =
DescP =Products
DescC =Customers
DescS =SalesPersons
CatP1 =Group
CatC1 =Group
CatS1 =Category_1
CatP2 =Contents
CatC2 =Parent
CatS2 =Category_2
CatC3 =Rep
CatC4 =Agent
CatC5 =Rep
CatP3 =Group
Cust Cat3 =
Cust Cat4 =
Cust Cat5 =
TOOLTIP = FromSWOTServer
DEFAULTDSN=
DEFAULTDATERANGE=
DEFAULTFREQ=
DEFAULTDATASETNAME=
AUTO_DATE_TODAY =1
AUTO_DATE_ADVANCED =1
AUTO_TEXTDSN = DRIVER=SQL Server; SERVER=customerswot.com,1533; UID=MyDataset_dbo; PWD=WdJKtffR677; DATABASE=MyDataset
 
 

Add Feedback