AnsweredAssumed Answered

NSQL view all dept X accounts for current user from dept X

Question asked by cthor on Mar 15, 2019
Latest reply on Mar 19, 2019 by cthor

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!

Outcomes