Clarity

Expand all | Collapse all

CA PPM Portlet Filter - Filters all after selecting distinct values

Jump to Best Answer
  • 1.  CA PPM Portlet Filter - Filters all after selecting distinct values

    Posted 07-20-2017 05:42 PM

    Hi - I am having issues within my portlet filter. I have created a filter (that will pull in distinct quarters). When I go to filter within the portlet, instead of using the filter choices I selected, it defaults to all and pulls in all values instead of what I want.

    Screenshots are below:

    Here is how it looks when I browse

    Here is how it looks when I select multiple items:

    After I hit add it only displays 1 item

    But after I hit Filter it will display everything

     

    Lookup code:

    SELECT DISTINCT  @Select:ODF_OBJECT_CODE:OBJ_CODE@,       
            @SELECT:Case when(C_SLOTTED_DATE is not null)
           then Concat(Concat(TO_CHAR(C_SLOTTED_DATE,'YYYY'),' Q'), TO_CHAR(C_SLOTTED_DATE,'Q')) else null end:Quarter_Display@
    From ODF_CA_INV
    Where ODF_Object_Code = 'project'
         AND @FILTER@

     

    Initially I had it as follows, but because the ID field is unique it pulled back in numerous entries for the same quarter.

    SELECT DISTINCT  @Select:ID:OBJ_CODE@,       
            @SELECT:Case when(C_SLOTTED_DATE is not null)
           then Concat(Concat(TO_CHAR(C_SLOTTED_DATE,'YYYY'),' Q'), TO_CHAR(C_SLOTTED_DATE,'Q')) else null end:Quarter_Display@
    From ODF_CA_INV
    Where ODF_Object_Code = 'project'
         AND @FILTER@

     

    Would someone be able to provide some guidance?

     

    Thanks!

    Greg



  • 2.  Re: CA PPM Portlet Filter - Filters all after selecting distinct values

    Posted 07-21-2017 02:35 AM

    The first thing that comes into my mind is that if if setting filter values does not do anything then that filter field is not in the query as a field nor  a a condition.



  • 3.  Re: CA PPM Portlet Filter - Filters all after selecting distinct values

    Posted 07-21-2017 09:48 AM

    Hi Urmas - So values are being pulled back in. It's just that the filter auto-sets to selecting everything rather than what my selection was.



  • 4.  Re: CA PPM Portlet Filter - Filters all after selecting distinct values

    Posted 07-21-2017 09:55 AM

    Your lookup returns "project" as the first column for every single record - presumably this is the hidden key in the lookup so when control is passed back to the portlet, one would assume that the application code is just highlighting the entries in the lookup that matches that hidden key - i.e. everything.

     

    Make the lookup return something unique (the quarter perhaps?).



  • 5.  Re: CA PPM Portlet Filter - Filters all after selecting distinct values

    Posted 07-21-2017 11:39 AM

    Thanks for the response Dave. So are you saying have the quarter (which is a case statement) as both the hidden key and the display attribute? I've tried that but it does not return any values.

     

    Here are the values within the table (only ID / ODF_OBJECT_CODE) have a value for each entry (excluding the created / updated date values).

     

    ID
    ODF_OBJECT_CODE
    CREATED_DATE
    CREATED_BY
    LAST_UPDATED_DATE
    LAST_UPDATED_BY
    ODF_PERIOD_TYPE
    ODF_PERIOD_START
    ODF_PERIOD_END
    ODF_ASR_TEMPLATE
    ODF_ASR_REPORTS
    OBJ_ALIGN_FACTOR1
    OBJ_ALIGN_FACTOR2
    OBJ_ALIGN_FACTOR3
    OBJ_ALIGN_FACTOR4
    OBJ_ALIGN_FACTOR5
    OBJ_ALIGN_FACTOR6
    OBJ_PFL_CATEGORY1
    OBJ_PFL_CATEGORY2
    OBJ_PFL_CATEGORY3
    OBJ_PFL_CATEGORY4
    OBJ_WORK_STATUS
    C_SUPPORTING_DOCS
    C_FINANCIAL_MODEL
    C_CONTRACT_SIGN
    C_REQUESTED_START
    C_PROJECT_SITE
    C_OPPORTUNITY_ID
    C_CONTRACT_NUM
    C_FIN_PENALTIES
    C_NEW_IP
    C_WORKFLOW_IMPACT
    C_CLIENT_COMMITMENT
    C_CLIENT_OBLIGATION
    C_LINK
    C_CLIENT
    C_PSR_ID
    C_BASE_RELEASE
    C_TARGET_RELEASE
    C_STAFFING_STATUS
    C_STAFF_PRIORITY
    C_PD_IP_GROUP
    C_PD_LIFECYCLE
    C_PD_EXEC_SPONSOR
    C_PD_CSRM_TYPE
    C_PD_CSRM_STATUS
    C_CLIENT_IDEA_LINK
    C_EPIC_ISSUE_ID
    C_ADDITONAL_INFO
    C_UNREIMB_TRAVEL
    C_SLOTTED_DATE
    C_VALUE_PROP
    C_REGION
    C_SEGMENT
    C_SALES_STAGE
    C_DEAL_TYPE
    C_DEAL_RV_STS
    C_EST_SIGN
    C_DEAL_RV_BOOK
    C_TRACKER_ID
    C_ESCALATED_PROJECT
    PPM_PAGEURL
    PPM_PROJECTTYPE
    PPM_URL
    C_DEAL_SUB_TYPE
    C_TRAVEL_CAP_TEMP
    C_TRAVEL_CAP



  • 6.  Re: CA PPM Portlet Filter - Filters all after selecting distinct values

    Posted 07-21-2017 11:56 AM

    Ummm, I'm really saying that I have no idea what your lookup/portlet combination is trying to do, but I think that the reason that you get all the possible values "selected" in the filter is probably just because the value that you have chosen from the lookup matches all the possible values.

     

    The key bit that you are not telling us (and I don't particularly need/want to know as I hope you can work it out for yourself!) is what the portlet is trying to filter upon ; what filter criteria matches what data in the portlet - your lookup needs to deliver that matching value (at the moment its delivering 'project') against either a dedicated parameter attribute in the NSQL that drives the portlet (if it is even NSQL-based?) or the filter field needs to be a relevant column of data from the portlet's dataset.

     



  • 7.  Re: CA PPM Portlet Filter - Filters all after selecting distinct values

    Posted 07-21-2017 02:41 PM

    Thanks David. I'll continue to work on it.

     

    The filter reads off of the C_Slotted_Date which is in the dataset, but I want it to go back quarter instead of single date for trending purposes (hence the case statement in the look_up).



  • 8.  Re: CA PPM Portlet Filter - Filters all after selecting distinct values
    Best Answer

    Posted 07-21-2017 03:10 PM

    So you would either need the NSQL in the portlet to also return the calculated 'quarter' attribute based on your case statement and then use that in the filter, OR use a parameter in the NSQL's where-clause to select data (using the same "logic" as in your case statement).

    But if your portlet is object based, then you can't do what you want - just implement a filter on the c_slotted_date and render it as a date range (but the user will have to enter the range manually)



  • 9.  Re: CA PPM Portlet Filter - Filters all after selecting distinct values

    Posted 07-25-2017 04:59 PM

    Was able to get this fixed, appears there was an issue within our environment which has since been corrected. Thank you for the help!