Clarity PPM1

Expand all | Collapse all

Add an Attribute

Jump to Best Answer
  • 1.  Add an Attribute

    Posted 07-25-2018 01:15 PM

    Hi,

     

    Somebody can tell me How to add an attribute of an object to a query in an nsql

     

    Thanks a lot

     

    Cira



  • 2.  Re: Add an Attribute
    Best Answer

    Posted 07-25-2018 02:59 PM

    See the default NSQL in a new query how attributes are defined.

    SELECT   @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.FULL_NAME:RSRC@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:MR.FULL_NAME:MANAGER@,
             @SELECT:METRIC:USER_DEF:IMPLIED:COUNT(*):PROJECT_COUNT:AGG@
    FROM     INV_INVESTMENTS I,
             SRM_RESOURCES R,
             SRM_RESOURCES MR,
             CMN_SEC_USERS U
    WHERE    I.CREATED_BY = U.ID
    AND      U.ID = R.USER_ID
    AND      R.MANAGER_ID = MR.USER_ID
    AND      @FILTER@
    GROUP BY R.FULL_NAME,
             MR.FULL_NAME
    HAVING   @HAVING_FILTER@

     

    If you want to add an attribute to an existing query with the least hassle add it after the last existing atribute.



  • 3.  Re: Add an Attribute

    Posted 07-25-2018 11:52 PM

    Thank you so much,

    Cira



  • 4.  Re: Add an Attribute

    Posted 07-25-2018 08:26 PM

    Hi Cira,

     

    Please check NSQL manual.

    https://docops.ca.com/ca-ppm/15-4-1/en/reference/ca-ppm-studio-development/ca-ppm-studio-nsql-queries/ 

     

    Above manual says below.

    Defining the Dimension Properties Column

    Once the dimension is defined, all other fields are referred to as dimension properties. The syntax that you use to define the dimension column is the same as the one you use to define the dimension properties column(s) with one exception. Instead of using DIM after the select statement, use DIM_PROP.

    SELECT @SELECT:DIM:USER_DEF:IMPLIED:<Dimension>:<Table.Field>:<label>@      @SELECT:DIM_PROP:USER_DEF:IMPLIED:<Dimension>:<Table.Field>:<label>@

    When defining the Dimension Properties column:

    • Each statement begins and ends with the @ character.
    • Use IMPLIED if the data type does not need to be further quantified (than what can be derived from the database).
    • <Dimension> must be the same Dimension name as for the Dimension column
    • <Table.Field> is the Table or Alias name and field from CA PPM.
    • <label> is the user-defined name or the field that appears in the query.

      SELECT     @SELECT:DIM_PROP:USER_DEF:IMPLIED:<Dimension>:<Table.Field>:<label>@

    Using the sample dimension statement from above, we add the names of the resource and the Manager dimension properties, resulting in the following example:

    SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.ID:RSRC_ID@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.FULL_NAME:RSRC@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:MR.FULL_NAME:MANAGER@

    In the preceding statement, the unique name of the dimension is the same for the three columns. This convention tells the NSQL engine that the three columns belong together.

     

    Regards,
    Shoichi