Making Queries

Now that have created a report, email or dashboards, you have certainly added in some placeholder visualisations within blocks or widgets. Now it’s time to make a query to get some data from your database and apply some visualisation to the resulting data.

To make a query, simply click on the “Edit query” button in a visualisation placeholder like shown in the image below:


Query editor button in DataReportive

This will open up the query editor. Let’s examine the query editor in more detail (as shown in the image below).


Query editor in DataReportive

The query editor is made from several components.

  1. To make a query you must first select a data source over which you can make the query to get results. The data source selector is the selector shown on the top left.
  2. Selecting a data source will give you all the available tables for that data source. You can click on a table to show you a preview of the data in that table (the first 100 rows). In MySQL it is equivalent to doing the following query: SELECT * FROM [table] LIMIT 100;.
  3. In the middle top you have an editor where you can write a query. Once you’ve done writing your query hit the “Run query” button to execute the query. Based on the SQL dialog the query editor will adapt its styling.
  4. Once the query is executed the table results will be shown in the middle lower section of the query editor. This will give you a preview of the resulting query.
  5. Between the Query edit section and results, in the same location as the Run Query button you have two additional selections you can make. The first is the view toggle. The view toggle will allow you to select between the tabular result of your data and any chart you may have applied to your result. Next to it is the visualisation selector. This will show you all the ways you can visualise your data. We will talk more about applying visualisations to data in the next section.

Example

Let’s go through a simple example. For this example we will use the Demo DB (MySQL) that is included in every DataReportive account and is used for demo purposes. In Demo DB there is a table called log which includes data about hits on different types of pages, along with the browser of the user and the IP.

Make sure you have the Demo DB datasource selected.

Now, let’s do a query that returns the total number of hits grouped by the page type. We will write something like this in our query editor: 


SELECT
    page_type AS 'Page Type',
    count(1) AS 'Hits'
FROM
    log
GROUP BY 1;

Running the query will return the following results:


Query results in DataReportive

Visualising Results

Now that we’ve made your query and got some results, you can apply a visualisation to the data. You will need to know how different visualisations work so you can know how to return your query result data.

In our example we notice that we have two columns in our results (the number column is not really returned with the results it’s just there to help you in case you need to count the rows). We notice that one column is text based, or what we call a categorical value, and the other is numeric in nature, or what we call a metric value.

Each visualisation has a requirement in terms of metric rows and categorical rows. In the following we provide a list of what these are for each visualisation.

Bar charts, Column Charts, Line Charts and Area charts

  • Requires 0 or 1 categorical row
  • Requires 1 or more metric row

Pie chart

  • Requires 1 categorical row
  • Requires 1 metric row

Scatter plot

  • Requires 1 categorical row
  • Requires 2 metric rows (One for the X-value, one for the Y-value)

Bubble chart

  • Requires 1 categorical row
  • Requires 3 metric rows (One for the X-value, one for the Y-value, one for the Size of each bubble)

Indicators

Simple indicator
  • Requires 1 metric row and uses its first value
Single indicator
  • Requires 1 categorical row and uses its first value
  • Requires 1 metric row and uses its first value
Double indicator
  • Requires 2 metric rows (one for the main value, and one for the change)

Visualization Options

After selecting a visualisation from the visualisation selector you can customise the look and feel of the chart using the visualisation options shown below.


Visualisation options in DataReportive

For example for a bar chart you can customise the colors, the axis components and title, the legend, background etc. Each chart will have its own set of visualisation options that you can customise.

Inline Queries

There might be cases where you may want to use a result from a query as part of a text in your report. You can do this with inline queries. Let say that in a paragraph you want to add the text "Number of users:" followed by the result of a query that gets that number of users. Using the HTML editor you can add in an inline query like shown in the following example: 

<p>Number of users: <metricclass="inline"></metric></p>

Inline queries in DataReportive

Once you've added the inline query by saving and closing the editor, you will be shown where it will be displayed. You can then click on the "[Click to Edit]" text to open up the query editor to write your query. As you will notice you will not be able to select a visualisation - this is an inline query so all results are text. You can only choose what divider can be used in order to render the results. For example if you have a result with three rows, each result in the row will be rendered, separated by ',' - the default separator.


Edit inline queries in DataReportive