Clarity PPM

Expand all | Collapse all

How do I filter an NSQL Portlet Page?

Jump to Best Answer
  • 1.  How do I filter an NSQL Portlet Page?

    Posted 10-03-2018 07:34 AM
      |   view attached

    Hi,

     

    I am creating a new portlet on CA PPM 15.3 for an initiative being driven by RAG Status at both the project and task levels.  I have successfully created the nSQL query, created the page and portlet page.

     

    I am having a road block where I can only filter on the project level data, the task level data is not being filtered properly.  My nSQL query has a single dimension and I have it set up with hg_has_children so that the user can click the expander and see the underlying milestones and associated RAG status.

     

    Can the community help explain how I can filter on both?  I have tried creating 2 dimensions, however, that has created more issues when I try and build the Page, I cannot see "data" columns for the x axis ? At this point if I can filter on all data in the original attached nSQL query, that would be the best for now.

     

    SnapShot:

     

    Thanks Everyone

    Jonathan

    Attachment(s)



  • 2.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-03-2018 09:13 AM

    Can you explain what you mean by "the task level data is not being filtered properly" - in terms of what you expect to happen when you enter a specific filter and what is actually happening. 

     

    Your screen shot looks right to me in terms of what is displayed and what the filter is - I might be misunderstanding what you are trying to demonstrate though.

     



  • 3.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-03-2018 10:33 AM

    The query is

    SELECT
    @SELECT:DIM:USER_DEF:IMPLIED:MST:ROWNUM:uq_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Name":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."Owner - Manager":owner_manager@,
    @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 "Project ID",
           "ID",
           "Audit ID",
           "CSOC ID",
           "Tech Risk ID",
           "Name",
    HG_HAS_CHILDREN,
           "Systems Pillar Sponsor",
           "Owner - Manager",
           "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 "Name",
                   P.ID HG_HAS_CHILDREN,
                   SPS.NAME "Systems Pillar Sponsor",
                   RPM.FULL_NAME "Owner - Manager",
                   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",
                   NULL PRISMILESTONE,
                   NULL 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,
                   
                   (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 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 @WHERE:PARAM:user_def:STRING:hg_row_id@ IS NULL)

     

    UNION

     

    SELECT "Project ID",
           "ID",
           "Audit ID",
           "CSOC ID",
           "Tech Risk ID",
           "Name",
    HG_HAS_CHILDREN,
           "Systems Pillar Sponsor",
           "Owner - Manager",
           "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",
                   T.PRNAME "Name",
                   NULL HG_HAS_CHILDREN,
                   SPS.NAME "Systems Pillar Sponsor",
                   RTO.FULL_NAME "Owner - Manager",
                   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.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 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 t.PRPROJECTID = @WHERE:PARAM:user_def:STRING:hg_row_id@)
    ) hgquery
       
       where 1=1 and @filter@



  • 4.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-03-2018 02:19 PM

    Hi Urmas,

     

    I don't understand your response, when I compare your above query to my original, they appear to be identical.  Can you share what you made different?

     

    Regards



  • 5.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-04-2018 03:04 AM

    It is your query.

    When it is posted it is easier to read than a zipped attachment. I posted hoping it increases the chances of getting an answer.



  • 6.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-04-2018 06:05 AM

    Hi Urmas,

     

    Thank you, I will do that from now on.

     

    Regards



  • 7.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-03-2018 02:20 PM

    Hi David,

     

    When I filter on name for instance, only actual projects are being filtered out, the underlying child names are not being filtered.

     

    Regards



  • 8.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-04-2018 04:20 AM

    OK well I'm still not clear (from the screen shot) what is "wrong" - but perhaps having the task name and the project name as distinct attributes (rather than both being returned in the "Project Name" column) might clear things up - you could then have distinct filters for project and task names?

     

    (you've done the hard bit of the NSQL of course ; the hierarchical/expandable bits - the thing to remember with this is that the whole query gets re-run when you click the "expand", but your @FILTER@ looks in the right place to pass in the filters to the actual SQL. Maybe you could get some investigative clues by previewing the NSQL and then working out what is going "wrong" with the filter in the task expansion section?)



  • 9.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-04-2018 08:32 AM

    If I pull the task name into a different column, would I loose the integrity of the hg_has_children functionality?

     

    Thanks



  • 10.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-04-2018 08:58 AM

    Don't think so.

     

    To be honest though, whenever I've built nasty hierarchical portlets I've had to do it really slowly, bit by bit, to make sure I understood what each part of the query was meant to be doing in terms of what I needed the portlet to do, and then whether it was doing it correctly. I only really suggested splitting out the columns as something to help understand what was going on - you were describing some confusion between project and task IDs and I just spotted that you were using the same column for the project and task NAMEs (and IDs) and whether that was adding to the confusion. I'm GUESSING really, just trying to make comments that might trigger something with you to help.



  • 11.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-04-2018 09:31 AM

    I appreciate your help.  I have split out the columns for Project and Task names and have no issues with the expander functionality.  I have also added the Task Name to the filtering section, however, I still cannot filter on a task name?  Example below,

    Initial Filtering Results based on a Project ID:

     

    Results after filtering on the Task Name identified by the green arrow:

     

    I wonder if it could be that the P.ID and Task.ID are both being pulled into the same column as you have mentioned?  I guess I can try splitting those out as well.  I'll circle back later today with results.

     

    Thanks



  • 12.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-04-2018 09:37 AM

    I was also wondering why when I export to excel that only the top level results get exported, not the underlying task rows or data below the expander.

     

    Thanks



  • 13.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-04-2018 10:09 AM

    OK - the reason why when entering the task name as a filter is that the filter is applied on all the query, so the project rows (where the task name is blank) don't match the filter so are not returned, and once the query doesn't delivery the project row then there is nothing to deliver the task rows.

     

    I have a feeling that you might get the results you want from using user-provided parameter/filter fields rather than the default ones - i.e. the @WHERE:PARAM:USER_DEF.....@ fields. That way you can "code" into the SQL the logic that says "if this is a project row then ignore the task filter" / "if this is a task row then ignore the project filter", but your (already complex) query is going to get even more complex.

    (this is kinda what I was getting at by talking about building it all up bit-by-bit and making sure each section of the query is delivering what you need it to)



  • 14.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-04-2018 01:40 PM

    Hi David,

     

    I was able to create the user based parameters for project name and task name, however, they are being interpreted as "Exact Text" type's of String Fields?  Do you know how I can get those to be just "Text" type?

     

    here is the code as presented in the queries:

    Project Level:

    AND (@WHERE:PARAM:USER_DEF:STRING:PROJECT_NAME@ = P.NAME OR
            @WHERE:PARAM:USER_DEF:STRING:PROJECT_NAME@ IS NULL)

     

    Task Level:

    AND (@WHERE:PARAM:USER_DEF:STRING:TASK_NAME@ = T.PRNAME OR
           @WHERE:PARAM:USER_DEF:STRING:TASK_NAME@ is null)

     

    How they appear in the portlet filter section.

     

    Any thoughts?

     

    Regards



  • 15.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-05-2018 03:30 AM

    Code it in;

     

    AND (@WHERE:PARAM:USER_DEF:STRING:PROJECT_NAME@ LIKE '%' @+@ P.NAME @+@ '%' OR
            @WHERE:PARAM:USER_DEF:STRING:PROJECT_NAME@ IS NULL)

     

    (or just put "LIKE" and let the user introduce the widcards - think the default application behaviour is just to wildcard the end of the string)

     

    [ * @+@ is just NSQL for + in sqlserver or || in Oracle ]



  • 16.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-05-2018 07:39 AM

    That qualifies as a Tuesday's tip



  • 17.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-05-2018 09:25 AM

    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



  • 18.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-05-2018 09:36 AM

    Yeah I'm not sure that I ever looked at trying to get the 'export to excel' everything for these type of portlets; the hierarchical portlets are not a single query execution and I believe that that 'export to excel' is only ever trying to 'run' a single query.

     

    Perhaps CTRL-P is your friend! 



  • 19.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-08-2018 03:35 AM

    Dave_3.0 wrote:

     

    Code it in;

     

    AND (@WHERE:PARAM:USER_DEF:STRING:PROJECT_NAME@ LIKE '%' @+@ P.NAME @+@ '%' OR
            @WHERE:PARAM:USER_DEF:STRING:PROJECT_NAME@ IS NULL)

     

    D'oh I put that the wrong way around - schoolboy error! Glad you spotted that then! 



  • 20.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-09-2018 08:06 AM

    Hi David,

     

    So when I run data in the new page, I am only able to search on wildcards that match with proper case?  I have read where folks have made the field value Lower(T.PRNAME) and the wildcard the same Lower(TASK_NAME), however, then I would have to let folks know visually on the screen that they need to search in lowercase formatting, not exactly ideal.  Would you have any suggestions as to how I can make the search case "insensitive", for a lack of better word?

     

    Regards



  • 21.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-09-2018 08:35 AM

    You just "code it in";

     

    AND ( UPPER ( P.NAME ) LIKE UPPER ( '%' || @WHERE:PARAM:USER_DEF:STRING:PROJECT_NAME@ || '%' ) OR @WHERE:PARAM:USER_DEF:STRING:PROJECT_NAME@ IS NULL)

     

    ( or LOWER - doesn't matter to the logic of course - you don't need to "let users know" anything, it works either way)



  • 22.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-09-2018 10:54 AM

    I get it now, I should have thought that all the way thru.

     

    Thanks



  • 23.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-09-2018 11:00 AM

    Also...

     

    I am trying to arrange my NSQL to follow the logic of the below code in regards to getting the hg_all_rows = 1:

     

    SELECT @SELECT:DIM:USER_DEF:IMPLIED:D1:dummy.id:ID@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:D1:dummy.name:Name@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:D1:dummy.region:Region@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:D1:dummy.rating:NumberRating@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:D1:dummy.hg_has_children:hg_has_children@
    from (
    select 1 as id, 'USA' as name, null as region, null as rating, 1 as hg_has_children from DUAL union
    select 2, 'Canada', null, null, 2 from DUAL union
    select 3, 'France', null, null, 3 from DUAL union
    select 4, 'Italy', null, null, 4 from DUAL union
    select 5, 'California', 'West', 90, 5 from DUAL union
    select 6, 'New York', 'East', 35, 6 from DUAL union
    select 7, 'Florida', 'Southeast', 51, 7 from DUAL
    ) dummy
    where (@WHERE:PARAM:USER_DEF:INTEGER:hg_all_rows@ = 1
      or (@WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ is null and id <= 4)
      or (@WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ = 1 and id <= 7 and id > 4)
    )
    and @FILTER@

     

    I am able to deploy the above code into our CAPPM environment and I can export all levels into Excel, how can I transfer the same where statement logic above into my NSQL below?

     

    SELECT
    @SELECT:DIM:USER_DEF:IMPLIED:MST:hgquery."ID":id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Name":Name@,
    @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."Owner - Manager":owner_manager@,
    @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 "Project ID",
           "ID",
           "Audit ID",
           "CSOC ID",
           "Tech Risk ID",
           "Name",
    HG_HAS_CHILDREN,
           "Systems Pillar Sponsor",
           "Owner - Manager",
           "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",
                   'PRJ' || P.ID "ID",
                   CP.AUDIT_ID "Audit ID",
                   CP.CSOC_ID "CSOC ID",
                   CP.TECH_RISK_ID "Tech Risk ID",
                   P.NAME "Name",
                   P.ID HG_HAS_CHILDREN,
                   SPS.NAME "Systems Pillar Sponsor",
                   RPM.FULL_NAME "Owner - Manager",
                   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",
                   NULL PRISMILESTONE,
                   NULL 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,
                   
                   (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 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 (@WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ IS NULL OR @WHERE:PARAM:USER_DEF:INTEGER:hg_all_rows@ = 1))

     

    UNION

     

    SELECT "Project ID",
           "ID",
           "Audit ID",
           "CSOC ID",
           "Tech Risk ID",
           "Name",
    HG_HAS_CHILDREN,
           "Systems Pillar Sponsor",
           "Owner - Manager",
           "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",
                   'TSK' || T.PRID "ID",
                   CP.AUDIT_ID "Audit ID",
                   CP.CSOC_ID "CSOC ID",
                   CP.TECH_RISK_ID "Tech Risk ID",
                   T.PRNAME "Name",
                   NULL HG_HAS_CHILDREN,
                   SPS.NAME "Systems Pillar Sponsor",
                   RTO.FULL_NAME "Owner - Manager",
                   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 CP.ID = P.ID(+)
               and P.ID = PR.PRID(+)
               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 (@WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ = P.ID OR @WHERE:PARAM:USER_DEF:INTEGER:hg_all_rows@ = 1)
               )
    ) hgquery
       WHERE 1=1 and @filter@

     

    Thank You & Regards



  • 24.  Re: How do I filter an NSQL Portlet Page?
    Best Answer

    Posted 10-09-2018 11:16 AM

    I don't really understand - the example NSQL you quoted is 'hard-coding' hg_all_rows = 1 for the USA  (because it has the Florida/New York/California sub-levels) and the whole logic in that example is contrived around the contrived data-set it is using as an example in the "dummy" sub-select thing.

     

    For your real query, you need to set hg_all_rows = 1  only where your data has child-records (I think all that hg_all_rows does is put the + expandable icon into the portlet). I'm not trying to debug your code as these things are horrible if you don't understand the functional scenario that you are trying to produce, but the logic as to whether you populate hg_all_rows as '1' or not could just be driven by a simple sub-select/where exists bit of SQL.



  • 25.  Re: How do I filter an NSQL Portlet Page?

    Posted 10-09-2018 02:02 PM

    Hi,

     

    Sometimes the solution is in the details, specifically...

     

    When I read this, I had an ah-ha moment and flipped my screen back to re-code my last where clause bit and BAM!!  I am able to export all records.  I am indebted to the CA Community for all their assistance, especially David.

     

    Regards