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