ajdjackson, Member
Hi
I wish to create a series of donut charts all with the same query except for a change in Site_ID.
I can create the chart fine but other than cutting and pasting the code multiple times I can't think of a way to do it better.
This obviously works but seems inefficient and I then would have to hard code for any additions/deletions.
Have you any suggestions on a better way to do this?
Thanks
Jake
myDBR Team, Key Master
What is the Site_ID you are referring to? Is it same data for the different charts or do you query different data based on the Site_ID?
--
myDBR Team
ajdjackson, Member
Hi
Thanks for getting back.
Here's the query:
select 'dbr.chart', 'donut', '', 450, 350;
select 'dbr.chart.options', 'donut.size', 20;
select 'dbr.chart.options', 'donut.text', 'Sales Budget';
select 'dbr.chart.options', 'donut.font.size', 20;
select 'dbr.chart.options', 'label_font_size', 12;
select 'dbr.chart.color', '0x008000', '0xFFA500', '0xFF0000';
select 'Sold',count(a.tblSalesInv_XIDJob) from hilmark.tblsalesinv_j a where year(a.tblSalesInv_Date) = year(curdate()) and a.site_id = 5 union select 'Booked',count(b.tblcontractxidjobno) from hilmark.tblsitesplotsjake b where year(b.Plot_BookDate) = year(curdate()) and b.tblcontractxidjobno not in (select tblsalesinv_xidjob from hilmark.tblsalesinv_j) and b.site_id = 5 union SELECT 'Required',sum(a.salesnum) - b.Sold -c.Book FROM hilmark.tblsalesbudgets a join (select count(tblSalesInv_XIDJob) as Sold from hilmark.tblsalesinv_j where year(tblsalesinv_date) = 2016 and site_id = 5) b join (select count(tblContractXidJobNo) as Book from hilmark.tblsitesplotsjake where year(Plot_BookDate) = 2016 and tblContractXidJobNo not in (select tblSalesInv_XIDJob from hilmark.tblsalesinv_j) and site_id = 5) c where year(Bgt_Date)=2016 and a.site_id = 5;
So as you can see I'm hardcoding the site_id.
I would like to do a group by site_id and create all the donuts in one go.
Hope you follow that.
Cheers
Jake
myDBR Team, Key Master
There is two things you can optimize. Firstly, instead of doing the actual possible heavy query only once and secondly, reuse the chart settings.
To optimize the query. Create a temporary table and query all data at once. You do the same query just group by the site_id (select site_id, 'Sold',count(*)...).
To create the charts, loop through the site_id's using a SQL cursor.
--
myDBR Team
ajdjackson, Member
Thanks for the hint.
I've never used cursors before.
Once I discovered that you declare the cursor before the creation of the temporary table I was able to get it working :)
Very powerful.
On a separate issue I'm displaying the above graphs in a jquery dialogue (modal) using a button.
One issue I can't resolve is that the Powered by mydbr footer is for some reason appearing after 11 of the graphs. There are 13 in total.
select 'dbr.html','<div id="budgraphs">
<div class ="graphs">';
select 'dbr.title',''; select 'dbr.chart', 'donut', '', 450, 350; select 'dbr.chart.options', 'donut.size', 20; select 'dbr.chart.options', 'donut.text', v_sitename; select 'dbr.chart.options', 'donut.font.size', 10; select 'dbr.chart.options', 'label_font_size', 10; select 'dbr.chart.color', '0x008000', '0xFFA500', '0xFF0000';
select 'Sold',v_sold union select 'Booked',v_book union select 'Required',v_req;
select 'dbr.html','</div>'; end loop get_salesbud;
close salesbud_cursor; select 'dbr.html',' <div class="clear"></div>'; select 'dbr.html','</div>';
The class graphs is just a float:left and I'm calling the report using load() with section #budgraphs.
Any thought as to why it's appearing where it is?
Many thanks
Jake
myDBR Team, Key Master
On a separate issue I'm displaying the above graphs in a jquery dialogue (modal) using a button.One issue I can't resolve is that the Powered by mydbr footer is for some reason appearing after 11 of the graphs. There are 13 in total.
You are embedding the reports into your own page? it is difficult advise without seeing the whole setup.
--
myDBR Team
You must log in to post.