hi @ll, how do you create a hierarchy for a GL accnt with the following ;1 i have a query for branch as follows

SELECT ounder.name
FROM m_office mo
LEFT JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, '%') AND ounder.hierarchy
LIKE CONCAT(${userhierarchy}, '%')

2,and the GL query is as below






select
details.edate entry_date,
details.acid1,
details.report_header,
details.reportid,
details.account_name,
details.debit_amount,
details.credit_amount,
details.aftertxn,
details.description,
opb.openingbalance,
branch.branchname,
details.transactionid
from
(
select je.account_id acid1,
# concat(gl.gl_code,"-",gl.name) as report_header,
concat(gl.gl_code,"-",gl.name) as report_header,
gl.gl_code as reportid,
je2.entry_date edate,
concat(gl2.gl_code,"-",gl2.name) as account_name,
if (je2.type_enum=1, je2.amount, 0) as debit_amount,
if (je2.type_enum=2, je2.amount, 0) as credit_amount,
je2.id,
je2.office_id,
je2.transaction_id,
je2.type_enum,
je.office_running_balance as aftertxn,
je2.description as description,
je2.transaction_id as transactionid


from acc_gl_journal_entry je
left join acc_gl_account gl on gl.id = je.account_id
left join acc_gl_journal_entry je2
on je2.transaction_id = je.transaction_id and je2.account_id <>${account}
left join acc_gl_account gl2 on gl2.id = je2.account_id
where je.account_id =${account}
and je.office_id = ${branch}
and je.reversed = 0
and je.entry_date between ${fromDate} and ${toDate}
order by je.entry_date, je.id) details
left join
(select
je.account_id acid2,
je.office_running_balance as openingbalance


from acc_gl_journal_entry je
where je.account_id =${account}
and je.office_id = ${branch}
and je.reversed = 0
and je.entry_date <= ${fromDate}
and id =
(select max(id) from acc_gl_journal_entry je
where je.account_id =${account}
and je.office_id = ${branch}
and je.reversed = 0
and je.entry_date <= ${fromDate}
) )opb
on opb.acid2=details.acid1
left join
(
select name branchname
from m_office mo


where
mo.id=${branch}
)branch
on details.office_id=${branch}
order by details.edate,details.reportid

May you kindly assist me to edit the detail query to make it print using hierarchy, thank you for your assistance in advance