Clarity

Is there a way to code an NSQL portlet (and corresponding filter) to include the OBS (units, units and descendants) options? We are looking to create a portlet which displays all Issues for a user-selected OBS, and which includes the OBS unit descendants' Issues as well, in the same portlet view.  thanks!

I use this sort of thing (but this does NOT implement the "full functionlity" quite like you describe, it does walk down the units&descendants though)      AND (@WHERE:PARAM:USER_DEF:INTEGER:OBS@ is null
          OR EXISTS ( SELECT 1 FROM prj_obs_associations ASSOC
                                                  , prj_obs_units_flat FLAT
                                          WHERE PRJ.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:OBS@
                        ))     this will create you a PARAM_OBS attribute in your query, associate that with the correct lookup (in this case OBS_BROWSE_FLT_PRJ) and you are OK.  (That example is obviously for PROJECT related OBS and my main query has srm_projects aliased to PRJ.  You can change the   'SRM_PROJECTS' bit and the lookup if its a different object)  --  I thought that there was a simpler NSQL construct as well???.... but I might be imagining that (else I'm sure I would have used it in the portlet I just ripped that code above out of!)    David MortonCapgemini Message Edited by Dave on 18-08-2009 06:10 PM [left]

Hi,  i have reverted back to one Question in the Forum earlier.Just have a look.i developed for custom portlets.  http://caforums.ca.com/ca/board/message?board.id=CAClarityGeneralDiscussion&thread.id=1797  Dave Query looks  much simpler.He is passing the parameter which is available from v 8.0 but in our old version 7.5.2 we don't have that.  regards,sundar

Hi - I don't think that the parameter I am passing is anything special at all (certainly nothing new in v8.0 since that code came from a v7.5.3 system!!!!)  It just ends up as a "normal" P_OBS number parameter in the query.  Dave.  

The NSQL parameter construct IS available in 7.x, 7.5x, 8x, 12x and I  maybe even in good old Niku 6x  (NSQL didn't exist at all before 6)   @WHERE:PARAM:USER_DEF:INTEGER:OBS@    "commence parsing/sustitution" = first  @      "user defined parameter" = WHERE:PARAM:USER_DEF:    "the type of data allowed" = :INTEGER    "name of this parameter" = :OBS    "stop parsing/sustitution" =  second  @       type of data could also be DATE, DECIMAL, STRING  very useful   note: when you introduce parameters of this sort into NSQL, the query will refer to "param_"+given_name   (e.g. param_obs) as the query attribute     

Hi Paul,  Thanks for the tip. I am unable to get the NSQL parser to accept the data as formatted. Your thoughts?   Processing your request...      Error NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'commence parsing/substitution'[left][left]  SELECT @SELECT:DIM:USER_DEF:IMPLIED:ISSUE:I.CODE + ISS.RIM_RISK_ISSUE_CODE:UNIQUE_KEY@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:I.ID:INV_INT_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:I.CODE:INV_CODE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:I.NAME:INV_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:O1.ID:OBS_INT_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:O1.NAME:OBS_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISS.ODF_PK:ISSUE_INT_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISS.RIM_RISK_ISSUE_CODE:ISSUE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISS.NAME:ISSUE_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISS.DESCRIPTION:ISSUE_DESCRIPTION@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:C.NAME:CATEGORY@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISS.STATUS_CODE:STATUS_CODE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:S.NAME:STATUS_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISS.PRIORITY_CODE:PRIORITY_CODE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:PR.NAME:PRIORITY_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:DATEDIFF(DD,NIKU.COP_CALC_FINISH_FCT(ISS.CREATED_DATE), GETDATE()):AGING@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:A.ID:ASSIGNED_TO_INT_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:A.LAST_NAME + ', ' + A.FIRST_NAME:ASSIGNED_TO@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISS.RESOLUTION:RESOLUTION@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISS.CREATED_DATE:CREATED_DATE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISS.CREATED_BY:CREATED_BY_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISS.TARGET_RESOLVE_DATE:TARGET_RESOLVE_DATE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISS.RESOLVED_DATE:RESOLVED_DATE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISS.PARENT_RISK_ISSUE_ID:PARENT_RISK_ISSUE_INT_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISS.P_RISK_ISSUE_NAME:PARENT_RISK_ISSUE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:NOTES.SUBJECT:NOTE_SUBJECT@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:NOTES.DESCRIPTION:NOTE_DESC@
, @SELECT:METRIC:USER_DEF:IMPLIED:DATEDIFF(Y,NIKU.CAL_TRUNC_DATE_FCT(ISS.TARGET_RESOLVE_DATE), NIKU.CAL_TRUNC_DATE_FCT(GETDATE())):SCHEDULE@
FROM NIKU.ODF_ISSUE_V2 ISS (NOLOCK) INNER JOIN NIKU.INV_INVESTMENTS I (NOLOCK) ON ISS.PK_UNIQUE_NAME = I.CODE
INNER JOIN NIKU.INV_PROJECTS P (NOLOCK) ON I.ID = P.PRID
INNER JOIN NIKU.PRJ_OBS_ASSOCIATIONS O (NOLOCK) ON O.RECORD_ID = I.ID AND O.TABLE_NAME = 'SRM_PROJECTS'
INNER JOIN NIKU.PRJ_OBS_UNITS O1 (NOLOCK) ON O1.ID = O.UNIT_ID
INNER JOIN NIKU.PRJ_OBS_TYPES O2 (NOLOCK) ON O1.TYPE_ID = O2.IDWHERE P.IS_TEMPLATE 1
AND (i.is_active 0)
AND @WHERE:PARAM:USER_DEF:INTEGER:OBS@
"commence parsing/sustitution" = first @
"user defined parameter" = WHERE:PARAM:USER_DEF:
"the type of data allowed" = :INTEGER
"name of this parameter" = :OBS
"stop parsing/sustitution" = second @
AND @FILTER@  

WHERE P.IS_TEMPLATE 1
AND (i.is_active 0)
AND @WHERE:PARAM:USER_DEF:INTEGER:OBS@
"commence parsing/sustitution" = first @
"user defined parameter" = WHERE:PARAM:USER_DEF:
"the type of data allowed" = :INTEGER
"name of this parameter" = :OBS
"stop parsing/sustitution" = second @
AND @FILTER@     A: the following 5 lines were meant as a way to explain the parts: not as a code snippet - sorry. so delete this stuff"commence parsing/sustitution" = first @
"user defined parameter" = WHERE:PARAM:USER_DEF:
"the type of data allowed" = :INTEGER
"name of this parameter" = :OBS
"stop parsing/sustitution" = second @      B which reveals the following:

FROM NIKU.ODF_ISSUE_V2 ISS (NOLOCK) INNER JOIN NIKU.INV_INVESTMENTS I (NOLOCK) ON ISS.PK_UNIQUE_NAME = I.CODE
INNER JOIN NIKU.INV_PROJECTS P (NOLOCK) ON I.ID = P.PRID
INNER JOIN NIKU.PRJ_OBS_ASSOCIATIONS O (NOLOCK) ON O.RECORD_ID = I.ID AND O.TABLE_NAME = 'SRM_PROJECTS'
INNER JOIN NIKU.PRJ_OBS_UNITS O1 (NOLOCK) ON O1.ID = O.UNIT_ID
INNER JOIN NIKU.PRJ_OBS_TYPES O2 (NOLOCK) ON O1.TYPE_ID = O2.ID


WHERE P.IS_TEMPLATE 1
AND (i.is_active 0)
AND @WHERE:PARAM:USER_DEF:INTEGER:OBS@
AND @FILTER@


 please than consider this post which demonstrates how to use the @WHERE:PARAM:USER_DEF:INTEGER:OBS@    

Thanks Paul, I am able to get the OBS NSQL construct working fine. I can now include all descendant object instances to appear. However, I still cannot replicate the "units" "units and descendants" OBS filtering. Is this a combination of NSQL code changes and filter parameter settings?  thanks  Mike          

This is true the construct so far assumes you want "Unit and Descenants" - I'll have to come back later on this, unless someone else does in the meantime.

This should allow optional selection by OBS with 'unit only' or 'unit and descendants, it is untested
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:ISSUE:UNIQUE_KEY:UNIQUE_KEY@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:INV_INT_ID:INV_INT_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:INV_CODE:INV_CODE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:INV_NAME:INV_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:OBS_INT_ID:OBS_INT_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:OBS_NAME:OBS_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISSUE_INT_ID:ISSUE_INT_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISSUE_ID:ISSUE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISSUE_NAME:ISSUE_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:ISSUE_DESCRIPTION:ISSUE_DESCRIPTION@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:STATUS_CODE:STATUS_CODE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:PRIORITY_CODE:PRIORITY_CODE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:AGING:AGING@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:RESOLUTION:RESOLUTION@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:CREATED_DATE:CREATED_DATE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:CREATED_BY_NAME:CREATED_BY_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:TARGET_RESOLVE_DATE:TARGET_RESOLVE_DATE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:RESOLVED_DATE:RESOLVED_DATE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:PARENT_RISK_ISSUE_INT_ID:PARENT_RISK_ISSUE_INT_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:PARENT_RISK_ISSUE_ID:PARENT_RISK_ISSUE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ISSUE:SCHEDULE:SCHEDULE@

FROM (
SELECT
I.CODE + ISS.RIM_RISK_ISSUE_CODE UNIQUE_KEY
, I.ID INV_INT_ID
, I.CODE INV_CODE
, I.NAME INV_NAME
, O1.OBS_INT_ID
, O1.OBS_NAME
, ISS.ODF_PK ISSUE_INT_ID
, ISS.RIM_RISK_ISSUE_CODE ISSUE_ID
, ISS.NAME ISSUE_NAME
, ISS.DESCRIPTION ISSUE_DESCRIPTION
, ISS.STATUS_CODE STATUS_CODE
, ISS.PRIORITY_CODE PRIORITY_CODE
, DATEDIFF(DD,NIKU.COP_CALC_FINISH_FCT(ISS.CREATED_DATE), GETDATE()) AGING
, ISS.RESOLUTION RESOLUTION
, ISS.CREATED_DATE CREATED_DATE
, ISS.CREATED_BY CREATED_BY_NAME
, ISS.TARGET_RESOLVE_DATE TARGET_RESOLVE_DATE
, ISS.RESOLVED_DATE RESOLVED_DATE
, ISS.PARENT_RISK_ISSUE_ID PARENT_RISK_ISSUE_INT_ID
, ISS.P_RISK_ISSUE_NAME PARENT_RISK_ISSUE_ID
, DATEDIFF(Y,NIKU.CAL_TRUNC_DATE_FCT(ISS.TARGET_RESOLVE_DATE), NIKU.CAL_TRUNC_DATE_FCT(GETDATE())) SCHEDULE
/* BELOW ARE ALIAS REFERENCES NOT FOUND */
--, C.NAME CATEGORY
--, S.NAME STATUS_NAME
--, PR.NAME PRIORITY_NAME
--, A.ID ASSIGNED_TO_INT_ID
--, A.LAST_NAME + ', ' + A.FIRST_NAME ASSIGNED_TO
--, NOTES.SUBJECT NOTE_SUBJECT
--, NOTES.DESCRIPTION NOTE_DESC
FROM NIKU.ODF_ISSUE_V2 ISS (NOLOCK)
INNER JOIN NIKU.INV_INVESTMENTS I (NOLOCK) ON ISS.PK_UNIQUE_NAME = I.CODE
INNER JOIN NIKU.INV_PROJECTS P (NOLOCK) ON (I.ID = P.PRID AND P.IS_TEMPLATE 1 AND (i.is_active 0))

left join
(
select oass.record_id, ounit.name OBS_NAME, oflat.branch_unit_id OBS_INT_ID
from prj_obs_associations oass
inner join prj_obs_units ounit on (oass.unit_id = ounit.id )
and ounit.TYPE_ID = (
select top 1 type_id from prj_obs_units where id = @WHERE:PARAM:USER_DEF:INTEGER:WHICH_OBS_UNIT@
/*
if the parameter is null then no match is made and the left join allows the overall query to continue
if the parameter is not null, then the ONE related OBS TYPE is considered = no row repitition
*/
)
inner join prj_obs_units_flat oflat on oass.unit_id = oflat.unit_id
and isnull(@WHERE:PARAM:USER_DEF:INTEGER:WHICH_OBS_UNIT@, oass.unit_id) = oflat.branch_unit_id


/*


above: we are using 'ISNULL' as a branching mechanism in a join instruction


if parameter is null join is via field 'oass.unit_id'


if parameter holds a value, join is restricted to only that specific value
*/
and (
case
when @WHERE:PARAM:USER_DEF:STRING:INCL_CHILD@ = '0' then oass.unit_id
else oflat.branch_unit_id
end
) = oflat.branch_unit_id
/*
above: now we are using a case statement to allow or deny 'descendants'
0 (deny) oass unit_id must equal oflat unit_id
1 (allow, & default) oflat unit_id = oflat unit_id (i.e. will always be equal)
here INCL_CHILD parameter is based on a lookup that returns '0' for Unit Only or '1' for Unit and Descendent
*/


where oass.table_name = 'SRM_PROJECTS'



) O1 on I.ID = O1.record_id


) SQL_INNER_BIT
where (
@WHERE:PARAM:USER_DEF:INTEGER:WHICH_OBS_UNIT@ is null

OR @WHERE:PARAM:USER_DEF:INTEGER:WHICH_OBS_UNIT@ = sponsor_obs_id

)
and @filter@
/*
QUERY ID = ???
*/

Thanks Paul!  I am wondering where the last parameter comes from (sponsor_obs_id) ? Does this value reference to another in the query?   OR @WHERE : PARAM : USER_DEF : INTEGER : WHICH_OBS_UNIT@ = sponsor_obs_id )      thanks     Mike

it's a mistake as you have spotted - going back to the .doc I added to this thread I explained the 'sponsor' was simply from my original example (client wanted 'sponsor obs')    

link in my previous post isn't working, so trying again - please go to this link  http://caforums.ca.com/ca/board/message?board.id=CAClarityGeneralDiscussion&message.id=3334#M3334  Please also adopt the practice of seperating SQL from NSQL - it helps....  select@stuff here....from (   -- now your SQL     select field1, field2   from table1     ) middle_is_sql-- back now to nsqlwhere @filter@  [If the linked post solves your problem, please also mark this post as a solution.]

Sundar,  a small warning/tip     SELECT id,parent_id,unique_name,name, depth,type_id FROM PRJ_OBS_UNITS WHERE TYPE_ID = 5000033 Here the code is using a "physical" reference (the number 5000033) which may ONLY work in one database; It might not work after migration to  a test environment or into production.  It's worth noting that when you XOG items from one environment to another those items  can be allocated quite different physical references (which includes OBS's and OBS elements).  The @where....@ approach does not suffer this problem as the physical reference used in the parameter is being established within the  environment whhich is running the query.      

Max,  Understood.Thanks.  regards,sundar

Hi,  i have 2 Questions.  1. Is the parameter(param) passing in nsql is limited to user,project,obs or we can pass any thing like (Issue id, Risk Id,Requistion Id etc).  2. Is there any way i can pass an project id via n-sql lookup to an attribute and show all the sub projects related to that project in Project Properties screen  (May be an sub page / section).we are not using the project Hierarchy -Parent -sub-projects in clarity way,so in the Project-->sub projects filter it will not show the projects.  regards,sundar

Two answers then;  1. The parameter can be "anything" you like - its up to you (in the logic in your NSQL what you do with the parameter).  In my example (post #2 above) I have decided that I am going to use the parameter as an OBS id.   I have decided to associate the parameter with teh OBS lookup and I have coded that bit of NSQL to utilise it as an OBS id.  2. I think for your particular situation you should build a portlet and put it on the Project "Dashboard" tab.   If you look at the URL when you are in that tab you will see that it contains some text like "...projmgr.projectDashboard&id=123456".   The trick here is that you can reference that "id" (which you might notice IS your srm_projects.id field) in NSQL.  Just include some NSQL like ....    AND SRM_PROJECTS.ID = @where:param:xml:integer:/data/id/@value@  in your query for your portlet to reference it - the rest of your quey is up to you of course!   :-)    NB the case of that @ statement is important and the bit that relates to the URL value is the "id" bit in the   "/data/id/" bit - you can reference other values from the URL as well using the same sort of thing.    Dave.

Thanks Dave.   Dave comment:-you can reference other values from the URL as well using the same sort of thing.     so i can pass  any object  (Project,Resource,Task,Issues,Risk,Requistion) values and the user id.   Suppose in a page if there is  2 id's (Combination) needs  to be passed.(Example -- For a project and particular task),so can we define  AND SRM_PROJECTS.ID = @where:param:xml:integer:/data/id/@value@  AND prtask.prid = @where:param:xml:integer:/data/prid/@value@  can we achieve that too..  regards,sundar  

Yes.  The trick is getting the URL formatted right.  The (out of the box) "Project Dashboard" page gives you the "id" ok.  But the power of this really starts to become apparent when you build your own "linkable portlet pages" because you can specify your own values in the URL (it is of source up to you to build a portlet (on your new page) that uses the values in its NSQL and also to build a portlet (somewhere else in the application) which provides the values (through a LINK (in the query) to the new portlet page).   Now you have the tools to create "drill-down" portlets all of your own!   (search in these forums for more information, there have been a number of discussions/examples about this sort of thing before!)    Dave.

Again Thanks Dave.  i will  try  one in my test server,need to build one dynamically(project & resourcewise)  for  showing Timesheet  hours / with cost which  going to be recharged(Based on Resource Availability).so if   the recharge is more/less the PM can advice  the resource to adjust.  regards,sundar

I think this was the post I was thinking of...  http://caforums.ca.com/ca/board/message?board.id=CAClarityGeneralDiscussion&message.id=400