Partners
Download
BuyMS SQL PHP Generator online Help
| Prev | Return to chapter overview | Next |
Data Query
This query returns data to be used by the chart. The retrieved data must contain exactly one column to specify labels along the major axis of the chart (domain column) and one or more columns to specify series data to render in the chart (data columns).
To allow a chart to be sensitive to grid data, refer to the chart's parent page query using the %source% placeholder.
Example 1
Assume we have the following table:
CREATE TABLE usd_exchange_rate (
id int NOT NULL,
exchange_date date,
eur_rate decimal(10,4),
gbp_rate decimal(10,4),
chf_rate decimal(10,4),
cad_rate decimal(10,4),
aud_rate decimal(10,4),
/* Keys */
PRIMARY KEY (id)
);
Our goal is to create a chart that displays the USD course. As data are already grouped, our query is very simple:
%source%
Then we should specify the exchange_rate column as domain column and all *_rate columns as data columns.
Example 2
Suppose we have two tables department and employee defined as follows:
CREATE TABLE department (
id int NOT NULL,
name varchar(100) NOT NULL,
/* Keys */
PRIMARY KEY (id)
);
CREATE TABLE employee (
id int NOT NULL,
full_name varchar(100) NOT NULL,
department_id int NOT NULL,
salary int NOT NULL,
/* Keys */
PRIMARY KEY (id),
/* Foreign keys */
CONSTRAINT fk_emp_department
FOREIGN KEY (department_id)
REFERENCES department(id)
);
Our goal is to create a chart representing the total salary by departments on the Department webpage. The following query returns the appropriate data:
SELECT
d.name as dep_name,
SUM(e.salary) AS total_salary
FROM
(%source%) d
INNER JOIN employee e ON (e.department_id = d.id)
GROUP BY
d.name
order by 2 desc
Then we should specify dep_name as the domain column and total_salary as a data column.
| Prev | Return to chapter overview | Next |
