Multi Select Filters on Portlet

[Anon Anon]

I know these are possible, but with my Portlet, I don't see any options to make my "resource" filter a multi-select filter.

Would it be related to how I've written my NSQL? I'm using the Lookup "Resources For Search" for this filter.


SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:ROWNUM:ROWNUMBER@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."User_ID":UserID@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."Full_Name":FullName@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."ResourceIsActive":ResourceIsActive@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."DepartmentID":DepartmentID@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."Department":Department@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."Project":Project@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."ProjectID":ProjectID@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."ProjectIsActive":ProjectIsActive@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."Task":Task@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."Hours":Hours@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."SliceDate":SliceDate@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."Employee_Type":EmployeeType@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."Time_Sheet_Status":TimeSheetStatus@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."Input_Type":InputType@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."Note":Note@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."Manager_Name":ManagerName@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."PM_Name":PMName@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."System":System@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."CostCode":CostCode@,
@SELECT:METRIC:USER_DEF:IMPLIED:A."Service":Service@
FROM CLARITY.FSS_ALL_DATA A
WHERE (@WHERE:PARAM:USER_DEF:INTEGER:UserID@ IS NULL OR @WHERE:PARAM:USER_DEF:INTEGER:UserID@ = A."User_ID")
AND (@WHERE:PARAM:USER_DEF:STRING:ResourceIsActive@ = 'All' OR @WHERE:PARAM:USER_DEF:String:ResourceIsActive@ = A."ResourceIsActive")
AND (@WHERE:PARAM:USER_DEF:STRING:ProjectIsActive@ = 'All' OR @WHERE:PARAM:USER_DEF:String:ProjectIsActive@ = A."ProjectIsActive")
AND (@WHERE:PARAM:USER_DEF:INTEGER:Dept@ IS NULL OR EXISTS
(SELECT 1 FROM prj_obs_associations ASSOC, prj_obs_units_flat FLAT
WHERE A."srm_resources.id" = ASSOC.record_id AND ASSOC.table_name ='SRM_RESOURCES'AND ASSOC.unit_id = FLAT.unit_id AND FLAT.branch_unit_id = @WHERE:PARAM:USER_DEF:INTEGER:Dept@))
AND @FILTER@

[Anon Anon]

The simplest way to do this is to NOT use a "user parameter" field as the filter (i.e. your @WHERE:PARAM:USER_DEF:INTEGER:UserID@ field, which becomes param_userid in the query attributes), but just return the "user_id" in your list of fields retrieved by the query (i.e. a "normal attribute") and then associate the lookup with that "normal attribute" and put it in your filter section. Clarity should then let you define that as multi-select.

We can get multi-select to work against those param_ type filter fields, but its a bit tortuous to explain how (there are some threads on the board about it so even if you ask how to do that I will just tell you to find those threads!)

--

In general, its much simpler (and better) to see if you can return your "filter field" in the query (realising that you don't want to display it in the final portlet) and then use that field in the filter section - rather than building in user parameter fields (there are use-cases when you just can't do that because of how you want the filter to work (the OBS hierarchical search is an example), but in simple cases this is much easier to build and then maintain since you don't have to write any NSQL logic to support itl)

[Atul.Kunkulol]

Shane

Dave's suggested option is probably the simplest approach which works. Or if you insist on using Multi-select Parameter - try this

AND ((a.user_id IS NULL) OR (@WHERE:PARAM:USER_DEF:INTEGER:a.user_id:user_id@))