Sort by column in a group

Hi, 
 
I've been working on adding sorting to my report where a user would be able to click on the column (which is a hyperlink) and the report would reload sorted by that column. I got that to work fine in a test report that just had a straight table, but when I went to do it in the production report, it failed miserably. I believe the reason it failed is due to the fact that I am using groups. I would like to make it so that if you sort by a column, it will sort in ASC or DESC order in that group by that column. Any ideas? 
 
The code I've been following for the sorting is illustrated in this webpage: 
<a class='bbc_url' href='http://www.ibm.com/developerworks/data/library/techarticle/dm-0708tomlyn/index.html'>Hints and tips for using the Business Intelligence and Reporting Tools</a> 
 
I am using option 1. Like I said, it works fine until I have to add everything in groups. Also, parameters may be an issue as well since they don't seem to repass the parameters of the report, but even still, the sorting is not working at all. 
 
Thanks for the help! 
Amanda

Find more posts tagged with

Comments

mwilliams

Hi Amanda,

Can you attach a sample report created with the sample database that I can run and modify? Thanks.

JasonW

Amanda,

There are several ways you can do this. Attached is an example that does this with parameters.

You can also change or add sorting using the DE API from script.
importPackage(Packages.org.eclipse.birt.report.model.api);
importPackage(Packages.org.eclipse.birt.report.model.api.elements);
importPackage(Packages.java.util);

//Make sure to name your table
delm = reportContext.getDesignHandle().findElement("mytable");

///Get first goup
tgh = delm.getGroups().get(0);
tgh.setSortDirection("desc");
var iter =tgh.sortsIterator();
//Bump twice for two sort keys
var sorthandle = iter.next( );
sorthandle = iter.next( );

sorthandle.setKey("row["ORDERLINENUMBER"]");

DreamCoder

Thanks to all who have posted! Mike, attached is what I've been playing with (without the grouping), and Jason, I'm played with yours but it doesn't track ASC and DESC. You've given me lots to play with tho and I will continue working through it.

Thanks,
Amanda

DreamCoder

Hmm...it appears that I resolved my own issue when I finally figured out a way to work in report parameters. Now that I have solved that, the sorting appears to work fine. See the attached document for what seems to be the final resolution. I will try it on a much more complex production report and see if that's it.

DreamCoder

Okay, so that definitely fixed the issue. I am now able to click on a hyperlink and sort ascending and descending. The only issue I have is each time you click the hyperlink to sort, the report is re-run with the new sorting parameters.

Is there a way to do this without having to rerun the report?

Jasons example may get me there...I'll have to dig deeper into it. I don't like using the sorting at the group level tho because you have to specify in the code whether or not its ascending or descending, whereas I would like that option to be more dynamic.

Anyone have any ideas?

Thanks,
Amanda

JasonW

Amanda,

This line
tgh.setSortDirection("desc");

Could always be tied to a report parameter, but using the open source viewer will still require a rerun of the report to make this change.

Jason

DreamCoder

<blockquote class='ipsBlockquote' data-author="JasonW">Amanda, 
 
This line 
tgh.setSortDirection("desc"); 
 
Could always be tied to a report parameter, but using the open source viewer will still require a rerun of the report to make this change. 
 
Jason</blockquote>
 
Jason, when you say it will rerun the report does that mean it re-hits the database? Or just refreshes the look of the report? Is there is a way through BIRT that I can cache the results of the query to make it faster, and the report just changes the sort order? 
 
Thanks, 
Amanda

JasonW

That method will re run the datasets. The open source viewer does not support resorting without rerunning the report. You can sort the currently displayed page by using some client side javascript. Take a look at the attached report, but it will only do the current page.

Jason

DreamCoder

That looks like it could be cool to play with and see if I can get it working with mine. Where did you put the custom function sortTable()?

JasonW

It is in the text element above the table.

Jason

Mat

Hi, Apologies for using this old thread, but since it has the relevant context i am using it. I am currently using the drill-through with parameter plus sort expression added in beforeFactory to support sorting on columns. I understand this entails rerunning the report every time the user wants to sort. My question is, given the above approach is there a way to specify when the report reruns to pick up the data from the rptdocument that gets generated rather than go all the way to the data source. That way though the request comes to the server the overhead of going back to the data source & recomputing everything is avoided. Is there anyway this can be achieved (tweaks or otherwise)? ThanksMat