| Partial solution for dynamic table sorting [message #129865] |
Tue, 14 February 2006 02:27 |
Eclipse User |
|
|
|
Originally posted by: tnsilver.ctxm.com
Hi - as far as I can tell this is a painful issue in BIRT 2.0.0. Many
report
designers want to provide their users with the option to sort a table by
clicking the column header. I managed to do so in more than one way. The
bad
news is that the sorting direction (e.g. 'ascending','descending) is very
hard, to say the least, to implement - if not impossible. The 'sort'
properties of the SortKey
(org.eclipse.birt.report.model.api.elements.structures.SortK ey) and the
desired 'direction' property are completely inaccessible via scripting at
run time (well I failed to use them - getDirection() and setDirection()
did
not work for me).
My solution is based on a hidden String report parameter which contains
the
sort key (e.g. the column name that would usually go in the ORDER BY
clause
of an SQL query). A click on a column header which is a hyperlink to the
same report - would just call the report again (regenerate it) with the
same
parameters but with the special report parameter set to a desired column
name.
1) Create a new hidden String report parameter - say 'order_by' and give
it
a default value which is one of your query columns - say 'customer_id'.
2) In the properties for the table that displays the data in your report,
choose the 'sorting' tab. Add a new sorting expression as follows:
row[params["order_by"]] and assign a Sort Direction which is either
Ascending or Descending. Your table is now sorted by the default value of
the order_by report variable which is the same as assigning the key
row["customer_id"] as the sot key.
3) Now we have to take care as to assign the right column name to the
order_by report parameter every time we click the respective column
header.
This is done by creating a hyperlink out of each label or text in the
table
header. You would want to link a Drill-Through hyperlink to the same
report.
If your report is called 'Customers.rptdesign' - simply enter this name in
the Report box. Now start assigning the parameters to pass to the link. In
the new report that will open - you wouldn't want to change anything but
the
sort order. So - assign each report parameter to the respective value that
you have in the params variable (e.g. Parameters = customer_id Values =
params["customer_id"]). Make sure to include all your report parameters.
Finally - for the order_by parameter assign the literal value of the
column
name you want to sort (e.g. Parameters = order_by Values =
"customer_company").
4) Notice when you create a hyperlink out of the column header the style
change to the default href style. This is solvable by using styles to
re-create the look of your column headers or just assign the border color
to
match the background thus making the borders vanish.
5) Later you can optimize the code a bit to dynamically set the order_by
value to the name of the item or some idea in that area. You may use a
global array of column names or any solution that would allow you to copy
and paste column headers. It is suggested as the process of defining
hyperlinks is somewhat cumbersome and annoying.
To solve the sorting order (e.g. ASC or DESC) you will have to work
harder.
I could not find a nice solution. One possibility is to create two tables
on
the same report. On table is sorted Ascending - the other Descending. You
can just copy and paste the first table and modify the properties. Then,
use
the visibility property to make one table disappear when your new report
parameter value 'order_dir' is 'ASC' and another to disappear when its
'DESC'. You will find some annoying errors with the sort key but
eventually
you will find the way. Use the hyperlink to pass the right order_dir value
too. You can now even place to images of arrow up and arrow down near each
column header and hyperlink them instead of the column header label.
These solutions, off course, would regenerate the report - meaning your
JDBC
driver will run the query again and will put some overhead on the DB. Not
very good - be aware. A better way would be to dynamically generate the
SQL
query in the data set with the order by and sort direction - using some
scripting in its available methods. I am working on that now.
|
|
|
Powered by
FUDForum. Page generated in 0.02520 seconds
Home