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

[valeskyt]

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

[valeskyt]

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.

[r.agurto]

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.

[valeskyt]

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.