Clarity

I realize there are similar posts on here, but none have been able to set me straight yet, so posting here again.

I've created a view, to simply my writing some Portlets. View is called AllData, and contains Time by Resource, Project, Task, Department, etc.

I've created a Query in Clarity, with the following N-SQL, listed below.

I then added a lookup to the Department parameter for "OBS Filter Browse".

Then I created a portlet around this query. It works perfectly, but if I choose an OBS unit, it only returns the values for that specific unit, and not any units under it. It's my understanding I need to modify my Where statement in this N-SQL, but I'm not sure how it needs changed. Any help?

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."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@
FROM CLARITY.ALLDATA A
WHERE (@WHERE:PARAM:USER_DEF:INTEGER:Dept@ IS NULL OR @WHERE:PARAM:USER_DEF:INTEGER:Dept@ = A."DepartmentID")
AND @FILTER@

You need to include

prj_obs_associations and obs_units_flat_by_mode to get the unit and descendants option

NJ

Something like;

 AND (@WHERE:PARAM:USER_DEF:INTEGER:Dept@ is null
      OR EXISTS ( SELECT 1 FROM prj_obs_associations ASSOC
                          , prj_obs_units_flat FLAT
                      WHERE [color=#ff0000]PRJ[/color].id = 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@
             ))

would work for me.

([color=#ff0000]PRJ[color] being the alias for my table containing the inv_investments.id and my param_Dept attrribute associated to the OBS Projects Filter Browse / OBS_BROWSE_FLT_PRJ lookup)

That's perfect! Got it working. Thanks for the help from all who contributed! Is there a way to mark this question as answered?

shane.steeves wrote:

Is there a way to mark this question as answered?

Only if you marked the original post as a question* - then you will get a "Answer?" box visible against each subsequent post for you (and you alone) to mark as the answer.

(* - you can go back and edit the original post to make it a "question" - there is a little check box you needed to tick after the large "body" text window)