0

I am using latest version of Jasper soft studio to design Jasper reports. I want to pass order by clause to jasper report based on certain criteria from Java. Can you please guide as how to append parameter(containing order by clause from Java) in the report query?

Query

with tmp as (      
select d.gl_code, nvl(sum(d.debit),0)- nvl(sum(d.credit),0) as simple   
          from gl_forms g,gl_voucher_detail d,financial_year f
          where g.voucher_no = d.voucher_no
      and f.fid = $P{P_FID}
        and g.voucher_date >= to_date('01-07-'+f.start_year,'dd-MM-yyyy')      and g.voucher_date < $P{P_FROM}
      and g.post=1
      group by d.gl_code)
select g.voucher_no,
       g.voucher_date,
       g.voucher_type,
       g.remarks remarks,
       d.sr_no,
       c.account_name,
       d.debit,
       d.credit,
       d.narration narration,
       c.account_code, 
       c.account_name || ' - ' || c.account_code Account,
       (select name from company) name,
       nvl((o.debit-o.credit),0) + nvl(tmp.simple,0) opening
from gl_voucher_detail d
   join gl_forms g 
      on g.voucher_no = d.voucher_no 
      and g.post=1 
      and g.voucher_date between $P{P_FROM} and $P{P_TO}
   left outer join opening_balances o
      on d.gl_code = o.account_code
      and o.fid = $P{P_FID}
   right outer join chart_of_account c 
      on c.account_code = d.gl_code 
   left outer join tmp
      on c.account_code = tmp.gl_code
where c.account_level=4
and c.active=1
and c.account_code = CASEWHEN($P{P_ACCOUNT}='%',c.account_code,$P{P_ACCOUNT})

Order by clause shall be one among these below two based on front fields selection:

  1. order by c.account_name, g.voucher_date,g.voucher_no,d.sr_no
  2. order by c.debit+c.credit desc
Moazzam
  • 397
  • 1
  • 8
  • 23

0 Answers0