Project and Portfolio Management Practitioners Forum

date filter


date filter



I want to make a portlet to present financial data from the table fm_forecast_actual_period_sum

I pick out the start and end dates from ppm_fiscal_periods


In a raw test version that sql looks like this:

   SELECT forecast_actual_id,
    count(period_id) num_of_lines,
    max(period_id) end_date,
    sum(nvl(actual_labor_total_bse, 0)) lab_tot,
    SUM(nvl(actual_total_bse, 0)) act_tot
  FROM fm_forecast_actual_period_sum s
   JOIN ppm_fiscal_periods period
ON period.fiscal_period_id       = s.period_id
where to_date('20120801', 'yyyymmdd') <= period.start_date
AND period.end_date             <= to_date('20120831', 'yyyymmdd')
  GROUP BY forecast_actual_id, period.start_date, period.end_date



I now want to be able to apply a "month-filter" in place of the hard-coded dates 20120801 and 20120831 so that I can cofigure the portlet with start and end month for the financial data. Which validation should I use? I tried some "fiscal period....." that shows the user a list of months but I could not get it to work. In the mentioned validation there are start- and end dates; How do I access the values of those from the sql code in the datasource?

Acclaimed Contributor.

Re: date filter

Hi musif,


You can use below code as example;

to_date(period.end_date,'mm') >= [month token]  AND

to_date(period.start_date,'mm') <= [month token]


You can create custom validation;

VP.Token should show name of month,

P.Token must show number of month



PS: Please keep alive our forum via KUDOS the professionals




IT Governance Professional
& PPM Solution Architect