Just select a database, type an sql query & click Run Query. Chart Preview will render your chart. You will see charts being generated on the fly. Change type of chart by clicking on the list of chart types below the boxes (Table, Line, Bar, etc).




Chart Creation page is divided into 4 sections:

  • Field List
  • SQL Pane
  • Visualization List
  • Chart Preview

Field List

It is located on the left side of the page. It lists all the fields present in all the data sources in the project. The fields are listed as collapsible dropdowns. The fields are grouped by tables. Within each table, they are further grouped by their data types. Tables are grouped under the data source name assigned to the database connection. E.g, Test Database is the name of the data source (database), Test Table is the name of a table in the database, and the fields in this table are present under groups of Text, Number & DateTime.

For illustration only

SQL Pane

It is located to the right of Field List, to the top. The fields listed in Field List can be dragged and dropped into the different placeholders of Data Pane. To begin creating a chart, just select a database, type an sql query & click Run Query. Chart Preview will render your chart.


For plotting charts & graphs such as line chart, area chart, bar chart & stack chart, the 1st column of your SQL query is used to populate X-axis. All subsequent columns are used to plot series on Y-axis. Similarly, for pie charts, donut charts, funnel charts the 1st column is used for pie categories and 2nd column used for measures. So you need at least 2 columns in your SQL query to plot graphs & charts. Here are some examples.

How to plot line chart / area chart / bar chart / column chart

Let's say you have the following query to calculate average sales, as well as total sale amount for each product

	 select product,avg(sale) as average_sales,sum(sale) as total_sales from product_sales group by product
	 


After pasting your SQL, if you click Run Query, you will see the following result table


If you click the Line chart visualization from visualization list, Ubiq will plot a line chart for you. In this case, the 1st column (product) of your query will be used for x-axis. The 2nd column (count(*)) will be used to plot Y-axis series. The 3rd column (sum(amount)) will be used to plot next line, and so on. You can plot multiple series as line/bar/column/area.


How to plot pie chart / donut chart

Let's say you have the following query to calculate total number of sales for each product

	 select product,sum(sale) as total_sales from product_sales group by product
	 


After pasting your SQL, if you click Run Query, you will see the following result table


If you click the Pie chart visualization from visualization list, Ubiq will plot a pie chart for you. In this case, the 1st column (product) of your query will be used for category values. The 2nd column (count(*)) will be used to plot chart measures. Since pie charts, donut charts and funnel charts can have only 1 series, you can use only 2 columns in your SQL query.


How to plot single number chart / guages

Single number charts require a single number as SQL result. So your SQL query needs to have only 1 column. Let's say you have the following query to calculate total number of sales

	 select sum(sale) as total_sales from product_sales group by product
	 


After pasting your SQL, if you click Run Query, you will see the following result table


If you click the Number chart visualization from visualization list, Ubiq will plot a number chart for you. In this case, the 1st column (count(*)) of your query will be used for number values.



Chart Preview

It is located to the right of Field List, below the Visualization List. Any time you drag and drop a new field or modify individual column settings, it updates in real time to display the visualization based on the data fields & visualization that you have selected. This provides the closest representation of how a chart will look like in your dashboard.



You can customize Chart Title, Axes Labels & Formats. To change the chart title, just click on the chart title. A textbox appears in its place. You can type in the new title and press Enter key. You can update the Axes Labels & Formats by entering the required values in the Chart controls for the same, below the chart.



To learn more about chart customization, please refer to Chart Customization

Please note, the chart preview fetches a maximum of 100 rows to plot the chart. However, the actual charts in dashboards have no row limit.



Saving Charts

You can save a chart by clicking Save button. You will see a form asking you to optionally select a dashboard where you may want to pin the chart. If you have not created any dashboard, or do not want to save the chart to a dashboard yet, you can directly click the Save button on the form. Later, you can pin it to a dashboard while editing a chart.


Editing Charts

You can edit a chart by clicking on Edit button against the chart, on the Charts tab. You will be directed back to the Chart Creation page pre-populated with previously saved values.




Copying Charts

You can copy a chart by clicking on Copy button against the chart, on the Charts tab. You will see a form where you can enter the chart name for the new chart and click the Save button. Please note, if you are copying a drill down chart, then the new chart will have the same parent as the old one. You read about Drill Down charts here.


Get Insights from your data

Business Intelligence, Dashboards or Analytics. Whatever you need, Ubiq can help

Try Ubiq for free