Overview: Query Builder for Microsoft SQL Server

HOW TO

How to access the Query Builder for Microsoft SQL

Navigate to Metrics > Query Builder to access the Query Builder for Microsoft SQL. Click the green + Create Custom Metric button and select your connected Microsoft SQL Data Source from the Data Source drop-down list.

these steps

How to use the Query Builder for Microsoft SQL

Custom Metric Name: Enter a name for your Custom Metric. This Custom Metric name will be available in the Designer after saving.

SQL: Construct query strings to access data from your Database using Structured Query Language (SQL).

Learn more about SQL here.

Data Type: Selecting the appropriate Data Type ensures that your Custom Metrics will be synced correctly in Databox.

Daily Values: This Data Type should be selected when your Metric reflects the most up-to-date Daily Value for the Metric. In Databox, the latest daily entry will be displayed for the Custom Metric.
Total Values: This Data Type should be selected when your Metric reflects the current total Value for the Metric. In Databox, the latest entry will be displayed for the Custom Metric.

For longer Date Ranges, data will be not aggregated and the most recent Value will be displayed for the selected Date Range. This is the main difference between "Total Values" and "Daily Values."
Event Values: This Data Type should be selected when your Metric Value selection reflects individual Value(s) for the Metric at the specified Date/ Timestamp. This Data Type is the default selection because it is appropriate for the majority of use cases.

For longer Date Ranges, individual Metric Values will be aggregated for the entire Date Range and the aggregated Value will be displayed for the Custom Metric.

Data Preview: After clicking Run query, a Data Preview of the Custom Metric will be displayed.

How to create a Custom Microsoft SQL Metric [Example]

The Query Builder for Microsoft SQL is designed so you can directly enter SQL queries. For our example, we will create a Custom Metric that returns a list of Product Names and their associated Order Quantities.

Navigate to Metrics > Query Builder

Click the green + New query button

Select the appropriate Microsoft SQL Data Source

In order to accurately explain what this Custom Metric is reporting on, we'll name the Custom Metric "Product Name by Order Quantity"

In the SQL field, we will enter the query below. A semicolon at the end of the query is not necessary

SELECT TOP 20 
p.ModifiedDate AS date, 
p.Name, sd.OrderQty 
FROM Sales.SalesOrderDetail sd 
INNER JOIN Production.Product p 
ON sd.ProductID = p.ProductID 
ORDER BY sd.OrderQty DESC

We will select Total Values as the Data Type. This selection is based on the way data is stored and updated in our Database

Click Run query to generate a Data Preview of the Custom Metric

add_user_popup@2x

If you don’t see any data, double check your query and try it directly in your Database. If no results are displayed there, an error exists in your query. You should also check to confirm that the Microsoft SQL user has the necessary permissions to access the Database from Databox's public IP.

Once we confirm that these are the results we're looking for, we'll click Save to save the Custom Metric

How to add a Custom Microsoft SQL Metric to a Databoard

Learn how to add a Custom Metric to a Databoard here.

Additional Information

The query must limit the query result to a maximum of 1000 rows. LIMIT the query if you’re not sure if you will exceed this number.
The query must contain a column named “data” (using AS when SELECT-ing data is perfectly acceptable)
One column must contain the Date value. You can cast any column to name “date” using the “AS” SQL syntax
One column should always contain the Metric Value
Make sure your Firewall is open to Databox's public IP and your Port is correctly forwarded to the server