Well, I have managed to do it.. Since it is a fairly common problem, let me just leave my solution here. Crosstabs! This is what I wanted to do. For each Category, there may or may not be an answer yes or no and a total for each yes/no (if any). What I wanted to do is merge the cells for Category so that we don't see blank cells like the table below. Categoryanswertotalxyes14 no2yyes3 no4ayes12 no3bno5It should be like this instead: Category answertotalxyes14no2yyes3no4ayes12no3bno5In order to achieve this in Jaspersoft Studio, I used the Crosstab element. The SQL query I used needed to have a global field returned that i can use as the column. It can be anything that applies to every row of the table. In my case I returned the field for the Organisation name of the data i'm pulling. So when I created the Crosstab, i used the Organisation field as Columns, and Category and Answer as Rows, and for Measures I used a distinct count of the id of the category. The crosstab will be created with a lot of unneeded information, such as total count of organisation and answer.. which i don't need. So in order to remove them without messing up the table, I deleted the unneeded text boxes and set the cell height for the uneeded rows to 0 and the cell width for the unneeded columns to 0. And there you have it :) Since we're grouping it globally by organisation and then breaking it down by category and answer, it will do the sequential grouping like SQL does, but in this case it merges the results that are the same. You can use properties for the text boxes such as "stretch with overflow" to allow text wrapping. Hope this was of any help to people who are stuck on this as I was... -Anji-
