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

User Token from Filter Field in the where clause

SOLVED
Go to solution
Highlighted
Ramazan1
Regular Collector

User Token from Filter Field in the where clause

Hello,

 

I am working on a SQL query and I am very confused: The aim is

- Our Company hierarchical Structure: Division lead, Department Lead, Group Lead, Employees

- Everybody in the company has to submit efforts in HP PPM

- When the group lead is logged in, he should see own efforts as well as of the own employees -> OK

- When the department lead is logged in, he should see own efforts, efforts of his group leads and efforts of all employees of the group leads, who are member of the department lead -> OK

- By default: the query should show all efforts of last period, e.g. when group leader logs in, he sees efforts of last week of his employees -> OK 

 

- BUT NOW: When a lead wants to see the efforts of one or more old periods, I have to set a filter field, with a TOKEN.  That Token makes me crazy, because when I set into the field an old period, I see the efforts of choosen period and the efforts of the last week (period). The issue is: I always see the efforts of the last week. But when I filter to a period, I want to see the efforts of that filtered period.

 

where clause

CURRENT_DATE - 7 between ktmg_periods.start_date AND ktmg_periods.end_date

 

filter field:

or ktmg_periods.period_id in ([P.PERIODS])  <-- This is the TOKEN

 

My validation returns me the period_id

 

very Short:

by default -> see efforts of last week

Use filter field: see efforts of choosen period in the filter field.

 

and not:

Use filter field: see efforts of choosen period and of last period.

 

in the where clause I cannot say

([P.PERIODS]) is null and CURRENT_DATE - 7 between ktmg_periods.start_date AND ktmg_periods.end_date

 

because if the filter is not set the where clause doesn't see the TOKEN ([P.PERIODS])

 

HEEEELLLP

 

by the way: I connected two query with union all:

first query = show efforts on projects / tasks

second query = show effort on demands

 

Regards,

Ramazan

3 REPLIES
Jason Nichols K
Honored Contributor
Solution

Re: User Token from Filter Field in the where clause

Try playing with something like this:

In the WHERE clause, put something like:

where ktmg_periods.period_id in decode('[P.P_PERIODS]', '['||'P.P_PERIODS]',
( select kp.period_id
from ktmg_periods kp
where sysdate - 7 between kp.start_date and kp.end_date
), '[P.P_PERIODS]')

and then in the filter where clause, just put AND 1=1
Ramazan1
Regular Collector

Re: User Token from Filter Field in the where clause

Hello Jason,

 

Great, it works! Thank you,

Regards!
Ramazan

TurboMan
Member

Re: User Token from Filter Field in the where clause

Hi Ramazan,

 

If you are happy with the solution, please mark it as solved, so it would be a remedy to someone looking for a solution.

And also give kudos  to the user who solved your problem..

 

Regards

T.M.

//Add this to "OnDomLoad" event