Jump to content
Changes to the Jaspersoft community edition download ×

Crosstab: reordering row groups in numeric sort order instead of string order


valeskyt

Recommended Posts

Environment is Jaspersoft Studio Community Edition version 6.9.0

User has a crosstab report. The row values are integers from 1 to 12, but they are stored as Strings in the database. The issue is that, in String sort order, the rows are sorting as "1 10 11  2 3 4". They'd like them to sort in integer sort order: "1 2 3 ... 9 10 11 12". 

I'm trying to figure out how to make this happen. I think it's possible, but I can't find any documentation about crosstabs that goes this deep into the detail. 

I think the right answer will have something to do with the bucket grouping for the row group, but nothing I've tried so far has done the trick.

Can anyone post an example of how to do this?

Thanks,

Tom

Link to comment
Share on other sites

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

What I'll probably end up doing is to edit the SQL to add a column where I cast the value to an integer, then sort on that in the crosstab instead of the "real" column. But I'm hoping there's a way to fix this at the Jaspersoft level.

Link to comment
Share on other sites

I ended up wrapping the query in a CTE and adding a column with the value in question cast as an integer. I had to modify the validation string on the server to permit the CTE syntax, but it works fine, and I didn't need to rewrite the query or fiddle with the database schema. 

For a simpler query, I'd have simply added the extra column to the select statement.

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...