Multiple filters on joint data set

cypherdj

Following on from my previous post (<a class='bbc_url' href='http://www.birt-exchange.com/modules/vbulletin/showthread.php?t=10945'>http://www.birt-exchange.com/modules/vbulletin/showthread.php?t=10945</a>) I now have my joint data set giving me each opened task, which user it is assigned to (or unassigned) and the date by which it must be completed.<br />
<br />
taskID|userID|targetDate|userName<br />
<br />
I have a report parameter "reportDate", and would like to display a table as follows:<br />
<br />
User|Overdue tasks|tasks due on reportDate|tasks due a week after reportDate<br />
<br />
I'm assuming I need to use several filters here, based on the reportDate. Then I'll need to aggregate on userID.<br />
<br />
Any idea how this can be done, if it can, with BIRT?

cypherdj

I think I got it! Can't believe how simple it was as well, definitely kudos for BIRT there!<br />
<br />
Basically, I created a table, bound my joint data set and added a group userIDGroup on row["userID"].<br />
Then in the table group header, I dropped <br />
<strong class='bbc'>1st cell</strong><br />
- row["userName"],<br />
<strong class='bbc'>2nd cell (aggregation)</strong><br />
- function: COUNT<br />
- filter condition: BirtComp.lessThan(row["targetDate"], params["reportDate"])<br />
- aggregate on group: userIDGroup<br />
<strong class='bbc'>3rd cell (aggregation)</strong><br />
- function: COUNT <br />
- BirtComp.equalTo(row["allOpenTaskByDepartment::TARGETDATE"], params["reportDate"])<br />
- aggregate on group: userIDGroup<br />
<strong class='bbc'>4th cell (aggregation)</strong><br />
- function: COUNT <br />
- BirtComp.between(row["allOpenTaskByDepartment::TARGETDATE"], params["reportDate"], DateTimeSpan.addDate(params["reportDate"], 0, 0, 7))<br />
- aggregate on group: userIDGroup<br />
...<br />
<br />
This seems to perform well, now I'm trying to figure out how to produce a stacked bar chart based on the data in this table. So the series to be stacked are the aggregate per user, x axis would hold the dates (overdue, reportdate, reportdate+1week, ...). <br />
<br />
Any suggestions welcome!