Crosstab Data Pre Sorted returns null values

0

I'm trying to create a report that's taking an Office and Survey as its parameters. I have to create a table, almost 100% sure it has to be a crosstab, that will display the input office's departments and their answers to the survey's questions - I don't know the number of questions or the departments. I created a proper crosstab thats displaying both question names (rows) and departments (columns) along with their respective answers (measure), but rows are sorted alphabetically. I've tried the "data pre sorted" checkbox and it sorted the rows in the correct order, but the answers are returning null values since then. How can I fix this? Is there way to sort the rows in a different order than just using "Data Pre Sorted" checkbox?

 

PROBLEM SOLVED:

 I managed to turn off sorting for rows only and I got both the proper row order and matching measures. I worked it out this way:

double clicked on the crosstab in Outline -> then, in the crosstab outline i expanded crosstab -> then expanded row groups -> clicked on the row group -> Properties -> Cell tab -> in the Bucket section, I switched "Order" to "None"

 

jack w.'s picture
32
Joined: Nov 9 2017 - 2:41am
Last seen: 1 year 8 months ago

1 Answer:

1

"Data PreSorted" means that Jasper can assume that you have sorted the data already in your database. (This allows Jasper to omit some processing steps & optimizes performance.)  So it's going through assuming that Question 1 comes first, and once it sees Question 2, it ignores all Question 1s after that. Kind of the opposite of what you want.

You can sort in a couple of  ways:

In the query: Select * from orders where shipcountry = $P{country} order by shipcity

In the dataset: Select the root node of the report in Outline view and open the Dataset and Query dialog (or open the subdataset if you are using one). Use the Sorting tab to pick a field to sort by.

elizam's picture
4016
Joined: Mar 5 2012 - 9:19am
Last seen: 2 weeks 3 hours ago
Feedback
randomness