Stacked chart using xml

I am using the chart connector to create some xml from mysql

I can get this to work with a simple dataset but I do not understand how to get data in a series to create a stacked chart.

In mysql my query groups the values eg -
department, location, users
sales, main, 20
sales, branch, 50
production, main, 10
production, branch, 75

looking at the samples it uses json in the form of
var data = [{
main: “20”,
branch: “50”,
department: “sales”
}, {
main: “10”,
branch: “75”,
department: “production”
}];

When I use render_sql and the chartconnector I get xml of the data but not in a series similar to the json output. where am I going wrong?

Thanks

The xml for your var data = […];

20 50 sales 10 75 production

Chart data can be loaded from any datasource - series definition will be the same. To add the new series you need to use addSeries method like so:

var chart = new dhtmlXChart({ view:"stackedBar", value:"#main#", ... }); chart.addSeries({ value:"#branch#", ... }); chart.load(...);

thanks Alexandra,

the samples show how the data should look but I was hoping that the ChartConnector would do the translation from a sql statement with “group by” items into the series format that dhtmlx requires.

Maybe my sql skills are not as good as they should be but I don’t know any easy way to generate a mySQL query that will return the data as a series?

Thanks

Connector does not provide methods for grouping, but Chart does. Please have a look at the sample:

dhtmlxChart/samples/03_group/01_basic.html

I have seen the “grouping” option in the samples but this is for aggregating data in the XML that is returned by the connector.

I did some more investigation and found that I could use the following mysql to translate my extract -

SELECT
location,
SUM( CASE department WHEN ‘sales’ THEN users THEN users ELSE 0 END ) AS ‘sales’,
SUM( CASE department WHEN 'finance THEN users ELSE 0 END ) AS ‘finance’,

FROM user_by_location
GROUP BY location

this output of this query will be

location sales finance … …
head office 100 200 … …
area office1 20 50 … …
area office2 10 40 … …

In my PHP page is use the standard approach to create the XML
$gridConn->render_sql([the query string shown above],“location”,“sales,finance,…,…”);

the ChartConnector now generates the XML from this query as a series wich I can use in charts