Merge rows when repeated values in table

[anji.viper]

Hello..

I am trying to merge rows that have repeated values..  When I uncheck "Print repeated values" I get the repeated value once, but the empty cells are still displayed, which make the table less readable. It is also not a good design to leave the repeated values in the table.

I have tried searching for the solution a LOT but to no avail. I even tried by using a crosstab but did not manage to do it properly.

Any help would be greatly appreciated.. Thanks!

-Anji-

[anji.viper]

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.

Category	answer	total
x	yes	14
	no	2
y	yes	3
	no	4
a	yes	12
	no	3
b	no	5

It should be like this instead:

Category	answer	total
x	yes	14
	no	2
y	yes	3
	no	4
a	yes	12
	no	3
b	no	5

In 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-