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
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_INVWhere 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_INVWhere ODF_Object_Code = 'project' AND @FILTER@
Would someone be able to provide some guidance?
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.
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.
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?).
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).
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.
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).
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)
Was able to get this fixed, appears there was an issue within our environment which has since been corrected. Thank you for the help!