Avoid repetitions


assuming I have a data set table like this:

ID    Timestamp     State

1      2017-06-03   New

1      2017-06-04   Started

1      2017-06-08   Ongoing

1      2017-06-24   Solved

2      2017-05-01   New

2      2017-07-25   Started

2      2017-07-31   Ongoing

3      2017-04-17   New

3      2017-04-25   Started

4      2017-06-01   New

4      2017-06-01   Started

4      2017-06-05   Ongoing

4      2017-07-14   Solved


But I only want to see the latest entry (ID & State) before a certain key-date.

Assuming, keydate is 2017-06-30

I would like to see the following result in the data set preview:

ID    Timestamp     State

1      2017-06-24   Solved

2      2017-05-01   New

3      2017-04-25   Started

4      2017-06-05   Ongoing


Is there a smart way to get this implemented?

The Data Set is bound to a Dynamic Text Field. I only see to run through the complete list in 'On Fetch' and just store the latest entry for an 'ID'. But maybe there is a more smarter way to get this solved already on data set level.






  • What I will do will in the query will be to use max(timestamp) over ( partition by Id order by id asc) as newname


    then will use a filter like timestamp = newname. this will give you the results.


    Hope this helps

  • Hi Shamo,

    thanks for the input, I understand what you're intention is.

    But I'm failing on the implementation part.


    I'm working with BIRT in Eclipse. I've now added a ComputedColumn 'Newname' with Expression 'row["TIMESTAMP"]' and Aggregation ='MAX'.

    But this leads to that every entry gets the same timestamp.


    I cannot simply transfer this SQL expression "max(row["TIMESTAMP"]) over ( partition by row["ID"] order by row["ID"] asc)" to the Expression Builder?



  • wwilliamswwilliams Junior Member

    I believe Shamo was suggesting that you use that in the open of you data set e.g.

    maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());


    var sqlText = new String();

    // Add query to sqlText variable.

    sqlText = "SELECT max(timestamp) over....

Sign In or Register to comment.