Avoid repetitions

Hi,


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.


 


Thx


Axel


 


Comments

  • 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?


     


    Thx


  • 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());

    maximoDataSet.open();


    var sqlText = new String();


    // Add query to sqlText variable.

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


Sign In or Register to comment.