Jump to content

BIRT Developer Center Download


Photo
- - - - -

Avoid repetitions


  • Please log in to reply
3 replies to this topic

#1 AxelB

AxelB

    Newbie

  • Members
  • Pip
  • 6 posts

Posted 12 September 2017 - 06:58 AM

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

 



#2 shamo

shamo

    Senior Member

  • Members
  • 190 posts

Posted 12 September 2017 - 07:37 AM

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



#3 AxelB

AxelB

    Newbie

  • Members
  • Pip
  • 6 posts

Posted 13 September 2017 - 01:26 AM

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



#4 wwilliams

wwilliams

    Junior Member

  • Members
  • 332 posts

Posted 13 September 2017 - 03:22 AM

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....