Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

date filter

Highlighted
musifier
Occasional Advisor

date filter

Hi!

 

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:

LEFT JOIN
  (
   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
  )sums

 

 

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?

1 REPLY
Celil
Esteemed 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

 

 

Celil

IT Governance Professional
& PPM Solution Architect
//Add this to "OnDomLoad" event