How can I aggregate data to a joint data set?

Arale

Hello All,

I came across a problem in my report. And I need your help:)

I have two data sets which come from two different data sources.
In Data set A, SQL looks like:
SELECT report_date, placement_id, SUM(...), SUM(...)
FROM Table1
GROUP BY report_date, placement_id;

In Data set B, SQL looks like:
SELECT placement_id, ****, ****
FROM Table2
GROUP BY placement_id;

I need data from both of the data sets, so I have to join them based on their common field "placement_id". So "placement_id" must be in the select statement. And also because I aggregate the data in Data set A, I have to include "placement_id" in the group by clause.

Here comes the problem. I ended up having multiple rows for each report_date in my report, simply because there might be several different placement_ids for each day. This is not what I want:(

I wonder if I could aggregate the data in the joint data set by the same report_date again? I tried, but I didn't figure out how to achieve this. Seems like we can't do much things to the joint data set...Do you by chance know how to do it?

And if I could use the joint data set as part of SQL, I may do:
SELECT report_date, SUM(...), SUM(...)
FROM ("joint data set)
GROUP BY report_date;
Can I do this in BIRT?

I hope my explanation is clear enough. Any suggestions will be much appreciated. Thank you very much:)

Tubal

You should be able to group your table by the report date, and aggregate on whatever fields you like.

So basically, your table would have no detail row, only a group footer that shows the aggregate value.