Jasper report data summary

Question

I am trying to create a report to display a summary of the values of the columns for each row. A basic analogy would an inventory listing. Say I have about 15 locations like 2A, 2B, 2C, 3A, 3B, 3C etc. Each location has a variety of items and the items each have a specific set of common descriptions, i.e. a rating of 1-9, Boolean Y or N, another Boolean Y or N. It looks something like this:

2A   4       Y       N
2A   5       Y       Y
2A   5       N       Y
2A   6       N       N      
...
2B   4       N       Y  
2B   4       Y       Y      
...etc.

What I would like to produce is a list of locations and summary counts of each attribute:

Location    1 2 3 4 5 6 7 8 9      Y  N        Y N      TOTAL
2A                1 2 1            2  2        2 2        4
2B                2                1  1        2          2
...
___________________________________________________________
Totals            3 2 1            3  3        4 2        6

The query returns fields:

location_cd String  
desc_cd Int 
y_n_1 String 
y_n_2 String

I have tried grouping by location but cannot get the summaries to work. I tried putting it in a table but that would only take the original query. I tried to create datasets for each unit and create variables in each one for each of the criteria but that hasn't worked yet either. But maybe I am way off track and crosstabs would work better? I tried that and got a total mess the first time. Maybe a bunch of subreports?

Can someone point me in the correct direction please? It seemed easy when I started out but now I am getting nowhere. I can get the report to print out the raw data but all I need are totals for each column broken down out by location.

Answer 1

The answer required me to group by location_cd and create variables to sum the occurances of the desc_cd values(1-9)

$F{desc_cd}==1?1:0

etc. for 2-9. Reset type was by group. Same for the Y/N columns.

$F{y_n_1}.equals("Y")?1:0   

Instead of putting them in the details row I put the text fields in the group footer, put some column titles in the column header and the rest in the page header.

I still need to find a way to sub-group some of my groups but that is for another question.