AnsweredAssumed Answered

OBS Filter not working on a Portlet

Question asked by e_martin on Apr 25, 2013
Latest reply on May 3, 2013 by e_martin
I have a portlet for Security rights where it pulls the Resource OBS for the users in our database, all users are at Level 7 of the OBS.

For some reason when I added the OBS Resource Filter Browse lookup to the query and added the field to the filter, it doesn't take the settings.

I set the Filter Field Properties to have OBS Filter Mode: Unit and Descendants but it's still stopping at the top layer and only looking for data with that ID, not all the ID's in the lower section of the OBS.

Any ideas? Is there a different Lookup I can assign to the ID Field?

For reference, here is the query that the Lookup uses:
SELECT   
@SELECT:a.name:name@,       
@SELECT:a.id:id@,
@SELECT:a.type_id:type_id@    
FROM   prj_obs_units a
WHERE @FILTER@  
and here is the query that powers the portlet in question:
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:SECURITY:(N_Number||SEC_GROUP_NAME):UNIQUE_ID@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:SECURITY:ADMIN_ID:ADMIN_ID@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:SECURITY:RES_ID:RES_ID@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:SECURITY:N_Number:N_Number@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:SECURITY:RES_FIRST_NAME:RES_FIRST_NAME@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:SECURITY:RES_LAST_NAME:RES_LAST_NAME@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:SECURITY:SEC_GROUP_ID:SEC_GROUP_ID@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:SECURITY:SEC_GROUP_NAME:SEC_GROUP_NAME@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:SECURITY:SEC_GROUP_DESC:SEC_GROUP_DESC@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:SECURITY:OBS_UNIT_ID:OBS_UNIT_ID@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:SECURITY:OBS_UNIT_NAME:OBS_UNIT_NAME@

FROM 
(SELECT 

ADMIN_RES.ID as ADMIN_ID, 

RES.ID as RES_ID,

ADMIN_RES.USER_NAME as N_Number,

ADMIN_RES.FIRST_NAME as RES_FIRST_NAME, 

ADMIN_RES.LAST_NAME as RES_LAST_NAME,  

GROUPS.ID as SEC_GROUP_ID, 

NAME.GROUP_NAME as SEC_GROUP_NAME, 

NAME.DESCRIPTION as SEC_GROUP_DESC,

UNIT.ID as OBS_UNIT_ID,

UNIT.NAME as OBS_UNIT_NAME


FROM 

CMN_SEC_USER_GROUPS USERS,

CMN_SEC_GROUPS GROUPS,

CMN_SEC_USERS ADMIN_RES,

CMN_SEC_GROUPS_V NAME,

SRM_RESOURCES RES,

PRJ_OBS_ASSOCIATIONS ASSC,

PRJ_OBS_UNITS UNIT

WHERE USERS.GROUP_ID = GROUPS.ID
AND USERS.USER_ID = ADMIN_RES.ID
AND GROUPS.ID = NAME.ID
AND RES.USER_ID = ADMIN_RES.ID
AND RES.ID = ASSC.RECORD_ID
AND ASSC.TABLE_NAME = 'SRM_RESOURCES'
AND UNIT.ID = ASSC.UNIT_ID
AND UNIT.TYPE_ID=5000001
AND GROUPS.IS_ACTIVE=1
AND LANGUAGE_CODE='en'
AND ADMIN_RES.USER_NAME LIKE 'N%')

WHERE @FILTER@
as you can see they are both comparing the same ID from the Same table, it just seems like the Lookup is ignoring the logic to look at the Unit and Descendants

Outcomes