Eclipse Community Forums: BIRT » selecting only first row of group in BIRT report

Home » Archived » BIRT » selecting only first row of group in BIRT report

selecting only first row of group in BIRT report [message #540193]

Tue, 15 June 2010 06:44

Eclipse User

Hi,

I have a list of next rows in report sorted by TIME_ID desc:

TIME_ID ID Name City
------------------------------------------------------------ --------
12:59:46 35 Mike New York
12:59:31 35 Mike New York
12:59:07 35 Mike New York
12:57:03 35 Mike New York
12:55:36 38 Jack Chicago
12:51:23 38 Jack Chicago
12:41:46 38 Jack Chicago
12:54:39 40 Danny Boston
12:54:02 40 Danny Boston
12:52:46 40 Danny Boston
------------------------------------------------------------ ----------

I need to get only one (first) row for each of group (every group differes by ID) with max TIME_ID. Example:

TIME_ID ID Name City
------------------------------------------------------------ --------
12:59:46 35 Mike New York
12:55:36 38 Jack Chicago
12:54:39 40 Danny Boston
------------------------------------------------------------ ----------

If this is possible, I would like the way without sql query because I use Jazz RTC connection to BIRT and writing SQL is not currently supported!

Any help would be appreciated!

Thanks,
Milan

Re: selecting only first row of group in BIRT report [message #540202 is a reply to message #540193]

Tue, 15 June 2010 07:15

Eclipse User

Add a computed column to your dataset using the Aggregation based on a running count over the specific group. Then at the Table level, filter where that count = 1.

Re: selecting only first row of group in BIRT report [message #540264 is a reply to message #540202]

Tue, 15 June 2010 09:43

Eclipse User

Hi Richard,

I didn't understand very vell your post. I created group over the table, and called it GROUP_ID. Then, I created aggregation named ROWNUM_IN_GROUP based on GROUP_ID, put it on new table column and got number of rows in group for each row.
As I understood, I must create a computed column in my dataset which is based on that aggregation, and set filter of that count to 1. The last part was not so clear to me, and if you can be more specified and write some steps, I would be gratefull to you!

Regars,
Milan

Re: selecting only first row of group in BIRT report [message #540268 is a reply to message #540264]

Tue, 15 June 2010 09:55

Eclipse User

Sorry, I wasn't completely correct or clear in the sequence.

1 - Create your dataset.

2 - Add the table based on your dataset to the report.

3 - Add a Group to the table.

4 - Select the Table, then go to the Property Editor in the Binding tab

You will see a list of the columns from the dataset.

5 - Click the "Add Aggregation" button

6 - Set the Column Binding Name and Display Name to "grouprow"

7 - Set Data Type to Integer

8 - Set Function to RUNNINGCOUNT

9 - Pick any field that should not be null as Expression

10 - Leave Filter Condition blank

11 - Check the radio button for Aggregate on Group and select the desired group

12 - Now still in the Property Editor, go to the Filters tab

13 - Click the Add button

14 - The new "grouprow" column will now appear in the first dropdown. Select it.

15 - Select "Equal To" in the second dropdown

16 - Enter 1 in the third dropdown/textbox and click OK

Now your report will show only the first row within each group, based on the sort order you've already applied.

Re: selecting only first row of group in BIRT report [message #540275 is a reply to message #540268]

Tue, 15 June 2010 10:08

Eclipse User

Thanks Richard, it works now Smile

Thank you very much on your help!

Regards,

Milan

Re: selecting only first row of group in BIRT report [message #666165 is a reply to message #540268]

Tue, 19 April 2011 07:41

Eclipse User

Hi Richard
The solution works well..is there a way to fetch only the last row in the group?? If so, could you please explain how to get it.. Thanks

Re: selecting only first row of group in BIRT report [message #666173 is a reply to message #666165]

Tue, 19 April 2011 07:57

Eclipse User

If you want the first row of group, you can at the end of mentioned steps add filter and set it to 1.

If you want last row, you can count elements in your group. Add new aggregation, give some name (e.g. num_of_rows), use count method and select Aggregate on group and choose your group.

Then, use same steps from third Richard comment, and at the end, instead of your filter (which will be RUNNINGCOUNT) don't use filter equal to 1, use filter equal to num_of_rows, that is, the aggregation name of number of rows in group.

Hope this helps,

Previous Topic:	go results in a column in a dataset
Next Topic:	HTML tidy errors

Goto Forum:

-=] Back to Top [=-

Current Time: Fri Jul 04 12:52:24 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter