Using Query Strings

This tutorial describes how database queries can be used to populate chart series and elements.

Simple chart

By default, the first column returned by your query is mapped on the x axis and the second column is mapped onto the y axis.

Consider:
'SELECT name, age FROM Employees'

This query will create a chart where the names are on the x axis, ages on the y axis, and might look something like this:

Figure 1. Using a simple query

Multiple series from a single query

Returning a third column splits the data into series. This feature is referred to as 'SplitBy'.

Consider:
'SELECT name, age, division FROM Employees GROUP BY division'

This time 3 columns are returned and rows are grouped by the division column. Because the third column is supplied, two series are created, one for each unique value of the division column. The resulting chart will look something like this:

Figure 2. Multiple series using a query

Another characteristic of the splitBy functionality is the aggregation of grouped values. For this example consider a database table with 3 fields [ Date, Items_Sold, Sales_Rep ] . Each day an entry is added with the number of items sold for each sales rep. In our database there are two and they have been selling from January - May.

Consider:
'SELECT Date, Items_Sold, Sales_Rep FROM Sales GroupBy Sales_Rep'

In this case the sales of each representative are aggregated for each month.

* This sample also uses the dategrouping setting of (TimeInterval.Month), see dataEngine for more info.

Figure 3. Multiple series with date grouping.

Count occurrences of a fields value.

For this case, let's say we have a table which is a log of logins into your main company server. The table contains a 'date' column and a 'name' column and each row is a login entry. To show the number of logins for each user we can use the following query:

Query:
'SELECT name, 1 FROM Orders GROUP BY name;'

This will create a table with a name and a value (1) for each login. This method takes advantage of the DataEngine's aggregation feature and the second column will be summed.

Figure 4. Counting occurences.