It's all about the answers!

Ask a question

BIRT: Sorting Data Sets


Andrew Trobec (48712129137) | asked Sep 07 '10, 4:11 a.m.
Good Morning,

I am trying to create a BIRT data set that is sorted (1) by WI_ID and (2) by TIME_ID.TIMESTAMP so as to have a list of all work items ordered by ID and timestamp, with a computed column ranking them. For example i would like to have the following result:

FACT_ID 2 | WI_1 | 1 Sep 2010 16:03 | RANK 1
FACT_ID 5 | WI_1 | 1 Sep 2010 16:30 | RANK 2
FACT_ID 3 | WI_2 | 1 Sep 2010 16:05 | RANK 3
FACT_ID 6 | WI_2 | 1 Sep 2010 16:40 | RANK 4
FACT_ID 1 | WI_3 | 1 Sep 2010 15:17 | RANK 5
FACT_ID 4 | WI_3 | 1 Sep 2010 16:25 | RANK 6

Since I cannot do this without reading the data into some sort of variable, please can I have some information on how to do this? Also, how do I then take replace the data set with the sorted information, making sure that the rank column is added? I have found many tutorials, but I cannot make enough sense of them to be able to do what I want.

Thank you in advance.

Best regards,

Andrew

Edit

I tried adding the following code to the beforeOpen script of my data set:

queryString = this.queryText;
queryString = queryString + " ORDER BY WI_ID ASC";
this.queryText = queryString;

Unfortunately it does not work. Is there anything else I can do?

2 answers



permanent link
Kirk Woods (3156) | answered Sep 08 '10, 12:44 a.m.
Sorting is accomplished by using the sort tab of the Properties Editor of the table.

Good Morning,

I am trying to create a BIRT data set that is sorted (1) by WI_ID and (2) by TIME_ID.TIMESTAMP so as to have a list of all work items ordered by ID and timestamp, with a computed column ranking them. For example i would like to have the following result:

FACT_ID 2 | WI_1 | 1 Sep 2010 16:03 | RANK 1
FACT_ID 5 | WI_1 | 1 Sep 2010 16:30 | RANK 2
FACT_ID 3 | WI_2 | 1 Sep 2010 16:05 | RANK 3
FACT_ID 6 | WI_2 | 1 Sep 2010 16:40 | RANK 4
FACT_ID 1 | WI_3 | 1 Sep 2010 15:17 | RANK 5
FACT_ID 4 | WI_3 | 1 Sep 2010 16:25 | RANK 6

Since I cannot do this without reading the data into some sort of variable, please can I have some information on how to do this? Also, how do I then take replace the data set with the sorted information, making sure that the rank column is added? I have found many tutorials, but I cannot make enough sense of them to be able to do what I want.

Thank you in advance.

Best regards,

Andrew

Edit

I tried adding the following code to the beforeOpen script of my data set:

queryString = this.queryText;
queryString = queryString + " ORDER BY WI_ID ASC";
this.queryText = queryString;

Unfortunately it does not work. Is there anything else I can do?

permanent link
Andrew Trobec (48712129137) | answered Sep 08 '10, 2:04 a.m.
Hello Kirk,

Thank you for your reply.

I am aware of the possibility of sorting within the layout, but the data sets that I am trying to sort represent the first step of the final data set (which will be a join between the two) that I want to use in the layout.

What I want to do is create two data sets that are in the above mentioned form (with rank), then create a third data set which would join the first two using the rank as key that I can then use in the report layout. In order for it to work, though, I need to be able to sort the first two data sets using some sort of script.

Regards,

Andrew

Sorting is accomplished by using the sort tab of the Properties Editor of the table.

Your answer


Register or to post your answer.