Filtering on max value

I would like to do something seamingly simple, but I can't figure out how.
I a text field I want to use dynamic text such as:
"The maximum count of accidents was xx. This occcured in the year yyyy."
I can get the dynamic value xx to show the correct maximum amount, but I can't figure out how to show the year yyyy, which is another field value in the row where accident equals the maximum number for accident.

I'm a novice using the drag and drop features of BIRT. I'm not a Java developer. Can anyone help?

Find more posts tagged with

Comments

kclark

Where is the year stored, is it in a variable, parameter, or in the table? If you're trying to display data in a variable or parameter in a text box you can us <VALUE-OF>vars["NAME OF VAR"]</VALUE-OF> and change the type to HTML.

mogwai

Thank you for the response. The year is a field in the database, as is #accidents. I also have a computed fields in the data set which gives me the max accident count. So the data set looks like this:
Year;#accidents;Max #accidents.

I tried to create a computed field with a filter. The field was Year and the filter was row["accidents"]==row["Max accidents"]. This filter doesn't work. Using a field rather than a static value in the filter value yields no results.

I could be going about this in the wrong way entirely, so to simplify things, I have a data set containing the fields Year and #Accidents:

2005;21
2006;18
2007;24
2008;17
2009;16

I'm looking for text that says: "The maximum number of accidents (24) occured in the year 2007."

Getting the 24 isn't too hard, but finding the matching 2007 baffles me.

Hans_vd

Hi Mogwai,

If you do it like this, it should work:

- create a computed max_accidents column in your data set that gets the max of #accidents
- create a table on the report bound to your data set
- add a filter to the table: #accidents equal to max_accidents
- drag a dynamic text item to the table with this expression:
"The maximum count of accidents was " + row["#accidents"]+ ". This occcured in the year"+ row["year"] + "."

Regards
Hans

mogwai

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="112121" data-time="1354784870" data-date="06 December 2012 - 02:07 AM">
Hi Mogwai, 
 
If you do it like this, it should work: 
 
- create a computed max_accidents column in your data set that gets the max of #accidents 
- create a table on the report bound to your data set 
- add a filter to the table: #accidents equal to max_accidents 
- drag a dynamic text item to the table with this expression: 
"The maximum count of accidents was " + row["#accidents"]+ ". This occcured in the year"+ row["year"] + "." 
 
Regards 
Hans </blockquote>

mogwai

This is such a good and universal solution. I've done it slightly differently. I created a table without filtering and put all the calculated values in the footer fields. What I couldn't achieve with filtering in the data set computed columns, I can do easily here in the fooer fields.
I made the table invisible and binded the text to the table instead of the data set. Now I can use the full data set and the filtered and calculated values in my text.
Thanks Hans. A small step for the BIRT community put a gigantic leap for me ;-).