Display 5 pages on jasper

[zain.nabi]

How do I print 5 pages. It renders sometimes a full month or even 4 days when i try the following dates and disregards the actual date range. The query is linked to Jasper and passes a date range as parameters

 

Actual Query

select e.employee_id,
       e.initials || ' ' || regexp_replace(e.surname, '([^()]*)', '') farm_manager,
       r.research_station_id,
       r.name,
       dr.attendance_date attendance_date
  from research_station r
  join employees e
    on (e.employee_id = r.farm_manager)
cross join (select ($P{Date_from} + rownum - 1) attendance_date
               from dual
             connect by level <= ($P{Date_from} - sysdate + 1)) dr
  join employee_jobs ej
    on (e.employee_id = ej.employee_id)
where r.research_station_id = $P{Research_Station_id}


What I have tried:

Test Query

select e.employee_id,       e.initials || ' ' || regexp_replace(e.surname, '([^()]*)', '') farm_manager,       r.research_station_id,       r.name,       dr.attendance_date attendance_date  from research_station r  join employees e on (e.employee_id = r.farm_manager) cross join (select (to_date('16032020','DDMMYYYY') + rownum - 1) attendance_date from dual  connect by level <= to_date('20032020','DDMMYYYY') - sysdate + 1) dr  join employee_jobs ej on (e.employee_id = ej.employee_id) where r.research_station_id = 2;[/code]

[zain.nabi]

Is it because this query only works with the current week and not deffered?

[jgust]

I use the cross join with connect by all the time at work.  Our usage is a little different than what you have.  I don't have a need for a range instead I just need consistent n number of days/years.

-- next five daysSELECT (CURRENT_DATE-1 + LEVEL) AS attendance_dateFROM DUALCONNECT BY LEVEL <= 5order by 1 desc;-- last five daysSELECT (CURRENT_DATE+1 - LEVEL) AS attendance_dateFROM DUALCONNECT BY LEVEL <= 5order by 1 desc;[/code]

I modified the DR table to grab from parameter date and the next 5 days.

 select e.employee_id,       e.initials || ' ' || regexp_replace(e.surname, '([^()]*)', '') farm_manager,       r.research_station_id,       r.name,       dr.attendance_date attendance_date  from research_station r  inner join employees e    on (e.employee_id = r.farm_manager)  inner join employee_jobs ej    on (e.employee_id = ej.employee_id)  cross join (SELECT ($P{Date_from}-1 + LEVEL) AS attendance_date              FROM DUAL              CONNECT BY LEVEL <= 5) drwhere r.research_station_id = $P{Research_Station_id}[/code]