Grouping by relating fields of different result sets to create a relationship tree

[benjamin.segall]

Hello, 

I am new at jaspersoft and i require some help on this complex query. So, the query i pasted below is what i have, and it prints a serial for a certain role and its privilages. It also brings back a base role serial and its description. I need to group these result sets based on the relation between the base serial and the serial of another result set. To be more clear: 

 

                I would like to see the full list of privileges by roll for example if I choose IT Manager

 

1. IT Manager (base role :IT Developer)
   1. List of all Privileges with Grant or Deny
2. IT Developer (Base Role: IT Technician)
   1. Lit of all Privileges with Grant or Deny
3. IT Technician (Base Role: Null)
   1. List of all privileges with Grant or Deny

 

 

                   Or if I choose IT Developer I would only receive

 

 

IT Developer (Base Role: IT Technician)

                List of all Privileges with Grant or Deny

IT Technician (Base Role: Null)

                List of all privileges with Grant or Deny

 

Here is my query:

 

select b.SERIAL As Serial, b.DESCRIPTION As Role, d.DESCRIPTION As Privileges, c.ACCESS As Access, a.DESCRIPTION As Base, b.BASE_ROLE_SERIAL
from CORE.ROLE a, CORE.ROLE b
inner join  CORE.PRIVILEGE c on b.SERIAL = c.PARENT_SERIAL
inner join  CORE.SECURITY_EVENT d on c.SECURITY_EVENT_SERIAL = d.SERIAL 
where a.SERIAL = b.BASE_ROLE_SERIAL and $X{IN, b.DESCRIPTION, SelectRole} 
union all
select a.SERIAL As Serial, a.DESCRIPTION As Role, d.DESCRIPTION As Privileges, c.ACCESS As Access, a.DESCRIPTION As Base, a.BASE_ROLE_SERIAL
from CORE.ROLE a
inner join  CORE.PRIVILEGE c on a.SERIAL = c.PARENT_SERIAL
inner join  CORE.SECURITY_EVENT d on c.SECURITY_EVENT_SERIAL = d.SERIAL 
where a.BASE_ROLE_SERIAL is null and $X{IN, a.DESCRIPTION, SelectRole}
order by Role, Privileges

 

Any help would be greatly appreciated 

 

Benjamin Segall