Group Volume By Month

basti

Hey,<br />
<br />
I'm new to Birt and currentlly trying to create a report. I have a Database as a data source. With an easy SELECT statement I fetch all the data from the db I need. My result looks something like the following:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>creationdate idproduct volume
13.04.2010 00:00 2 100
19.04.2010 10:20 1 1200
19.04.2010 12:15 1 1000
19.03.2010 12:33 1 100
21.03.2010 13:31 1 600
19.03.2010 13:08 2 100
19.03.2010 09:37 1 1200
23.02.2010 07:58 1 40
23.02.2010 07:58 1 60
21.02.2010 13:31 1 300
23.01.2010 14:00 2 300
19.01.2010 10:20 1 100
28.01.2010 12:40 1 30
23.01.2010 14:00 1 100
</pre>
<br />
Now I would love to summerize the volume by product and month. That it looks something like the following<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
month product volume
4 1 2200
4 2 100
3 1 1900
3 2 100
2 1 400
...
</pre>
<br />
I tried to work with scripting etc. but I just dont get it managed. Maybe someone has a tip for that.<br />
<br />
Reagards and Thanks,<br />
Basti

mwilliams

Hi Basti,

If you create a 3 column table linked to this dataSet, then group the table by the date field, then add a second group that groups on product, you can put a dynamic text box in the first column in the product group header(group #2) with the expression, row["creationdate"].getMonth() + 1. Then, you'd put the product id in the second column of the product group header. Finally, in the third column, you'd put an aggregation that has a function of SUM and an expression of row["volume"]. Hopefully this all makes sense. If not, let me know what version of BIRT you're using and I'll make up a quick example with your sample data here.