Building a simple PIE chart from single row of data

JohnMelody

<p>I have a problem with creating a simple BIRT pie chart and perhaps someone could help.</p><p> </p><p>I have a data set that is generated from the following SQL SELECT statement.</p><p> </p><p>select count(*) total ,
sum(if(hour(StartTime) >= 0 and hour(StartTime) < 9,1,0)) earlyMorning,
sum(if(hour(StartTime) >= 9 and hour(StartTime) < 13,1,0)) Morning,
sum(if(hour(StartTime) >= 13 and hour(StartTime) < 14,1,0)) Lunch,
sum(if(hour(StartTime) >= 14 and hour(StartTime) < 18,1,0)) Afternoon,
sum(if(hour(StartTime) >= 18 and hour(StartTime) < 24,1,0)) Evening
from calls</p><p> </p><p>It groups a set of calls into sections based on the time the calls arrived.</p><p> </p><p>The data appears as a single row with the columns names as total, earlyMorning etc.</p><p> </p><p>E.g.</p><p>total   earlyMorning Morning   Lunch Afternoon   Evening</p><p>100 10 50   10   10 10</p><p> </p><p>I would like to present the data as a pie chart using BIRT 4.3 designer.</p><p>I can't seem to get the PIE chart to pick up the different categories i.e.  Morning, Lunch etc.</p><p> </p><p>Any help appreciated</p><p>John.</p>

BRM

<p>You need to rearrange your data so that categories are in one column and values are in another.  There are ways to do this using scripting but since your data is coming from a SQL database you can do something like this...</p><pre class="_prettyXprint _lang-sql">SELECT count(*) AS `count` , CASE WHEN hour(StartTime) >= 0 and hour(StartTime) < 9 THEN 'earlyMorning' WHEN hour(StartTime) >= 9 and hour(StartTime) < 13 THEN 'morning' WHEN hour(StartTime) >= 13 and hour(StartTime) < 14 THEN 'lunch' WHEN hour(StartTime) >= 14 and hour(StartTime) < 18 THEN 'afternoon' WHEN hour(StartTime) >= 18 and hour(StartTime) < 24 THEN 'evening' END AS time_period FROM calls GROUP BY 2 </pre>