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.

 

Illustrating the steps to create a chart with datapine's SQL query box

 

1. Access the SQL mode

 

In the Chart Creator, simply click on the SQL Box icon on the toolbar to switch to the SQL mode. The SQL Box will appear above the chart preview.

 

 2. Enter SQL Query

 

Start to build your SQL query in the SQL Box:

 

a) Paste or type your SQL Query into the grey area below Enter SQL Query. You can simply type in your field labels or drag and drop fields from your data source structure into the SQL Box on the left.

 

b) 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’.

 

 3. Choose datasource(s) and Run Query

 

Specify the datasource(s) used to create the query:

 

a) To the right, select the data source(s) you are referring to in your query by simply clicking on the data source name in the dropdown menu. Selected data sources will be shown with a checkmark next to the data source name.

 

b) You can now click on Run Query to execute the script.

 

 4. Build your chart

 

After you’ve executed your SQL code you can now start to visualize it using the variables from your query:

 

a) Select a chart type in the dropdown above the chart preview.

 

b) You can now choose on which axes you wish your results to be displayed by simply selecting the label names in the axes drop-downs. If you deselect a value, it will be shifted to the other axis and can then be selected there.

 

c) Run the query again to apply any changes to the chart below.

 

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.