Clarity

 View Only
Expand all | Collapse all

OBS (Units and descendants) Project NSQL Options

  • 1.  OBS (Units and descendants) Project NSQL Options

    Posted Aug 18, 2009 08:37 AM
    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!


  • 2.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Aug 18, 2009 09:09 AM
    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]


  • 3.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Aug 18, 2009 10:10 PM
      |   view attached
    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

    Attachment(s)

    zip
    Project OBS Lookup.zip   376 KB 1 version


  • 4.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Aug 19, 2009 12:04 AM
    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.  


  • 5.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Aug 19, 2009 04:29 AM
    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     


  • 6.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Sep 02, 2009 10:27 AM
    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@  


  • 7.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Sep 02, 2009 01:25 PM
    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@    


  • 8.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Sep 11, 2009 10:25 AM
      |   view attached
    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          

    Attachment(s)

    doc
    Doc1.doc   32 KB 1 version


  • 9.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Sep 11, 2009 03:15 PM
    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.


  • 10.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Sep 13, 2009 03:00 PM
    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 = ???
    */


  • 11.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Sep 14, 2009 08:44 AM
    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


  • 12.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Sep 14, 2009 02:35 PM
    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')    


  • 13.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Sep 02, 2009 08:15 PM
    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.]


  • 14.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Aug 19, 2009 04:43 AM
    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.      


  • 15.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Aug 19, 2009 05:56 AM
    Max,  Understood.Thanks.  regards,sundar


  • 16.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Aug 27, 2009 04:55 AM
    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


  • 17.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Aug 27, 2009 05:17 AM
    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.


  • 18.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Aug 27, 2009 07:13 AM
    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  


  • 19.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Aug 27, 2009 07:27 AM
    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.


  • 20.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Aug 27, 2009 07:46 AM
    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


  • 21.  Re: OBS (Units and descendants) Project NSQL Options

    Posted Aug 27, 2009 07:50 AM


  • 22.  RE: Re: OBS (Units and descendants) Project NSQL Options

    Posted 14 days ago
    AND (@WHERE:PARAM:USER_DEF:INTEGER:OBS_ID@@CASTAS@BIGINT IS NULL OR
    EXISTS (SELECT 1
    FROM PRJ_OBS_UNITS_FLAT OBSF
    INNER JOIN PRJ_OBS_ASSOCIATIONS OBSA ON OBSF.UNIT_ID = OBSA.UNIT_ID
    WHERE OBSA.TABLE_NAME = 'SRM_RESOURCES' AND OBSF.BRANCH_UNIT_ID = @WHERE:PARAM:USER_DEF:INTEGER:OBS_ID@
    AND OBSF.BRANCH_UNIT_ID = CASE WHEN @WHERE:PARAM:USER_DEF:STRING:OBS_MODE@ = 'UNIT_ONLY' THEN OBSF.UNIT_ID ELSE OBSF.BRANCH_UNIT_ID END
    AND OBSA.RECORD_ID = RES_DET.SRMID))


  • 23.  RE: Re: OBS (Units and descendants) Project NSQL Options

    Posted 11 days ago
    Edited by Jason McClellan 8 days ago
    Hi.
    The OBS parameter works as multi-value parameter.

    This filter by resource param OBS:
    ...
    ...
    left join prj_obs_associations ar on ar.RECORD_ID = res.resource_id
    and ar.table_name = 'SRM_RESOURCES'
    left join prj_obs_units uf on uf.unit_id = ar.unit_id
    and (uf.branch_unit_id IS NULL OR @WHERE:PARAM:USER_DEF:INTEGER:uf.branch_unit_id:OBS@)
    ...

    Regards.