Filtering out non duplicates of Birt dataset

rogerb99

<p>I'm using Birt Report Designer to create the report.</p><p>I need to create a Birt report that will show the duplicate values for a given field plus the other fields on that row of dataset results.  The MS SQL Server query returns a set of rows that look like this. The results are ordered by Rack.<br />
[font="'courier new', courier, monospace;"]RACK DC SYS<br />
09 2   DEF<br />
10 1   AAA  <br />
10 5   ABC<br />
12 1   A2B<br />
15 2   EFG[/font]<br />
<br />
After parsing the results, the display should look like this.<br />
[font="'courier new', courier, monospace;"]RACK DC SYS<br />
10 1   AAA  <br />
10 5   ABC[/font]<br />
<br />
Duplicates in the Rack column indicate a problem and I don't want to hide the problem by making the Rack field not accept duplicates.<br />
<br />
I'm not sure which event handler this parsing should be done in and how to parse & remove non duplicated rows from the result set. The database table should be untouched after this parsing.</p><p> </p><p>Any help would be greatly appreciated.</p>

ahsiang

Hi, you can create a SQL statement that does a join to the table twice where the first select does a group by and filter the count > 0 then join back the same file to select the records you want.

Alternatively, in BIRT, you create 2 data set, first a group by and the second a select all. After that, you use BIRT join data set option to join them. It's the same as above except this is for those who don't know how to write join SQL statement or has a join data set that is too complex. Simple join should use SQL statement which will return quicker result.