How to create a hierarchical tree

Question

I am using iReport 4.2 and I am trying to do the following

from database I get data in BOM like format (or tree format) and now I need to shift fields right according to the level. I am doing a hierarchical tree

for example

A.
   A.a
   A.b
B.
   B.a
      B.a.a

Any suggestions. I did some research and I don't even know what to look for :)

Answer 1

I wrote the following select where I changed the ids with blanks multiplied with the level

select ocs_season_questionnaire_id ,id, code, title, description, 0, '1' from ocs_season_report_unit where supervisor_id is null and ocs_season_questionnaire_id = 5

union all
select C.ocs_season_questionnaire_id, C.id, C.code, C.title, C.description, depth+1, xid||'.'||C.id
from sumthis P
inner join ocs_season_report_unit C on P.id = C.supervisor_id
)
 select id, lpad('', depth*2)||title as title, description from sumthis  where ocs_season_questionnaire_id = $P{qu_id}

It is not the best solution so if anybody knows a better one please respond. thank you