Documentation

SQL Chart Creation

 

In the Chart Creator, you can easily paste your existing scripts into our SQL Box to visualize your data or perform advanced cross-database queries on-the-fly. The following steps demonstrate how to use the SQL Box and how to create charts and tables based on your SQL queries in datapine.

 

 

1. To access the SQL mode open the Analyzer and simply click on ‘Switch to SQL Mode’ on the bottom left of your screen. The SQL Box will appear left to the chart preview.

 

How to open SQL Mode in Analyzer

 

 

2. Paste or type your SQL Query into the white textbox on the left. You can simply type in your field labels or drag and drop fields from your data source structure into the SQL Box.

 

Please note, in case you have defined custom labels for your database fields during the setup and you are using these labels in the SQL code, you will need to address the tables and fields by ‘Internal name’. Also make sure you have the checkmark ‘Use Internal field names’ checked.

 

 

3. Specify the datasource(s) used to create the query by selecting the data source(s) you are referring to in your query by simply clicking on the data source name in the dropdown menu on top of the query box. Selected data sources will be shown with a checkmark next to the data source name.

 

You can now click on Run to execute the script.

 

 

4. After you’ve executed your SQL code you can now start to visualize it using the variables from your query. Make sure that the fields are selected in the correct axis in the dropdowns under the SQL box. If you deselect a value from the axis dropdown, it will be shifted to the other axis and can then be selected there. Run the query again to apply any changes to the chart. After you selected your fields chose a chart type from the chart selection at the top.

 

How to create a chart in SQL Mode

 

 

SQL based charts have the exact same options as charts made by drag and drop. You have access to the chart options, advanced options, X- and Y-axis formatting options as well as to the different chart types.

 

 

Useful tips:

 

 

  •  The SELECT * command is not enabled in datapine. You need to specify each individual field that you want to query.
  • Using variables in your SQL Queries is not supported by datapine. If you need help to optimize a query in datapine feel free to contact us at support@datapine.com.
  • If you are querying multiple data sources at once you have to include the data source names in { } in front of the table’s name such as {datasource}Table.Field  (e.g. {Sales}Customer.Country)
  •  Filter: If you wish the dashboard filters to apply to your SQL charts, you need to mention the filter’s field in your query. For example, if you want to perform a filter on “City”, you will have to insert the field “City” in the SELECT or the WHERE command of your SQL query, e.g. where “City” is not null.