nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Column Sorting With Joint Data Set

dehuaste

All,
 
Using BIRT Runtime Engine 4.3 and attempting to implement column sorting like in <a data-ipb='nomediaparse' href='http://www.ibm.com/developerworks/data/library/techarticle/dm-0708tomlyn/'>this example</a> (Option #1). This is working well for simple reports that are based off of one data set. 
 
The problem I'm facing is on reports with tables binding to joint data sets. The other aspect is that we're calling some functions in the query and creating column aliases. Specifically, the issue I'm having is that the column name I'm passing over the hyperlink is not having an impact on the sort direction of my column. 
 
So given a table that is bound to a joint data set, trying to pass something with the hyperlink like <span style="color:rgb(0,0,0);font-family:'Andale Mono', 'Lucida Console', Monaco, Liberation, fixed, monospace;font-size:14px;font-style:normal;font-weight:normal;text-align:left;background-color:rgb(255,255,255);">'targetURL + "VIEWSCHEMA.MY_TABLE.MY_COL";' throws the following exception:
<pre class="_prettyXprint">
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Column "schema.table.column" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
;
java.sql.SQLException: Column "schema.table.column" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
at org.eclipse.birt.report.engine.executor.ExtendedGenerateExecutor.execute(ExtendedGenerateExecutor.java:70)
</pre>
If I alter the above to be like <span style="color:rgb(0,0,0);font-family:'Andale Mono', 'Lucida Console', Monaco, Liberation, fixed, monospace;font-size:14px;font-style:normal;font-weight:normal;text-align:left;background-color:rgb(255,255,255);">'targetURL + "MY_COL";' there's no exception but the report is only reloaded with no sorting. I've tried "MY_COL" as both the original column name and the alias name to no avail.
 
 
What is the appropriate value that I should enter to achieve sorting in joint data sets?
 
Thanks!

Find more posts tagged with

Comments

mwilliams

Could you attach an example that uses the sample database that shows this issue? Thanks.

dehuaste

Michael,
 
Sorry, got pulled in another direction and haven't had time to create a test report. 
 
In doing some research I found another post that mentioned sort order for joint data sets is predefined by whichever field joins the two tables. To get around this, I've added a sort expression to the table. For the Sort Key, I wrote an expression to evaluate a parameter and select the appropriate column by which to sort
 
This is working great, the only limitation here is that there isn't the same programmatic control over sort direction that there is for the sort key.
 
How can we control the sort direction dynamically?
 
EDIT: This is how to do sorting dynamically

<a data-ipb='nomediaparse' href='http://wiki.eclipse.org/Example_-_Sort_Table_based_on_parameter_(BIRT)'>http://wiki.eclipse.org/Example_-_Sort_Table_based_on_parameter_(BIRT)</a>

mwilliams

Thanks for the update!