CA PPM Portlet Filter - Filters all after selecting distinct values

[Legacy User]

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

[Deactivated User]

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.

[Legacy User]

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.

[David Morton]

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?).

[Legacy User]

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

[David Morton]

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.

 

[Legacy User]

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).

[David Morton]

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)

[Legacy User]

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