AnsweredAssumed Answered

NSQL Parameters Issue

Question asked by cshah on Nov 1, 2010
Latest reply on Nov 16, 2010 by cshah
Hello,
I am creating a portlet that allows us to see which hospital a particular resource supported during a given time period. In our test enviornment we have populated the USER VALUE 1 field with our hospital codes.
The parameters I am currently seeking are Resource Type, Time period, Timesheet Status, User Field 1, and Resource Name. I would also like to add Resource OBS, but read on for that issue.

For the parameters I am using the lookups below:
Resource Type: SRM_RESOURCE_TYPE
Time period: Portlet Timesheet Period Browse
Timesheet Status: Timesheet Status
Resource Name: SCH_Resource_Browse
Hospital code: User Field 1

My portlet seems to work as long as I have not provided a resource parameter. What am i missing in my code? This is the error I get when i put a resource in the parameter:

Error 500 - Internal Server Error. The server could not retrieve the document due to server-configuration or other technical problems. Contact your site administrator.

I would also like to add a resource obs parameter. From searching the boards i know i need to add this:
AND (@WHERE:PARAM:USER_DEF:INTEGER:OBS@ is null OR EXISTS 
(SELECT 1 FROM prj_obs_associations ASSOC, prj_obs_units_flat FLAT 
WHERE srm_resources.id = ASSOC.record_id 
AND ASSOC.table_name = 'SRM_RESOURCES' AND 
ASSOC.unit_id = FLAT.unit_id AND FLAT.branch_unit_id = @WHERE:PARAM:USER_DEF:INTEGER:OBS@))
to my where clause however when i add it to my code below, it's not taking it.

Thank you in advance.

SELECT
@select:dim:user_def:implied:RTE:ROW_NUMBER()OVER(ORDER BY R.ID):UniqueID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:R.FULL_NAME:RESOURCE_FULLNAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:TP.PRSTART:PERIOD_STARTDATE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:TP.PRFINISH-1:PERIOD_FINISHDATE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:L.NAME:TIMESHEET_STATUS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:I.NAME:INVESTMENT_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:CD.PRNAME:CHARGE_CODE@,
@SELECT:METRIC:USER_DEF:IMPLIED:SUM(TE.PRACTSUM/3600):RESOURCE_HOURS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:TE.USER_LOV1:RHC_CODE@
FROM PRTIMESHEET TS
INNER JOIN SRM_RESOURCES R on TS.PRRESOURCEID = R.ID
INNER JOIN PRTIMEPERIOD TP on TS.PRTIMEPERIODID = TP.PRID
INNER JOIN CMN_LOOKUPS_V L on TS.PRSTATUS = L.LOOKUP_ENUM
AND L.LOOKUP_TYPE = 'Timesheet_status'
AND L.LANGUAGE_CODE = 'en'
INNER JOIN PRTIMEENTRY TE on TS.PRID = TE.PRTIMESHEETID
LEFT OUTER JOIN PRASSIGNMENT A on TE.PRASSIGNMENTID = A.PRID
LEFT OUTER JOIN PRTASK T on A.PRTASKID = T.PRID
LEFT OUTER JOIN INV_INVESTMENTS I on T.PRPROJECTID = I.ID
LEFT OUTER JOIN PRCHARGECODE CD on TE.PRCHARGECODEID = CD.PRID
WHERE
@WHERE:PARAM:USER_DEF:INTEGER:TS.PRSTATUS:TIMESHEET_STATUS@
AND @WHERE:PARAM:USER_DEF:INTEGER:TP.PRID:TIME_PERIOD@
AND @WHERE:PARAM:USER_DEF:INTEGER:R.PERSON_TYPE:RESOURCE_TYPE@
AND @WHERE:PARAM:USER_DEF:INTEGER:R.FULL_NAME:RESOURCE_FULLNAME@
AND @WHERE: PARAM:USER_DEF:INTEGER:TE.USER_LOV1:RHC_CODE@
AND @FILTER@
GROUP BY R.ID, TE.USER_LOV1, I.NAME, R.FULL_NAME, 
TP.PRSTART, TP.PRFINISH-1,L.NAME, CD.PRNAME, TS.PRUID
HAVING @HAVING_FILTER@

Outcomes