Search code examples
oracle-databasejasper-reportscrosstab

IReport Creation Issue- Get all Months of Year

I want to create a crosstab for LeaveCount for Employees for respective Months with iReport (JasperReports). I'm using Oracle database.

The problem is, I'm getting only the months where the measure exists, I want to display all months of the year whether the measure(Leave of Employee) exists for this month or not.

Solution

  • That solved the issue with the answer suggested by @Pu297 . Later I got an even better method which involves no table creation and saves trouble of creating a table every time i need to run report on a new database.

    select a.mnth,b.leavecount from
(
     SELECT to_char(to_date(LVL,'MM'),'MM') mnth
     FROM (select level lvl from dual CONNECT BY LEVEL <=12)
) a
,(SELECT to_char(leavedate,'MM') AS MONTH,leavecount..."your query")b
WHERE a.mnth=b.MONTH(+)

    This is better way according to me for this issue. Cheers!!!