Tuesday, 22 June 2010

Query results chart

Hello,

while the code for generating charts for the status page and the profiling page was more or less trivial, code for the query results will be more complicated. This is because of the dynamic data structure that query results can have. You can get any number of rows and columns as a result. Also the data in the result is composite - there are strings, numbers, dates, etc.. Not everything is suitable for one chart or another.

I decided to introduce two (for now) query result formats that must be followed when you want to put your data to the charts. If this format is followed code will recognise that and will render a chart which suits best.

For now I will be talking about two types of charts: simple bar charts and stacked bar charts. For the simple bar chart we need to provide X and Y axis value pairs, something in a lines of this:

MonthSum
2009-11225
2009-121157
2010-01569
2010-02282

You could imagine now that the chart for this table would look like this:


Now this is easy. What if we know that the total amount on every month is composed of different parts and we would like to see them in the chart as well. For this we will have to add another column which specifies which component the data belongs to. This breaks the monolithic bars into the bars composed of series of data.

MonthSumOperator
2009-11129Bite
2009-1140Omnitel
2009-1156Tele2
2009-12176Bite
2009-12707Omnitel
2009-12274Tele2
2010-01422Bite
2010-01108Omnitel
2010-0139Tele2
2010-02110Bite
2010-02131Omnitel
2010-0241Tele2

For this data generated chart would look like this:


Note that for both charts labels for X and Y axis were taken from the column names. It is nice to have such details taken from the query result. However I would also imagine that some users would prefer to set details like these in the chart generation page. I will be adding some options there which will have default values but will give the ability to change them to whatever you like.

So If you would like to put your data to these gorgeous charts generated by pChart just write a MySQL query which puts your data to the formats described above and you are done. Any other chart types which are suitable for query results?

No comments:

Post a Comment