anji.viper Posted May 16, 2017 Share Posted May 16, 2017 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- Link to comment Share on other sites More sharing options...
Solution anji.viper Posted May 17, 2017 Author Solution Share Posted May 17, 2017 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:Categoryanswertotalxyes14no2yyes3no4ayes12no3bno5In 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- 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