Need help in jxls

[Philip Zac]

Hello folks,

I am using OpenReports and I have to create new report using excelSheet. I have put this code in it but it shows error. How do I fix it.

   <jx:forEach items="${rm.exec('select 
	c.first_name, 
	c.last_name, 
	rn.room_name, 
	rs.check_in_date , 
	rs.check_out_date, 
	(DATEDIFF(rs.check_out_date,rs.check_in_date)) periodOfStay, 
        (rs.adult_pax + rs.child_pax) pax,
	sum(rd.amount) as amount,
	sum(pta.element) as tax, 
	pta.mapkey,
	rs.reservation_id, 
	rs.id

from 
	posting_tax_amount as pta, 
	posting as p, 
	rate_details as rd, 
	room_night as rn, 
	room_stay as rs, 
	customer as c, 
	reservation as r 
where 
	pta.mapkey LIKE ('Luxury Tax%') and 
	pta.posting_id=p.posting_id and 
	p.posting_id=rd.posting_id and 
	rd.room_night_id = rn.id and 
	rn.reservation_status = 'Re-Confirmed' and 
	rn.deleted_date IS NULL and 
	rn.room_stay_id = rs.id and 
	rs.check_in_date >= ' “ +  $P{startDate1}+ “ ' and 
	rs.check_in_date <= ' “ + $P{endDate1} + “ ' and 
	rs.check_out_date between ' “ +  $P{startDate1} + “ '  and DATE_ADD( ' “ + $P{endDate1} + “ ', INTERVAL 86399 SECOND) and 
	rs.reservation_id = r.id and 
	r.customer_id = c.id 
group by rs.id;
')}" var="log">

 

Error message.

  

    * Cant parse an expression rm.exec(select c.first_name, c.last_name, rn.room_name, rs.check_in_date ,
 rs.check_out_date, (DATEDIFF(rs.check_out_date,rs.check_in_date)) periodOfStay, (rs.adult_pax + rs.child_pax) pax, 
sum(rd.amount) as amount, sum(pta.element) as tax, pta.mapkey, rs.reservation_id, rs.id from posting_tax_amount as pta, 
posting as p, rate_details as rd, room_night as rn, room_stay as rs, customer as c, 
reservation as r where pta.mapkey LIKE (Luxury Tax%) and pta.posting_id=p.posting_id and p.posting_id=rd.posting_id 
and rd.room_night_id = rn.id and rn.reservation_status = Re-Confirmed and rn.deleted_date IS NULL and rn.room_stay_id = rs.id 
and rs.check_in_date >= ? + startDate1+ ? and rs.check_in_date <= ? +endDate1 + ? and rs.check_out_date between ? + startDate1 + ? 
and DATE_ADD( ? + endDate1 + ? , INTERVAL 86399 SECOND) and rs.reservation_id = r.id and r.customer_id = c.id group by rs.id; )  

Thanks in advance.

Philip

[Martijn Verburg]

Have you tried to run the SQL manually in the DB?