Sorting String data

Hello,

Could someone tell me how to sort string data otherwise than alphabetically in a table?

example:

B
C
A

Thank you very much .

Find more posts tagged with

Comments

mwilliams

You can create a computed column with a numeric value for the order you'd like them to appear in and sort based on your customSort field. I'm guessing you have some sort of key that you can check what order it should be in or you can write a script that will have a check for all values to assign a sort key value. Let me know.

Viclamb

Thanks for your reply.

The way I would like to do that is without writing any script or expression.

With the computed column, I guess I have no solution than writing an expression to test if the value of my state is "A", "B" or "C" and returning the corresponding value (like "3", "1", "2").

Is there an easy way for end users (who are not developpers) to sort their data like that ?

mwilliams

Are you creating a report for end users to use, so you'd be ok with some script on your side? Or are you considering yourself an end user, too. How would you like the end user to specify the order? Let me know that and your BIRT version and I'll see what I can do with an example with the simplest possible solution.

Viclamb

I am preparing reports (with datasource and dataset) programmatically using the java api for end users to make them able to organize their data as they wish in the RCP Designer. 
So, yes, I am developper and I would be ok with some script but what I want to do it's finding the easiest way for the end user to sort a field with only few value possible. 
 
Here is an example: 
This is how the data appear in a table : 
Order num

Order state 
01

To be billed 
02

Billed 
03

Billed 
04

To be billed 
05

Canceled 
06

Billed 
07

Canceled 
08

To be billed 
09

To be billed 
10

Billed 
 
And this is how the end user should be able to sort it : 
Order num

Order state 
01

To be billed 
04

To be billed 
08

To be billed 
09

To be billed 
02

Billed 
03

Billed 
06

Billed 
10

Billed 
05

Canceled 
07

Canceled 
 
This is not the alphabetical way so that means that it's the end user who choose which one is the first, the second etc ... in any simple way, which is my question : How could they be able to do that ? 
BIRT versions are 3.7.2 and 4.2.0. 
 
Thank you for your time.

mwilliams

Well, the easiest way to do it would probably be to create a static list box parameter like the following:

Choose a Sort Order:
1 - Billed, Canceled, To be billed
2 - Billed, To be billed, Canceled
3 - Canceled, Billed, To be billed
4 - Canceled, To be billed, Billed
5 - To be billed, Billed, Canceled
6 - To be billed, Canceled, Billed

And then use their parameter selection in your computed column to assign the correct value for the sort.

Then, all the end user ever has to do is select an order. No coding whatsoever.

Viclamb

Ok, but we agree that on this way, in my computed column I have to write an expression code to test the choice of the user through the parameter, and then to assign the value. Or maybe I am missing something...

It might be something like that :
if(row["state"]=="To be billed"){
1}
else if(row["state"] == "Billed"){
2}
else{
3}

In my application, I will generate the report with datasource/dataset, and after, they will have to deal with the sorting in RCP Designer by their own.
My app will generate hundreds of reports, and it is not possible that a developper or a birt expert have to intervene in each case. So my question is : is the computed column expression code above the only way for the end user to sort a String data precising the order he want ?

mwilliams

This would need to be done in a computed column, in the dataSet, yes. So, if like you say above, you create the dataSource and dataSet, then the computed column will already be handled. If from there, someone else has to actually create a table and set up the sorting, all they'll have to do is go to the sorting tab for the table and choose to sort by the new sorting column. I don't know what your application does and what the non-developer end user has to do after they receive a report from you. Maybe, if you explain exactly what your application returns, and what the end user must do with the design, I can be more specific. There are several ways you could let the end user enter the information for the sort, but all of them will need to have a sort key created in a computed column, if they're going to be sorted out of order. If you're not creating the report in its entirety, the end user will have to do some work. Depending on how much you're doing for them, depends on how much they'll have left to do. If you're sending out completed reports, and they're just running them with the designer, they'll have to do absolutely nothing except pick a sort order. Please let me know and I'll gladly give a detailed explanation of what will need to be done by you and the end user. If you'd like a different method for the end user to be able to enter the order, also let me know that.

Viclamb

Here is what the app does : 
- Extracting data from the database in a XML File 
- Parsing the XML file to retrieve the data 
- Creating an empty rptdesign file 
- Building DataSource and DataSet 
- Adding a table with non sorted data from the DataSet 
- Saving the rptdesign file in the workspace folder of the RCP Designer 
 
 
Here is what the end user must do : 
- Launch RCP Designer 
- Open the generated rptdesign from the shared resource folder (workspace) 
- Sort the data by the state field and by the order he wants 
 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="107381" data-time="1342795993" data-date="20 July 2012 - 07:53 AM">
This would need to be done in a computed column, in the dataSet, yes. So, if like you say above, you create the dataSource and dataSet, then the computed column will already be handled. If from there, someone else has to actually create a table and set up the sorting, all they'll have to do is go to the sorting tab for the table and choose to sort by the new sorting column.</blockquote>
I cannot handle the computed column programmatically because I don't even know if the end user will want to do this kind of sort and on which field he would be interested to do it. 
 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="107381" data-time="1342795993" data-date="20 July 2012 - 07:53 AM">
Depending on how much you're doing for them, depends on how much they'll have left to do. If you're sending out completed reports, and they're just running them with the designer, they'll have to do absolutely nothing except pick a sort order. Please let me know and I'll gladly give a detailed explanation of what will need to be done by you and the end user. </blockquote>
Yes the reports are completed but not sorted. And I can't create a parameter for every field and every order possible because it would be nonsense. 
 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="107381" data-time="1342795993" data-date="20 July 2012 - 07:53 AM">
There are several ways you could let the end user enter the information for the sort, but all of them will need to have a sort key created in a computed column, if they're going to be sorted out of order. If you're not creating the report in its entirety, the end user will have to do some work. </blockquote>
I completely understand that, and this is exactly what I am asking. What is this work ? How the end user could be able to do that ? 
 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="107381" data-time="1342795993" data-date="20 July 2012 - 07:53 AM">
If you'd like a different method for the end user to be able to enter the order, also let me know that.

</blockquote>
I would like you give me all methods for the end user to be able to enter the order, please

I hope to be more precise in my explanations this time.

mwilliams

One more quick question. Will the "state" field always have the same 3 values? Or will there be variance depending on the end user? Let me know. I've got something I'm gonna try and send to you when I have it working, but need to know this.

Viclamb

It is depending on the report they are making. In my Java app, they are choosing the fields they are extracting from the database. But mostly, that will be on kind of state fields, with only few values possible (like 4 or 5).
So yes, there will be variance depending on the end user.

EDIT :
(By the way, it is possible to open a rptdesign file directly in BIRT using a command option. Just to avoid going in File>Open File ... Thank you)

mwilliams

Take a look at this devShare post for an option that you might be able to make work. It dynamically fills a form with input text boxes for each value from the status field. The end user can then input 1-n in the boxes, in the order they would like. Once they click on the Sort button, the report is re-ran with the new sort order as a parameter, which creates a computed column and sorts the report accordingly. If no values are entered, the standard sort order is followed. Everything is hidden, unless the user clicks on the html button, so it might be something that will work for clients who don't necessarily want custom sorting. If they really don't want it. They can delete/hide the text control from the report and it will no longer be there.

http://www.birt-exchange.org/org/devshare/designing-birt-reports/1521-user-specified-custom-sort-order/

Hope this helps.

Viclamb

Thank you very much Michael for your help, you have finally found a solution to my sorting issue. 
 
 
What about my other question : 
 
Is it possible to open a rptdesign file in BIRT using a command option ? 
(Just to avoid going in File>Open File ... etc) 
I am looking for something like that : 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>C:\Users\vl\Path\to\Birt\BIRT.exe -report C:\reports\report.rptdesign
</pre>
 
Is there a way to do that ?

mwilliams

If you choose BIRT/Eclipse as the default program that should open a file of type .rptdesign, you should just be able to type in C:\reports\report.rptdesign into the command line and it will automatically open it in the designer.

Viclamb

I tried this before and the file association with BIRT.exe does not work. I even tried to drag and drop a rptdesign file on the BIRT.exe file and this did not work either.

mwilliams

I used eclipse.exe when I tried it.

Viclamb

I can't do it. It is not working I am sorry.

I'm looking for a way to open reports programmatically in Birt RCP Designer or Eclipse.