We have a "license usage" portlet which provides a view of all resources and their access rights, as per:
SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:U.ID:UserID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:U.LAST_NAME:LastName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:U.FIRST_NAME:FirstName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:U.USER_NAME:UserName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:OBS.NAME:Department@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.goc_apm:APM@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.goc_ppm:PPM@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.goc_it_plan:ITPlan@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.goc_serv:Serv@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:U.LAST_LOGGED_IN_DATE:LastLoggedIn@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:U.USER_STATUS_ID:Status@
FROM CMN_SEC_USERS U, ODF_CA_RESOURCE R, SRM_RESOURCES S, PRJ_OBS_UNITS OBS
WHERE R.ID = S.ID
AND S.USER_ID=U.ID
AND OBS.UNIQUE_NAME = R.DEPARTMENT
AND S.IS_ACTIVE = 1
AND (R.department = @WHERE:PARAM:USER_DEF:STRING:Dept@ OR @WHERE:PARAM:USER_DEF:STRING:Dept@ IS NULL)
AND (R.goc_apm = @WHERE:PARAM:USER_DEF:STRING:fltAPM@ OR @WHERE:PARAM:USER_DEF:STRING:fltAPM@ IS NULL)
AND (R.goc_ppm = @WHERE:PARAM:USER_DEF:STRING:fltPPM@ OR @WHERE:PARAM:USER_DEF:STRING:fltPPM@ IS NULL)
AND (R.goc_it_plan = @WHERE:PARAM:USER_DEF:STRING:fltITP@ OR @WHERE:PARAM:USER_DEF:STRING:fltITP@ IS NULL)
AND (R.goc_serv = @WHERE:PARAM:USER_DEF:STRING:fltSERV@ OR @WHERE:PARAM:USER_DEF:STRING:fltSERV@ IS NULL)
AND @FILTER@
This is great for admins, etc, but clients are now requesting a version which would show them the same level of information, but for their specific department. So a user from DeptA should view all resources relating to that department, but no resources from other departments.
I've tried a couple of variations using PARAM:USER_DEF, but nothing has panned out so far. They end up being limited to viewing only their own resource information, rather than all users for said department.
Any help would be appreciated, thanks!