AnsweredAssumed Answered

OBS Department Filter - Need Help Tweaking A Bit...

Question asked by Shane.Steeves on Sep 10, 2013
Latest reply on Sep 11, 2013 by Shane.Steeves
A few days ago I posted for some help on an OBS Filter Lookup, and based on all your posts, I thought i had it. However, I noticed today it isn't pulled back exactly what I expected. I've been trying all morning to correct it, and I think I'm missing something.

I'm attaching a screen shot of my Raw Data, based on a View I've created for simplicity...Notice I have 11 records keyed under my Department, "Financial Services Team", but only one project (circled in red) is assigned directly to my team.

I'm using Lookup "OBS FILTER BROWSE" as my Lookup, so when I select "Financial Services Team", I would expect to see all 11 entries in my portlet. Instead, I'm only seeing the one entry for Project "Finance - KTLO". So it's actually filtering out by Projects associated with my department, when what I really want is all records for my department.

Here is the NSQL I'm using. I think it's something to do with my Department Parameter, but I've been unsuccessful at all attempts to rewrite it. Any help out there?


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."ProjectID" = ASSOC.record_id AND ASSOC.table_name = 'SRM_PROJECTS'
AND ASSOC.unit_id = FLAT.unit_id AND FLAT.branch_unit_id = @WHERE:PARAM:USER_DEF:INTEGER:Dept@))

AND @FILTER@

Attachments

Outcomes