Expectus & exMon Support

In this demo we're going to create our first dataset in Turnkey. Turnkey rules can be created to monitor datasets and report on data within the dataset that fails to meet the requirements of the rules.

Creating Products with category

1. Open a workspace
2. Open the Dataset tab
3. Press the Create dataset button in top right corner
4. Set the name of the dataset as Products with category
5. Select the data provider exMon
6. Paste the following SQL code into the dataset definition:
   

   SELECT product.[ProductID]
         ,product.[Name]
         ,product.[ProductNumber]
         ,product.[Color]
         ,product.[StandardCost]
         ,product.[ListPrice]
         ,product.[Size]
         ,product.[SellStartDate]
         ,product.[SellEndDate]
         ,category.Name as Category
     FROM [Demo].[Product] product
     INNER JOIN [Demo].[ProductCategory] category ON product.ProductCategoryId=category.ProductCategoryID

7. Press Preview Dataset to see the data the dataset contains
   
   
8. Now switch to the Properties tab
9. In Schedule select Every Week
10. In Description write Product with category in exMon database
    
    
11. Now switch to the Column settings tab
12. Select ProductId
13. Press Set as primary key and select Submit
    
    
14. Select Standard Cost
15. Change Format as type to Currency
16. Change Decimal places to 4
    
    
17. Select List Price
18. Change Format as type to Currency
19. Change Decimal places to 2
    
    
20. Hover over the Save button in top right corner and select Save and Publish

Creating Sales Order Details

1. Open a workspace
2. Open the Dataset tab
3. Press the Create dataset button in top right corner
4. Set the name of the dataset as Sales Order Details
5. Select the data provider exMon
6. Paste the following SQL code into the dataset definition:

   SELECT
     header.SalesOrderID, detail.SalesOrderDetailID, 
     header.AccountNumber, product.ProductId, product.Name, product.ProductNumber,  
     detail.UnitPrice, detail.OrderQty, detail.UnitPriceDiscount, detail.LineTotal,
     header.OrderDate,
     cust.LastName as CustomerLastName,
     cust.SalesPerson
   FROM [Demo].[SalesOrderDetail] detail
   left join [Demo].[SalesOrderHeader] header on detail.SalesOrderId=header.SalesOrderID
   left join [Demo].[Product] product on detail.ProductID=product.ProductID
   left join [Demo].[Customer] cust on header.CustomerID=cust.CustomerID

7. Press Preview Dataset to see the data the dataset contains
   
   
8. Now switch to the Properties tab
9. In Schedule select Every Week
10. In Description write Sales Order Details in the exMon database
    
    
11. Now switch to the Column settings tab
12. Select SalesOrderDetailId
13. Press Set as Primary key and select Submit
    
    
14. Select Unit Price
15. Change Format as type to Currency
16. Change Decimal places to 2
    
    
17. Select UnitPriceDiscount
18. Change Format as type to Percentage
    
    
19. Select LineTotal
20. Change Format as type to Currency
21. Change Decimal places to 3
    
    
22. Select OrderDate
23. Change Format as type to Date and time
    
    
24. Hover over the Save button in top right corner and select Save and Publish