Hi David,
I used your above code, however, it only allows exact text searching still. I changed the above suggestion to the below syntax and I am now able to search by wildcard even though the field is labeled .
Project (Top of Hierarchy Grid):
AND (P.NAME LIKE '%' || @WHERE:PARAM:USER_DEF:STRING:PROJECT_NAME@ || '%' OR
@WHERE:PARAM:USER_DEF:STRING:PROJECT_NAME@ IS NULL)
Task (Bottom of Hierarchy Grid):
AND (T.PRNAME LIKE '%' || @WHERE:PARAM:USER_DEF:STRING:TASK_NAME@ || '%' OR
@WHERE:PARAM:USER_DEF:STRING:TASK_NAME@ IS NULL)
The only stumbling block I am left with is the ability to print "all" the visible records the portlet view and not just the parent level records (Project)? I did find a community discussion about this, and I added the additional union and empty query with the same column headers as both the project and task level queries and the below @WHERE statement to the where clause. The entire NSQL is listed below for your viewing pleasure.
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:MST:ROWNUM:uq_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Project Name":Project_Name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Task Name":Task_Name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."ID":id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery.externalid:externalid@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Project ID":project_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Audit ID":audit_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."CSOC ID":csoc_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Tech Risk ID":tech_risk_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Systems Pillar Sponsor":systems_pillar_sponsor@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Project Manager":project_manager@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Task Owner":task_owner@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Business Lead":business_lead@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Business Owner":business_owner@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Start":start_Date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Finish":finish_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Baseline Start":baseline_start@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Baseline Finish":baseline_finish@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."RAG Status":rag_status@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Status Report Date":status_report_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Status":status@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Status Comment":status_comment@,
@SELECT:DIM_PROP:USER_DEF:BOOLEAN:MST:hgquery.prismilestone:milestone@,
@SELECT:DIM_PROP:USER_DEF:BOOLEAN:MST:hgquery.priskey:key_task@,
@SELECT:DIM_PROP:USER_DEF:BOOLEAN:MST:hgquery.is_active:is_active@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Product":product@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Sub Product":sub_product@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Strategic Investment Category":strategic_investment_category@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery.hg_has_children:hg_has_children@
FROM(
SELECT NULL "Project ID",
NULL "ID",
NULL "Audit ID",
NULL "CSOC ID",
NULL "Tech Risk ID",
NULL "Project Name",
NULL "Task Name",
NULL HG_HAS_CHILDREN,
NULL "Systems Pillar Sponsor",
NULL "Project Manager",
NULL "Task Owner",
NULL "Business Lead",
NULL "Business Owner",
NULL "Start",
NULL "Finish",
NULL "Baseline Start",
NULL "Baseline Finish",
NULL "RAG Status",
NULL "Status Report Date",
NULL "Status",
NULL "Status Comment",
NULL PRISMILESTONE,
NULL PRISKEY,
NULL Is_Active,
NULL "Product",
NULL "Sub Product",
NULL "Strategic Investment Category",
NULL EXTERNALID
FROM DUAL
WHERE 1=1
AND @WHERE:PARAM:USER_DEF:INTEGER:hg_all_rows@ = 1
UNION
SELECT "Project ID",
"ID",
"Audit ID",
"CSOC ID",
"Tech Risk ID",
"Project Name",
"Task Name",
HG_HAS_CHILDREN,
"Systems Pillar Sponsor",
"Project Manager",
"Task Owner",
"Business Lead",
"Business Owner",
"Start",
"Finish",
"Baseline Start",
"Baseline Finish",
"RAG Status",
"Status Report Date",
"Status",
"Status Comment",
PRISMILESTONE,
PRISKEY,
IS_ACTIVE,
"Product",
"Sub Product",
"Strategic Investment Category",
EXTERNALID
FROM (SELECT P.UNIQUE_NAME "Project ID",
P.ID "ID",
CP.AUDIT_ID "Audit ID",
CP.CSOC_ID "CSOC ID",
CP.TECH_RISK_ID "Tech Risk ID",
P.NAME "Project Name",
NULL "Task Name",
P.ID HG_HAS_CHILDREN,
SPS.NAME "Systems Pillar Sponsor",
RPM.FULL_NAME "Project Manager",
NULL "Task Owner",
RBL.FULL_NAME "Business Lead",
RBO.FULL_NAME "Business Owner",
PR.PRSTART "Start",
PR.PRFINISH "Finish",
PR.PRBASESTART "Baseline Start",
PR.PRBASEFINISH "Baseline Finish",
CASE
WHEN ST.OVERALL > 0 THEN
ST.OVERALL
ELSE
0
END "RAG Status",
TO_CHAR(ST.COP_REPORT_DATE, 'MM/DD/YYYY') "Status Report Date",
DECODE(PR.PROGRESS, 0, 'Not Started', 1, 'Started', 2, 'Completed') "Status",
ST.COP_REPORT_UPDATE "Status Comment",
2 PRISMILESTONE,
2 PRISKEY,
CAST(P.IS_ACTIVE AS INTEGER) IS_ACTIVE,
PRO.NAME "Product",
SUBPR.NAME "Sub Product",
CP.BBH_STRATEGICINVCATE "Strategic Investment Category",
P.UNIQUE_NAME EXTERNALID
FROM NIKU.PRJ_PROJECTS PR,
NIKU.SRM_PROJECTS P,
NIKU.ODF_CA_PROJECT CP,
NIKU.SRM_RESOURCES RBO,
NIKU.SRM_RESOURCES RPM,
NIKU.SRM_RESOURCES RBL,
NIKU.ODF_CA_INV SP,
NIKU.PRTASK TSK,
(SELECT ODF.ODF_PARENT_ID,
ODF.COP_REPORT_DATE,
ODF.COP_REPORT_UPDATE,
(ODF.COP_SCHEDULE_STATUS + ODF.COP_SCOPE_STATUS +
ODF.COP_COST_EFT_STATUS) OVERALL
FROM NIKU.ODF_CA_COP_PRJ_STATUSRPT ODF,
(SELECT OD.ODF_PARENT_ID, MAX(OD.ID) "ID"
FROM NIKU.ODF_CA_COP_PRJ_STATUSRPT OD
WHERE OD.COP_REPORT_STATUS = 'FINAL'
AND OD.COP_REPORT_DATE >=
ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -1)
GROUP BY OD.ODF_PARENT_ID) LU
WHERE LU.ID = ODF.ID) ST,
(SELECT CMN.LOOKUP_CODE, CMN.NAME
FROM NIKU.CMN_LOOKUPS_V CMN
WHERE CMN.LANGUAGE_CODE = 'en'
AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY4') SPS,
(SELECT CMN.LOOKUP_CODE, CMN.NAME
FROM NIKU.CMN_LOOKUPS_V CMN
WHERE CMN.LANGUAGE_CODE = 'en'
AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY1') PRO,
(SELECT CMN.LOOKUP_CODE, CMN.NAME
FROM NIKU.CMN_LOOKUPS_V CMN
WHERE CMN.LANGUAGE_CODE = 'en'
AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY2') SUBPR
WHERE P.ID = PR.PRID
AND P.ID = TSK.PRPROJECTID (+)
AND CP.ID = P.ID(+)
AND CP.OBJ_STAKEHOLDER1 = RBO.ID(+)
AND RPM.USER_ID = PR.MANAGER_ID(+)
AND CP.BBH_BUSINESSLEAD = RBL.ID(+)
AND P.ID = SP.ID(+)
AND SP.OBJ_PFL_CATEGORY4 = SPS.LOOKUP_CODE(+)
AND SP.OBJ_PFL_CATEGORY1 = PRO.LOOKUP_CODE(+)
AND SP.OBJ_PFL_CATEGORY2 = SUBPR.LOOKUP_CODE(+)
AND P.ID = ST.ODF_PARENT_ID(+)
AND (PR.PRCLOSEDTIME IS NULL OR PR.PRCLOSEDTIME >= '01-JAN-17')
AND P.IS_TEMPLATE = 0
AND P.IS_PROGRAM = 0
AND PR.PRUSERTEXT1 NOT LIKE '%HARD%'
AND PR.PRUSERTEXT1 NOT LIKE '%ROUTE%'
AND (P.NAME LIKE '%' || @WHERE:PARAM:USER_DEF:STRING:PROJECT_NAME@ || '%' OR
@WHERE:PARAM:USER_DEF:STRING:PROJECT_NAME@ IS NULL)
AND @WHERE:PARAM:user_def:STRING:hg_row_id@ IS NULL)
UNION
SELECT "Project ID",
"ID",
"Audit ID",
"CSOC ID",
"Tech Risk ID",
"Project Name",
"Task Name",
HG_HAS_CHILDREN,
"Systems Pillar Sponsor",
"Project Manager",
"Task Owner",
"Business Lead",
"Business Owner",
"Start",
"Finish",
"Baseline Start",
"Baseline Finish",
"RAG Status",
"Status Report Date",
"Status",
"Status Comment",
PRISMILESTONE,
PRISKEY,
Is_Active,
"Product",
"Sub Product",
"Strategic Investment Category",
EXTERNALID
FROM (SELECT P.UNIQUE_NAME "Project ID",
T.PRID "ID",
CP.AUDIT_ID "Audit ID",
CP.CSOC_ID "CSOC ID",
CP.TECH_RISK_ID "Tech Risk ID",
P.NAME "Project Name",
T.PRNAME "Task Name",
NULL HG_HAS_CHILDREN,
SPS.NAME "Systems Pillar Sponsor",
NULL "Project Manager",
RTO.FULL_NAME "Task Owner",
RBL.FULL_NAME "Business Lead",
RBO.FULL_NAME "Business Owner",
T.PRSTART "Start",
T.PRFINISH "Finish",
BA.START_DATE "Baseline Start",
BA.FINISH_DATE "Baseline Finish",
CAST(CT.MILESTONE_STATUS AS INTEGER) "RAG Status",
TO_CHAR(SYSDATE, 'MM/DD/YYYY') "Status Report Date",
DECODE(T.PRSTATUS, 0, 'Not Started', 1, 'Started', 2, 'Completed') "Status",
T.PRUSERTEXT1 "Status Comment",
T.PRISMILESTONE,
T.PRISKEY,
CAST(P.IS_ACTIVE AS INTEGER) IS_ACTIVE,
PRO.NAME "Product",
SUBPR.NAME "Sub Product",
CP.BBH_STRATEGICINVCATE "Strategic Investment Category",
T.PREXTERNALID EXTERNALID
FROM NIKU.PRTASK T,
NIKU.ODF_CA_TASK CT,
NIKU.SRM_RESOURCES RTO,
NIKU.SRM_PROJECTS P,
NIKU.PRJ_PROJECTS PR,
NIKU.ODF_CA_PROJECT CP,
NIKU.ODF_CA_INV SP,
NIKU.SRM_RESOURCES RBO,
NIKU.SRM_RESOURCES RBL,
(SELECT CMN.LOOKUP_CODE, CMN.NAME
FROM NIKU.CMN_LOOKUPS_V CMN
WHERE CMN.LANGUAGE_CODE = 'en'
AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY4') SPS,
(SELECT CMN.LOOKUP_CODE, CMN.NAME
FROM NIKU.CMN_LOOKUPS_V CMN
WHERE CMN.LANGUAGE_CODE = 'en'
AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY1') PRO,
(SELECT CMN.LOOKUP_CODE, CMN.NAME
FROM NIKU.CMN_LOOKUPS_V CMN
WHERE CMN.LANGUAGE_CODE = 'en'
AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY2') SUBPR,
(SELECT B.OBJECT_ID, B.START_DATE, B.FINISH_DATE
FROM NIKU.PRJ_BASELINE_DETAILS B
WHERE B.OBJECT_TYPE = 'TASK') BA
WHERE T.PRID = CT.ID
AND T.TASK_OWNER = RTO.ID(+)
AND T.PRISTASK = 1
AND (T.PRISKEY = 1 OR T.PRISMILESTONE = 1)
AND P.ID = T.PRPROJECTID
AND P.ID = PR.PRID
AND CP.ID = P.ID(+)
AND CP.BBH_BUSINESSLEAD = RBL.ID(+)
AND CP.OBJ_STAKEHOLDER1 = RBO.ID(+)
AND SP.ID = P.ID(+)
AND SP.OBJ_PFL_CATEGORY4 = SPS.LOOKUP_CODE(+)
AND SP.OBJ_PFL_CATEGORY1 = PRO.LOOKUP_CODE(+)
AND SP.OBJ_PFL_CATEGORY2 = SUBPR.LOOKUP_CODE(+)
AND T.PRID = BA.OBJECT_ID(+)
AND (PR.PRCLOSEDTIME IS NULL OR PR.PRCLOSEDTIME >= '01-JAN-17')
AND P.IS_TEMPLATE = 0
AND P.IS_PROGRAM = 0
AND PR.PRUSERTEXT1 NOT LIKE '%HARD%'
AND PR.PRUSERTEXT1 NOT LIKE '%ROUTE%'
AND (T.PRNAME LIKE '%' || @WHERE:PARAM:USER_DEF:STRING:TASK_NAME@ || '%' OR
@WHERE:PARAM:USER_DEF:STRING:TASK_NAME@ IS NULL)
AND t.PRPROJECTID = @WHERE:PARAM:user_def:STRING:hg_row_id@)
) hgquery
where 1=1 AND
(
@WHERE:PARAM:USER_DEF:INTEGER:OBS@ IS NULL
OR
(
SELECT pou.id AS prj_unit_id
FROM prj_obs_associations poa
JOIN prj_obs_units pou ON pou.ID = poa.unit_id
WHERE poa.table_name = 'SRM_PROJECTS'
AND poa.record_id = hgquery."ID"
AND pou.type_id = (SELECT P.type_id
FROM prj_obs_units P
WHERE P.id = @WHERE:PARAM:USER_DEF:INTEGER:OBS@ )
)
IN
(
SELECT F.unit_id
FROM prj_obs_units_flat F
WHERE branch_unit_id = @WHERE:PARAM:USER_DEF:INTEGER:OBS@
)
) AND @filter@
Regards