21.2.5 Using SQL Features in Report Sources

Use SQL features and custom functions in report sources to shape data for a report.

Your report source queries can use any SQL functionality, including custom function calls, to retrieve the data your report needs. The Employees in Department (by Tenure) report source with data loop name emp_t has the following query. It calls the TENURE_YEARS_MONTHS function in the EBA_DEMO_WOODSHR_REPORT package to format the employee's tenure as a phrase like "1 year, 5 months".

select empno, 
       ename, 
       job, 
       eba_demo_woodshr_report.tenure_years_months(hiredate) as tenure
  from emp
 where deptno = :G_DEPTNO_FOR_REPORT
 order by hiredate

The source for this helper function appears below.

-- In package eba_demo_woodshr_report
function tenure_years_months (p_date in date)
  return varchar2
is
  l_months      number;
  l_years       number;
  l_rem_months  number;
begin
  if p_date is null then
    return null;
  end if;

  -- Whole months completed between the dates
  l_months := floor(months_between(trunc(sysdate), trunc(p_date)));

  -- If p_date is in the future, clamp to 0
  if l_months < 0 then
    l_months := 0;
  end if;

  l_years      := trunc(l_months / 12);
  l_rem_months := mod(l_months, 12);

  return  l_years || ' ' ||
          case when l_years = 1 then 'year' else 'years' end || ', ' ||
          l_rem_months || ' ' ||
          case when l_rem_months = 1 then 'month' else 'months' end;
end;