valeskyt Posted August 12, 2019 Share Posted August 12, 2019 Environment is Jaspersoft Studio Community Edition version 6.9.0User 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 More sharing options...
valeskyt Posted August 12, 2019 Author Share Posted August 12, 2019 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 More sharing options...
r.agurto Posted August 20, 2019 Share Posted August 20, 2019 In the database the type should represent the data, if you store numbers, why set it to string?In any case, on jasper level, you could cast to integer the strings your getting from your database. Link to comment Share on other sites More sharing options...
valeskyt Posted August 20, 2019 Author Share Posted August 20, 2019 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now