The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

URGENT -- How can i make the rownum as a filter in portlets?

Highlighted
Sevilay
Collector

URGENT -- How can i make the rownum as a filter in portlets?

Hi,

i want users can choose the max number of rows returned in a custom portlet.
Portlets automatically add an "rownum < 200" criteria to every datasource query.I want to make this value parametric .
I can do this in sql sentence.But i can't apply it in datasource because of the optionally selected parameters in the inner query.I attach the query.
But there can be a way of doing this without query.
Thanks in advance.
11 REPLIES
Semenov Alexand
Esteemed Contributor

Re: URGENT -- How can i make the rownum as a filter in portlets?

Hi Sevilay.
Sample in attach.
Good Luck
Sevilay
Collector

Re: URGENT -- How can i make the rownum as a filter in portlets?

Hi Alexander,

i thought and applied the same thing but i stated in my first post that
"I can do this in sql sentence.But i can't apply it in datasource because of the optionally selected parameters in the inner query."

When applying this,the parameter columns 'Department' AND 'Pool Manager' becomes problem.You also closed out these statements in the query.i can not remove them.

In any case thanks.

Re: URGENT -- How can i make the rownum as a filter in portlets?

Hi Sevily,

did you know that PPM won't add the rownum limit to the select statement if you define a group by clause in PPM datasource.

So don't use the inner select.
Put the SELECT, FROM, WHERE, GROUP BY and ORDER BY clause to the right fields in the PPM datasource
and PPM will return ALL rows without limiting the result set.

If you still want the user to limit the result set, do it like Alexander advised with another filter field.

Regards
Michael
Sevilay
Collector

Re: URGENT -- How can i make the rownum as a filter in portlets?

Hi Michael,

i think my poor English didn't let me explain the problem very well.
i did the same thing with Alexander,i stated that in my first post.In order to put the rownum as a filter field in datasource, you have to put all other sql statement in parantheses "()" and write all the query in from clause of the datasource.I have two optional parameter field which are now in the "from clause" of the datasource like that
"and substr(ku.department_code,1,3) = '[P.SS_DEPARTMENT]'"

Of course you can put the rownum and those parameters as a filter field in the "filter fields".But if the user don't choose any value from those optional parameters query fails.I don't want to give any default value.My query should run with or without department field.User want to see the result for all the departments or for only one department.

I hope i can explain this time.

Thanks.
Sevilay
Collector

Re: URGENT -- How can i make the rownum as a filter in portlets?

Ok i did it but i didn't want to use this alternative way from the beginning.

i want to search extra more flexible approach.
All the answers show me that there is no alternative way.

i changed the validations of those optional parameter fields and put the all sql statement in "from clause".

One of the validations becomes like that :

select '99' org_unit_short,'ALL' org_unit_name from dual
union all
select distinct substr(org_unit_name,1,3) org_unit_short,org_unit_name
from ppmasya_usr.krsc_org_units ku
where ku.org_unit_name in ('SGM', 'YGM','SDM','KTM','BTPYM')
and ku.enabled_flag = 'Y'

In order to see all the departments user should choose "ALL" in the department filter.
I changed my inner sql regarding the this parameter like that "and substr(ku.department_code,1,3) like decode('[P.SS_DEPARTMENT]','99', '%%','[P.SS_DEPARTMENT]')".

Best regards.

Re: URGENT -- How can i make the rownum as a filter in portlets?

This is exactly the reason why you should use the filter fields and not put the condition hard coded in the PPM fields for WHERE or FROM clause.
Because the condition you defined in filter field will only be added to the where clause, if the user chooses a value.
So if you don't choose a department, the condition won't be added to the select statement and PPM will show all departments.
If you choose a value, you will only see that department.

If your solution works, you will be fine.
Regards
Michael
Sevilay
Collector

Re: URGENT -- How can i make the rownum as a filter in portlets?

Hi Michael,

i think i didn't explain the problem again.
I know the filter condition's usage.
In order to understand the problem you should practice my query as Alexander's suggestion.
After adding the rownum to query (in case you put the remaining query in from clause) and NOT put the parameters as hard coded,all the selected values of parameters are added to the end of the query like that :

select T1.ManagerName,T1.Count from
(select
ku.full_name as ManagerName,
count(*) as Count
from ppmasya_usr.rsc_positions rp,
ppmasya_usr.rsc_staffing_profiles sp,
ppmasya_usr.knta_users ku,
ppmasya_usr.rsc_resource_pools rsp,
ppmasya_usr.rsc_resource_pool_managers pm
where rp.staffing_profile_id = sp.staffing_profile_id
and sp.status_code <> 4
and rp.resource_pool_id = rsp.resource_pool_id
and rsp.resource_pool_id = pm.resource_pool_id
and ku.user_id = pm.manager_user_id
group by ku.full_name
order by Adet desc
) T1
where rownum <= 10
and ku.department_code = 'SGM'

and YOU GET THE ERROR.Because there is no column named "department_code".Because it is in the inner query.But the parameters are added to the end of the query.

I hope ,you get it this time?

Thanks anyway.
Vibhor Dwivedi
Regular Collector

Re: URGENT -- How can i make the rownum as a filter in portlets?

try this

select T1.ManagerName,T1.Count ,T1.department_code from
(select
ku.full_name as ManagerName,
count(*) as Count ,ku.department_code
from ppmasya_usr.rsc_positions rp,
ppmasya_usr.rsc_staffing_profiles sp,
ppmasya_usr.knta_users ku,
ppmasya_usr.rsc_resource_pools rsp,
ppmasya_usr.rsc_resource_pool_managers pm
where rp.staffing_profile_id = sp.staffing_profile_id
and sp.status_code <> 4
and rp.resource_pool_id = rsp.resource_pool_id
and rsp.resource_pool_id = pm.resource_pool_id
and ku.user_id = pm.manager_user_id
group by ku.full_name,department_code
order by Adet desc
) T1
where rownum <= 10
and department_code = 'SGM'


Note :need to check for each department more mangagers are there or not.

Dont map department_code in partlet defintion...
Sevilay
Collector

Re: URGENT -- How can i make the rownum as a filter in portlets?

Hi Vibbor,

you are very close to understand me.Thank you firstly.Yes,your response becomes a solution but what will be happen if my filter condition isn't in the selection list of the inner query ? I gave department_code as an example.
Sometimes there is need to add a filter on a column which is not in the select clause of the inner query and you cannot put that column in the select clause because when you put that column in select clause you have to put in the group by clause.

Thanks again.
Vibhor Dwivedi
Regular Collector

Re: URGENT -- How can i make the rownum as a filter in portlets?

Hi sevilay,

it is a sql constraint.So any time we need to put any column in filter we have to put same in select clause and include it in group by clause.For this kind of requirement we have to look for alternative..

Regards,
Vibhor
Sevilay
Collector

Re: URGENT -- How can i make the rownum as a filter in portlets?

Hi Vibhor,

this is the exact answer i want to be sure.
Thanks for undesrtanding and repyling me.
I sometimes have difficulty to explain myself correctly.

Ok,i'll try to find some workaround solutions.

Have a good day.
//Add this to "OnDomLoad" event