CA PPM Studio NSQL Queries

Before you create a portlet to extract and display data, write a query that defines the data. Leverage the cappm data model and its primary database tables to build NSQL queries and lookups that extract data. Knowledge of SQL is not required to work in Studio, but some familiarity is helpful in setting up and managing data.
ccppmop1551
Before you create a portlet to extract and display data, write a query that defines the data. Leverage the
Clarity PPM
 data model and its primary database tables to build NSQL queries and lookups that extract data. Knowledge of SQL is not required to work in Studio, but some familiarity is helpful in setting up and managing data.
Beginning with CA PPM 15.3, you can define dynamic lookups against the data warehouse and in 15.4, you can now define NSQL queries for portlets against the data warehouse.
Your NSQL queries are read-only operations; they do not change data, they only retrieve it. Anything in the database can be used to call stored procedures. When you define an NSQL query, identify the query segments and designate them as metric values, dimensions, dimension properties, or parameters. All these terms, and the overall query definition process, are described in the following sections.
With NSQL queries in Studio, you can create new portlets that can read and write to the database. Because the queries are not directly associated with a single portlet, the same query can be used to produce data for multiple portlets.
2
NSQL Syntax
The main NSQL keywords are the same as standard SQL keywords.
SELECT
The SELECT statement retrieves column data from tables. NSQL statements fail with an error message when a query statement does not start with @SELECT.
FROM
The FROM clause is a standard SQL statement. It identifies the required tables and includes tables that contain the fields in the SELECT statement lists and any additional required tables.
SELECT @Select:DIM:USER_DEF:IMPLIED:PRJ:P.ID:[email protected], @Select:DIM_PROP:IMPLIED:PRJ:P.Name:[email protected] FROM SRM_PROJECTS P ------------------------------------------------------------- SELECT @Select:DIM:USER_DEF:IMPLIED:PRJ:SRM_PROJECTS.ID:[email protected], @Select:DIM_PROP:IMPLIED:PRJ:SRM_Projects.Start:[email protected], @Select:DIM:USER_DEF:IMPLIED:RES:R.Unique_Name:[email protected], @Select:DIM_PROP:IMPLIED:RES:R.Full_Name:[email protected] FROM SRM_PROJECTS, SRM_RESOURCES, PRTEAM
WHERE
The WHERE statement filters data returned by a query to be used on portlets. NSQL follows the same syntax with one exception, each WHERE statement must contain a @[email protected] parameter.
Select @Select:DIM:USER_DEF:IMPLIED:PRJ:P.ID:[email protected], @Select:DIM_PROP:IMPLIED:PRJ:P.Name:[email protected] FROM SRM_PROJECTS P WHERE @[email protected] AND P.Is_Active=1 ------------------------------------------------------------------------- Select @ Select:DIM:USER_DEF:IMPLIED:PRJ:SRM_PROJECTS.ID:[email protected], @Select:DIM_PROP:IMPLIED:PRJ:SRM_Projects.Start:[email protected], @ Select:DIM:USER_DEF:IMPLIED:RES:R.Unique_Name:[email protected], @Select:DIM_PROP:IMPLIED:RES:R.Full_Name:[email protected] FROM SRM_PROJECTS, SRM_RESOURCES, PRTEAM WHERE @[email protected] AND SRM_PROJECTS.ID=PRTeam.prProjectID AND SRM_RESOURCES.ID=PRTeam.prResourceID
XPATH (XML Parameter) Construct
Use this syntax construct in the WHERE clause to enable a portlet to retrieve a name-value pair from the XML page URL to a user-defined portlet. The XPATH or XML Parameter construct has the following lowercase format:
@where:param:xml:string:/data/id/@[email protected]
This construct can only be used on a specific portlet instance type, not the
General
portlet instance type. This construct expects the internal ID value to come from the URL that is displaying the page that contains the portlet. Therefore, the portlet must be created as a specific portlet instance type, such as the Project object portlet instance type to be placed on one of the pages from the project object. If you create and place a General portlet on a general page, there will not be a specific internal ID value in the URL that displays the page containing the portlet.
The following example shows how an NSQL query would use the XPATH construct. The example assumes a portlet is placed on the Project Dashboard. In the example, ID is the name of the parameter that appears in the Project Dashboard that must be used for this particular query to filter the data.
SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:P.ID:[email protected], @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.UNIQUE_NAME:[email protected], @SELECT:METRIC:USER_DEF:IMPLIED:COUNT(*):TEAM_COUNT:[email protected] FROM SRM_PROJECTS P, PRTEAM T WHERE P.ID = @where:param:xml:string:/data/id/@[email protected] AND P.ID = T.PRPROJECTID AND @[email protected] GROUP BY P.ID, P.UNIQUE_NAME HAVING @[email protected]
When using this construct, identify the name-value pair that you want to retrieve from the URL on the page where the portlet is placed. If you do not specify the correct named parameter, the portlet does not generate the expected result set.
GROUP BY and HAVING
The GROUP BY clause is typically used to combine database records with identical values in a specified field into a single record, usually for the purposes of calculating some sort of aggregate function. For example, to summarize all hours for each role on each project, group data by project and then by role.
HAVING is typically used only when GROUP BY is used and when the purpose is to filter data based on the fields that are being grouped. In NSQL, HAVING is required whenever a GROUP BY metric is used. The syntax for the HAVING statement is @[email protected]
SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:P.PROJECT_CODE:[email protected], @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.PROJECT_NAME:[email protected], @select:metric:user_def:implied:P.ACTUAL_HOURS:Actuals:[email protected] FROM NBI_PROJECT_CURRENT_FACTS P WHERE @[email protected] HAVING @HAVING_FILTER ------------------------------------------------------------------------- SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:C.MANAGER_LAST_NAME:[email protected], @SELECT:METRIC:USER_DEF:IMPLIED:SUM(C.ACT_HOURS):Actuals:[email protected], FROM NBI_PROJECT_CURRENT_FACTS C WHERE C.MANAGER_LAST_NAME is not null AND @[email protected] GROUP BY C.MANAGER_LAST_NAME HAVING @[email protected]
ORDER BY
Use ORDER BY to specify requirements for sorting by one or more attributes. However, do not use an ORDER BY clause on a dynamic query that is used in a lookup. The Browse Window tab for the dynamic query already includes a 
Default Sorting Column
field.
If your query requires nested sorting by more than one field, the following advanced example offers one coding possibility:
SELECT @select:odf_object_code:[email protected], @select:name:[email protected], @select:num_inv:[email protected], @select:sort_string:[email protected] FROM ( SELECT Row_number() OVER ( ORDER BY inv_data_grouped.sort_string) row_num, Count(*) OVER ( partition BY inv_data_grouped.total_invs ) total_count, inv_data_grouped.* from ( select i.odf_object_code , substring(i.NAME,0,4) name , concat(i.odf_object_code, substring(i.NAME,0,4)) sort_string , count(*) num_inv , 'x' total_invs from inv_investments i where 1 = 1 group by i.odf_object_code, substring(i.NAME,0,4) having count(*) > 1 ) inv_data_grouped ) inv_results where 1 = 1 and @[email protected]
User-Defined NSQL Constructs
All parts of the SELECT clause must use special NSQL syntax and be specified with an NSQL @[email protected] construct.
Data Types
The following data types are supported in NSQL. They are valid only as part of the Dimension, Properties, and Metrics columns, and cannot be used as parameters.
  • IMPLIED indicates there is no need to qualify a data type; the database uses what it contains. This data type is allowed only in @SELECT…@ constructs because the NSQL engine can retrieve information about the data type from this location only.
  • MONEY (<currency column>) specifies that the value is a monetary amount. The column alias in parentheses specifies the currency. The currency must also be part of the SELECT statement.
  • STRING specifies a basic string that cannot be manipulated.
  • INTEGER represents a whole number that has no fraction or decimal component. The minimum and maximum limits depend on the capabilities of your database. As an example, use a unique integer data type for an employee ID field.
  • FLOAT represents a decimal or floating-point number (for example, 2.375). The minimum and maximum limits, and the precision of values, depend on the capabilities of your database.
  • DATE represents a date and time value. The default data format and supported conversion functions depend on the capabilities of your database. Typically, the default date-time format is YYYY-MM-DD HH:MM:SS.
Dimensions
A dimension is a grouping of similar data elements from one or more tables. For example, 
Project 
may be one dimension and 
OBS 
or 
Tasks 
could be other dimensions. Dimensions are defined in the SELECT statement using specific syntax. First, you define a key value for the dimension and then you can define the other data elements in the dimension.
Defining the Dimension Column
Each dimension must contain a definition for the dimension column. Typically, this is the table’s primary key, though that may not always be the case. Use the following syntax to define a dimension column:
SELECT @SELECT:DIM:USER_DEF:IMPLIED:<Dimension>:<Table.Field>:<label>@
When defining dimensions:
  • Each statement must begin and end with the @ character.
  • Use IMPLIED if the data type values can be derived from the database and do not need to be further quantified.
  • <Dimension> is a user-defined name such as RES.
  • <Table.Field> is the table or alias name provided by 
    Clarity PPM
    .
  • <label> is a user-defined name or the field that appears in the query.
    Portlets based on a query only work as expected when the dimension is comprised of unique values.
For example:
Select @Select:DIM:USER_DEF:IMPLIED:Project:SRM_PROJECTS.ID:[email protected] Select @Select:DIM:USER_DEF:IMPLIED:PRJ:P.ID:[email protected] Select @Select:DIM:USER_DEF:IMPLIED:MyDim:SRM_Projects.Name:[email protected] Select @Select:DIM:USER_DEF:IMPLIED:PRJ:SRM_PROJECTS.ID:[email protected] @Select:DIM:USER_DEF:IMPLIED:RES:R.Unique_Name:[email protected]
The following statement defines the resource dimension as the full name of the resource:
SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.FULL_NAME:[email protected]
The following construct defines the resource dimension as the primary key of the resource table (SRM_RESOURCES). The dimension is comprised of unique values.
  • R.ID is the actual column expression.
  • RSRC_RD is the alias that the column receives.
The actual SQL for this NSQL expression is as follows:
SELECT R.ID RSRC_ID,
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 
    Clarity 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:[email protected], @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.FULL_NAME:[email protected], @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:MR.FULL_NAME:[email protected]
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.
Metrics Column
A metric column is similar to a dimension property in that it belongs in the SELECT section of the query, but metric column values can be totaled on a grid or displayed as value(s) on the chart. Use the following syntax to define a metric column:
@SELECT:METRIC:USER_DEF:IMPLIED:<Table.Field>:<label>:[email protected]
When defining metric columns:
  • Each statement must begin and end with the “@” character.
  • The keyword <METRIC> must be present. Do not use the dimension name because metrics cross dimensions.
  • Use IMPLIED if the data type does not need to be further quantified (than what can be derived from the database).
  • <Table.Field> is the table or alias name provided by 
    Clarity PPM
    .
  • <label> is a user-defined name or the field that appears in the query.
  • Use agg to allow the metric to be totaled when used in a grid.
For example:
@Select:METRIC:USER_DEF:IMPLIED:NBI_PROJECT_CURRENT_FACTS.ACTUAL_HOURS:hrs:[email protected] @Select:METRIC:USER_DEF:IMPLIED:PRASSIGNMENT.PRACTSUM/3600:Actuals:[email protected], @Select:METRIC:USER_DEF:IMPLIED:PRASSIGNMENT.PRESTSUM/3600:ETC:[email protected] @Select:METRIC:USER_DEF:IMPLIED:Count(*):Project_Count:[email protected]
Specifying a metrics column (or columns) is very similar to specifying a dimension. For example, to add the Project Count (the number of projects this resource has created) metric to the example above:
@SELECT:METRIC:USER_DEF:IMPLIED:COUNT(*):PROJECT_COUNT[:AGG]@
The last segment of the metric SELECT syntax is optional and determines if the column expression uses an aggregation function such as COUNT, AVG, SUM, or is a plain column expression. This is required for Metric Column filters.
If the expression uses an aggregate function, the filter is part of the HAVING clause of the query. Otherwise, it becomes part of the WHERE clause. Adding this to the example presented above produces the following query:
SELECT R.ID RSRC_ID, R.FULL_NAME RSRC, MR.FULL_NAME MANAGER, COUNT(*) PROJECT_COUNT
Parameters
Parameters are substitution variables that you use in a query to pass values. Parameters only appear in the SELECT list and in the WHERE clause. There are two kinds of parameters:
  • User-supplied parameters
    are used as filters in portlets and are either based on the query or are fixed when the portlet is created. User-supplied parameters are specified using the following syntax:
   @SELECT:PARAM:USER_DEF:DATA_TYPE:PARAM_NAME[:ALIAS]@
   or,
   @WHERE:PARAM:USER_DEF:DATA_TYPE:[email protected]
Where:
  DATA_TYPE is the data type for the parameter, and
  PARAM_NAME is the unique identifier for the parameter.
  • Built-in parameters
    , which automatically take their values at run-time based on the current user settings or system context. Built-in parameters are specified using the following syntax:
       @SELECT:PARAM:PARAM_IDENTIFIER[:ALIAS]@
    or,
    @WHERE:PARAM:[email protected]
    Where:
    PARAM_IDENTIFIER is one of the following:
    • USER_ID
    • USER_NAME
    • LANGUAGE
    • LOCALE
Example:
i.xdm_priority = prio.id and prio.language_code = @where:param:[email protected] and i.act_status = s.id and s.language_code = @where:param:[email protected] and i.xdm_impact = imp.id and imp.language_code = @where:param:[email protected] and i.xdm_issue_type = isstype.id and isstype.language_code = @where:param:language
Advanced NSQL Construct Examples
NSQL provides several special constructs to make building reports easier. Constructs are available for the following types of data:
  • OBS
  • Calendar Time
  • Fiscal Time
  • Security
Advanced NSQL constructs are often datamart-specific in nature. The construct uses the following syntax:
@SELECT:DIM:DATA_MART:construct_type:{entity}@
The DATA_MART part of the NSQL construct is required. The construct type can be OBS, CALENDAR_TIME, or FISCAL_TIME. The entity for the OBS type is either PROJECT or RESOURCE.
General Examples
@SELECT:DIM:DATA_MART:OBS:[email protected] @SELECT:DIM:DATA_MART:[email protected] @SELECT:DIM:DATA_MART:[email protected]
OBS Construct
Use the OBS construct with datamart tables to retrieve data at an OBS unit level. In the SELECT statement, specify either a project or a resource OBS. In the WHERE statement, specify the datamart table. Use the following syntax for the OBS construct:
@SELECT:DIM:DATA_MART:OBS:{entity}@, @FROM:DIM:DATA_MART:[email protected] @WHERE:DIM:DATA_MART:OBS:[email protected] @GROUP_BY:DIM:DATA_MART:[email protected]
Set the datamart_table parameter to one of the datamart tables.
OBS Example
SELECT @SELECT:DIM:DATA_MART:OBS:[email protected], @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:P.PROJECT_CODE:[email protected], @select:metric:user_def:implied:Sum(P.ACTUAL_HOURS):Actuals:[email protected], @select:metric:user_def:IMPLIED:Sum(P.ETC_Hours):ETC:[email protected] FROM NBI_PROJECT_CURRENT_FACTS P, @FROM:DIM:DATA_MART:[email protected] WHERE @[email protected] AND @WHERE:DIM:DATA_MART:OBS:[email protected] GROUP BY @GROUP_BY:DIM:DATA_MART:[email protected], P.Project_Code HAVING @[email protected]
Calendar Time
Use the CALENDAR TIME construct with the following datamart time tables to show hours or costs across a time scale:
  • NBI_PM_PT_FACTS
  • NBI_PM_PROJECT_TIME_SUMMARY
  • NBI_RT_FACTS
  • NBI_RESOURCE_TIME_SUMMARY
Use the following syntax for the CALENDAR TIME construct:
@SELECT:DIM:DATA_MART:[email protected] @FROM:DIM:DATA_MART:[email protected] @WHERE:DIM:DATA_MART:CALENDAR_TIME: <Datamart TIME Table>@ @GROUP_BY:DIM:DATA_MART:[email protected]
Calendar Time Example
SELECT @SELECT:DIM:DATA_MART:[email protected], @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:T.PROJECT_ID:[email protected], @select:metric:user_def:implied:Sum(T.ACTUAL_HOURS):Actuals:[email protected] FROM NBI_PM_PT_FACTS T, @FROM:DIM:DATA_MART:[email protected] WHERE @[email protected] AND @WHERE:DIM:DATA_MART:CALENDAR_TIME:[email protected] GROUP BY @GROUP_BY:DIM:DATA_MART:[email protected], T.PROJECT_ID HAVING @[email protected]
Fiscal Time
Use the FISCAL TIME construct when dealing with fiscal periods. Use the following syntax for the FISCAL TIME construct:
@SELECT:DIM:DATA_MART:FISCAL_TIME[:<name>]@ @FROM:DIM:DATA_MART:FISCAL_TIME[:<name>]@ @WHERE:DIM:DATA_MART:FISCAL_TIME:F[:<name>]@ @GROUP_BY:DIM:DATA_MART:FISCAL_TIME[:<name>]@
Fiscal Time Example
SELECT @SELECT:DIM:DATA_MART_TEST:[email protected], @SELECT:METRIC:USER_DEF:IMPLIED:SUM(P.ACT_BILLING):ACT_BILLING:[email protected], @SELECT:METRIC:USER_DEF:IMPLIED:SUM(P.REV_ACT_LABOR):REV_ACT_LABOR:[email protected] FROM NBI_FM_PROJECT_TIME_FACTS P, @FROM:DIM:DATA_MART_TEST:[email protected] WHERE @WHERE:DIM:DATA_MART_TEST:FISCAL_TIME:[email protected] AND @[email protected] GROUP BY @GROUP_BY:DIM:DATA_MART_TEST:[email protected] HAVING @[email protected]
Security
Use the following syntax for the Security construct:
@WHERE:SECURITY:<entity type>:<entity id>@
When defining a Security construct, apply the following guidelines:
  • Each statement begins and ends with the @ character.
  • WHERE, must appear in the WHERE section of the NSQL construct.
  • <entity type> is either PROJECT or RESOURCE.
  • <entity id> is the project or resource ID (for example, SRM_PROJECTS.ID or NBI_PROJECT_CURRENT_FACTS.Project_ID).
  • Projects or resources appear only in a grid or chart when a user has sufficient access rights. To verify the user access rights, use SECURITY in the WHERE clause.
Security Joins
Some entities enforce instance-level security for certain objects. You can slice queries that are based on the security information of the user who executes the query. NSQL provides the following construct:
@WHERE:SECURITY:<entity type>:<entity id>]@
  • <entity type> is either PROJECT or RESOURCE
  • <entity id> is the query expression that represents the primary key of the entity.
For example:
@WHERE:SECURITY:PROJECT:[email protected]
Dynamic NSQL Query Lookups
You can use NSQL to create lookups that dynamically filter portlet data. These lookups fetch data from the database in real time to populate the drop-down or browse lists. Dynamic lookups provide the most up-to-date values possible.
The following example shows a dynamic query that returns a list of resources and filters out all resources with a null user_id value. The result set contains resources with a user account to log in to 
Clarity PPM
.
SELECT @SELECT:r.user_id:[email protected], @SELECT:r.unique_name:[email protected], @SELECT:r.first_name:[email protected], @SELECT:r.last_name:[email protected], @SELECT:r.full_name:[email protected] FROM srm_resources r WHERE r.user_id IS NOT NULL AND @[email protected]
For more information about lookup types, see
Configure Lookups
in the
Administration
section.
BROWSE-ONLY Construct for Dynamic Query Lookups
For dynamic query lookups, browsing works in a specific way. When a record appears in the application, the value does not appear in the lookup field. There is no match between the value stored on the record and the list value in the lookup. The old values are retained in the database table for the instance record. However, if a record with the old value is retrieved and is not seen in the application, clicking the Save button removes the old value from the record.
To retain inactivated values in the record and see them in the application, you must change the lookup query definition. For this case, the definition must include the specific NSQL construct @BROWSE-ONLY. When a user uses the lookup on new records, active values are displayed. And, when an existing record is shown with inactive values, the inactive value remains on the record.
The syntax for this construct is :
@BROWSE-ONLY: /* include SQL statement here to look at active results only */ :[email protected]
The following example shows the BROWSE-ONLY construct with a value included that defines the results that appear:
SELECT ...... FROM ..... WHERE @[email protected] @BROWSE-ONLY: AND IS_ACTIVE = 1 :[email protected]
Create Dynamic Lookups and Export Configurations
If the purpose of a dynamic lookup is to link an object to a unique instance of another object or record, we recommend the following approach:
  • Use the internal id that the 
    Clarity PPM
     environment generates as the hidden key.
  • The column, function, or expression you designate as a unique hidden key cannot exceed 30 characters.
  • Add the user-defined id/unique code as the column UNIQUE_CODE in the query select clause
Include the NSQL query column UNIQUE_CODE in a query to prevent the XOG from exporting the lookup attribute using the literal hidden key value. Instead of the hidden key value, the XOG uses the value of UNIQUE_CODE. Using this value avoids exporting primary key ID values in the XML that are impossible to import into another 
Clarity PPM
 environment.
For example, the following lookup query selects the SRM_RESOURCES.ID as its hidden key.
SELECT @SELECT:r.id:[email protected] ,@SELECT:r.full_name:[email protected] ,@SELECT:r.unique_name:[email protected] ,@SELECT:r.last_name:[email protected] ,@SELECT:r.first_name:[email protected] FROM srm_resources r WHERE 1=1 AND @[email protected]
When the preceding lookup is used, the hidden key is selected and stored as the lookup value. When this lookup query is exported using the XOG, the attribute that is associated with the lookup contains the internal id of the resource. When importing this data into a different environment, the effort can fail. The exported record can even become associated with the wrong target record because the internal id in the target system differs from the source system.
By including the UNIQUE_CODE column in the select clause, the XOG export substitutes the unique user-defined id (SRM_RESOURCES.unique_name) into the attribute in the XOG output.
SELECT @SELECT:r.id:[email protected] ,@SELECT:r.full_name:[email protected] ,@SELECT:r.unique_name:[email protected] ,@SELECT:r.unique_name:[email protected] ,@SELECT:r.last_name:[email protected] ,@SELECT:r.first_name:[email protected] FROM srm_resources r WHERE 1=1 AND @[email protected]
When the data is imported, the 
Clarity PPM
 architecture uses the UNIQUE_CODE value to query the system and store the SRM_RESOURCES.ID for the lookup value.
Hierarchical NSQL Queries
Use a hierarchical query to display parent values that expand to reveal child values that are grouped together in a grid portlet.
The following dimension property can be of any data type but is usually number or string:
hg_has_children
This property must be a unique value for all rows in the dimension or it can be null. A value at runtime for this property signifies that the row has children and the row in the grid shows the [+] expand icon for expanding and collapsing. A NULL value means the row does not have children.
The data type for the following parameter must match the data type of 
hg_has_children
 and indicates the current row:
hg_row_id
When a user clicks the [+] expand icon in the grid, the id of the expanded row is passed into the query as this parameter. The value that is passed is the same value that was previously returned as 
hg_has_children
. The following rules apply when using this parameter:
  • When 
    hg_row_id
     is null, you return only the top-level rows in the hierarchy.
  • When 
    hg_row_id
     has a value, you return only the immediate child rows and no grandchildren or further descendants.
: Investment Parents and Investment Rollup objects do not support custom time-scaled value (TSV) attributes. For example, the investment hierarchy does not support custom TSVs. In Studio, the Investment (Project) Hierarchy Financial Rollup page does not display valid values for custom TSVs. The values all display as zero (0), instead of showing their correct data. The out-of-the-box stock time-varying attributes (for example, Actuals for Labor Resources or ETC for Labor Resources) do display their correct values.
 
Hierarchical Query Filters
Filter values are passed into the hierarchical queries as they are for regular queries. The standard hierarchical grids return all ancestor rows when a leaf row matches the filter criteria. For example, consider this simple hierarchy:
North America   Canada Ontario Toronto
If you filter on name=
Toronto
 the query returns North America, Canada, Ontario, and Toronto, one level at a time. Expand the hierarchy to find the rows that match the filter criteria and view the data in a hierarchical format. You can structure a query to provide similar functionality.
Hierarchical Portlets
This example shows the NSQL query behind the following multidimensional hierarchical grid portlet:
Image showing the NSQL query behind the sample multidimensional hierarchical grid portlet.
Use the following NSQL code for the first dimension that includes the expanding hierarchical entities in the 
red 
box. For example, the user expands a location, then a department, and then sees a list of investments.
select @Select:dim:USER_DEF:IMPLIED:HIER:EDI.ID:[email protected], @Select:dim_prop:USER_DEF:IMPLIED:HIER:EDI.SHORTDESC:[email protected], @Select:dim_prop:USER_DEF:IMPLIED:HIER:[email protected], @Select:dim_prop:USER_DEF:IMPLIED:HIER:EDI.hg_has_children:[email protected],
Use the following NSQL code for the second dimension that includes the row of monthly time periods in the 
blue 
box. For example, 01-2017, 02-2017, and additional months in the format MM-YYYY.
@Select:dim:USER_DEF:IMPLIED:timeperiod:period_key:[email protected], @Select:dim_prop:USER_DEF:IMPLIED:timeperiod:to_char(edi.start_date, 'MM-YYYY'): [email protected],
Use the following NSQL code for the metrics that include the columns in the 
green
 box. For example, Actual (A), Budget (B), and Cost (C).
@Select:metric:user_def:implied:sum(edi.actuals):actuals:[email protected], @Select:metric:user_def:implied:sum(edi.budgets):budgets:[email protected], @Select:metric:user_def:implied:sum(edi.cost_plan):cost_plan:[email protected],
You can also show cost plan data as a percentage:
@Select:metric:user_def:implied:sum(edi.cost_plan):cost_plan:[email protected],
The remaining NSQL code is included to complete the example:
from ( /*** Level 1 Entity ***/ select distinct E.id || 'E' id, E.shortdesc, E.entity, E.id || ' ' hg_has_children, to_char(CT.start_date, 'YYYY-MM') period_key, CT.start_date start_date, z_cp_entity_total(E.id,CT.start_date) cost_plan, 0 cp_pct, z_tx_entity_act(E.id, CT.start_date) actuals, z_bdgt_entity_total(E.id, CT.start_date) budgets from entity E, departments D, ( select first_day(add_months(SYSDATE, -6)) start_date from dual union
Export Construct for Hierarchical Grids
Include the construct 
hg_all_rows
 in an NSQL query to export all the child rows in a hierarchical grid. In normal execution mode, a hierarchical query returns only the rows for the current level of the hierarchy. The current level is typically the top-level rows that are viewed when a user first navigates to the grid. When the user makes a child row the current-level, the query returns only the immediate child rows.
However, when Export to Excel is invoked in a hierarchical grid, the query exports all rows in a flat list. The order of the rows is not specified, but the user receives all rows of the hierarchy in the resulting spreadsheet. To provide data in both use cases, the NSQL query returns different result sets for each one. A special construct named 
hg_all_rows 
is passed into the query to signify which mode the query is to use, normal execution mode or Export to Excel.
The following table explains the possible values for this construct: 
Value
Result
1
The query returns all rows for the export request.
Any other value, including null
The query returns only the rows for the current level, typically top-level rows.
If child rows are selected, only the immediate child rows are returned.
This construct can have a performance impact.
The use of 
hg_all_rows
 usually means that the NSQL query must use a UNION SQL construct where:
  • The first part of the union returns only the current-level rows when 
    hg_all_rows
     is not equal to 1.
  • The second part of the union returns all rows when 
    hg_all_rows
     is equal to 1.
The application does not dictate the specific SQL syntax to achieve this functionality. As the author of the NSQL query, you can use any valid SQL construct to achieve the behavior that you prefer.
Create a Simple NSQL Query
To help you get started, Studio provides query templates for the following types of data:
  • Collaboration
  • Project
  • Productivity
  • Resource
  • Business Intelligence
  • Framework
Each of the query templates specifies typical data elements for that type of query. After you create your query, you can use it to populate data in a portlet. You cannot modify a query that is associated with an object, and you cannot delete a query that has an association with a portlet.
Follow these steps:
  1. Open Administration, and from Studio, click Queries.
  2. Click New.
  3. Complete the following fields:
    • Query Name
      Defines the name of the query.
    • Query ID
      Defines a unique alphanumeric identifier for the query.
    • Content Source
      Specifies where the data for a query originates.
    • Category
      Specifies the general area that a query reports data on.
    • Description
      Defines the purpose of the item being created and provides any relevant information.
  4. Click Save and Continue.
    The NSQL tab appears.
  5. Enter your NSQL statement in the query window.
  6. Click Save and Continue.
    The Attributes tab appears.
  7. Review the data to be included in the query and identify which of the columns can be filtered, which are required, and which can be used as lookups.
  8. Click Continue.
    The Linking tab appears.
  9. To define links to another table, click New and complete the following fields:
    • Name
      Defines the name of the link.
    • Link ID
      Defines a unique alphanumeric identifier for the link.
    • Description
      Defines the purpose of the item being created and provides any relevant information.
    • Action
      Specifies a destination for the link. When the link is selected, additional fields can appear to complete the link definition. Complete the fields as needed.
  10. Save your changes.
  11. Click Return.
If an NSQL query SELECT statement includes too many columns or aggregate functions at runtime, a system error occurs. The total amount of actual data for sorting (plus the aggregates) cannot be greater than the current database block size.
The STRING_LIST NSQL Construct
To offer users multi-select lookup values in the filter of a portlet, use the following construct:
@WHERE:PARAM:USER_DEF:STRING_LIST:[email protected]
Add the the following parameter to the 
WHERE 
clause of the NSQL query:
STRING_LIST  Example 1
In this example, you want to display only the list of projects matching a project name filter. Your project name filter is titled 
project_name_in
.
  1. Click Administration, and from Studio, select Queries.
  2. Click New and create a query titled 
    string_list_test
    .
    SELECT DISTINCT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:inv.id:[email protected], @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:inv.name:[email protected] FROM inv_investments inv WHERE inv.is_active=1 AND @WHERE:SECURITY:PROJECT:[email protected] AND (inv.name in (@WHERE:PARAM:USER_DEF:STRING_LIST:[email protected]) OR @WHERE:PARAM:USER_DEF:STRING_LIST:[email protected] is null) AND @[email protected]
  3. Install a SOAP client such as SOAP UI.
  4. Log into 
    Clarity PPM
     using the SOAP client and note the returned session ID.
  5. Run the following query using the noted session ID:
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:quer="http://www.niku.com/xog/Query"> <soapenv:Header> <quer:Auth> <quer:SessionID>5250602__1B908028-0359-471A-920B-9207E0E8A900</quer:SessionID> </quer:Auth> </soapenv:Header> <soapenv:Body> <quer:Query> <quer:Code>string_list_test</quer:Code> <quer:Filter> <quer:project_name_in>stringlisttest</quer:project_name_in> </quer:Filter> </quer:Query> </soapenv:Body> </soapenv:Envelope>
STRING_LIST  Example 2
This example demonstrates that a typical NSQL portlet filter cannot have a multi-select filter field. Even though the STRING_LIST construct does enable such a field, use a Filter Portlet in order to enable a multi-valued parameter.
  1. Click Administration, and from Studio, select Queries.
  2. Click New and create the following query:
    SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.FULL_NAME:[email protected], @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:MR.FULL_NAME:[email protected], @SELECT:METRIC:USER_DEF:IMPLIED:COUNT(*):PROJECT_COUNT:[email protected] 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 MR.UNIQUE_NAME IN @WHERE:PARAM:USER_DEF:STRING_LIST:[email protected] AND @[email protected] GROUP BY R.FULL_NAME, MR.FULL_NAME HAVING @[email protected]
  3. Execute the following query:
    select * from (select row_number() over ( order by rsrc asc) row_num, count(*) over () num_rows, q.* from ( SELECT R.FULL_NAME RSRC, MR.FULL_NAME MANAGER, COUNT(*) PROJECT_COUNT 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 (MR.UNIQUE_NAME IN (null) OR greatest(null) IS NULL) AND 1=? and 1=1 GROUP BY R.FULL_NAME, MR.FULL_NAME HAVING 1=? ) q) q where q.row_num between ? and ? order by q.row_num setInt(): 1, 1 setInt(): 2, 1 setLong(): 3, 1 setLong(): 4, 20
Row Limit for Studio NSQL Queries
To reduce the demand on server resources, a row limit governor is applied to the NSQL query results in grid portlets, chart portlets, and cases where XOG query tags are used. The governor restricts the number of rows that are processed to 50,000 (MAX_FETCH_LIMIT). The row limit is applied in the following cases:
  • Scenario 1: A portlet is running a single dimension NSQL data provider, and the portlet has an aggregation row defined.
  • Scenario 2: A portlet is running a multidimensional NSQL data provider.
When the system detects Scenario 1, a warning message appears when the row limit is exceeded. The results up to the row limit (including the aggregation row total) are processed. You can enter filter criteria to display the specific rows you want. This action reduces the number of rows in the result.
When the system detects Scenario 2, a warning message appears when the row limit is exceeded. The system does not display any results or aggregation row totals. Because of the nature of multidimensional NSQL queries, the correct result set or aggregation row cannot be determined.
Row Limit for Large Queries with Aggregated Calculations
To reduce your exposure to potential out-of-memory errors for aggregations on large datasets, a row limit governor is applied to stock and custom object portlets. The MAX_FETCH_LIMIT value is applied in the CMN_OPTIONS_VAL table. When aggregation is applied to any stock or custom attributes on a list page, the query ignores pagination and attempts to retrieve all the rows from the database. When the number of rows is larger than 50,000, a memory error can occur. The row limit is applied to reduce the chance of getting this error. We recommend that you do not adjust the limit.
To calculate aggregate totals on large datasets, use a report or summary portlet instead of paging through a standard grid portlet.
NSQL Queries Against the Data Warehouse
As a Studio developer, you can design portlet queries in the application that use the data warehouse as their source. The steps for defining NSQL queries remain the same for database (Niku) or data warehouse sources. The same NSQL constructs, syntax, validation rules, and error messages apply to queries against either data source.
All NSQL constructs that are available when the source database is
Niku
are also available when the source database is set to
Data Warehouse
, with the exception of security. The default security clause (@[email protected]) is not supported with the data warehouse data source. To implement your own security, create your query with all the required WHERE clauses. You can also implement security the same way that it is implemented in reports.
Data warehouse NSQL queries are available for the following portlet types: single dimension grid, two dimension grid, hgrids, drill-down portlets, and graphs. 
Note
: DBLINK is not permitted in data warehouse NSQL queries.
To see this change, perform the following steps after the upgrade:
  1. Log in to Classic PPM 15.4 and navigate to 
    Administration
    Studio
    Queries
    .
  2. Open a query or create one.
  3. Click the 
    NSQL
     tab.
  4. In the 
    Source Database
     field, select 
    Data Warehouse
    . In previous releases, selection was limited to the 
    Niku
     database.
When you create a query in Studio, the application sets the default source database to 
Niku
 and provides a database query template. When you switch the source to 
Data Warehouse
, a data warehouse query template appears. During creation, you can switch back and forth and the corresponding query template appears. After you save the NSQL query, you can still switch the data source; however, the matching query template no longer appears. Your last saved query continues to appear.
As a developer or content administrator, you can also create or update these queries using the XML Open Gateway (XOG). You can import and export portlets and queries against the data warehouse using the XOG or a content package. You can also use the XOG to make structural changes to a query. The same validation rules apply to your queries whether you use Studio or XOG.
: If you do decide to create data warehouse queries or switch existing ones, verify your NSQL query logic, clauses, and statements are written against the correct tables by name in the selected source database.
The following NSQL features are supported:
SELECT Clause
(both single and multi-dimensional)
@SELECT:DIM:USER_DEF_IMPLIED:<DimensionName>:<TableName.Field>:<label>@ @SELECT:DIM_PROP:USER_DEF_IMPLIED:<DimensionName>:<TableName.Field>:<label>@ @SELECT:METRIC:USER_DEF:IMPLIED:<TableName.Field>:<label>:[email protected]
WHERE Clause
@WHERE:PARAM:USER_DEF:<data_type>:<PARAM_NAME>@ @WHERE:PARAM:XML:<data_type>:/data/<filter_option>/@[email protected] @[email protected] @[email protected]
Valid data types are STRING, INTEGER, FLOAT, and DATE.
Built-in Parameters
@WHERE:PARAM:<param_identifier>@
Valid param_identifier can be USER_ID, USER_UID, USER_NAME, LANGUAGE, or LOCALE.
Hierarchical Queries
The hg_has_children parameter is supported:
@WHERE:PARAM:USER_DEF:STRING:[email protected]
Miscellaneous Constructs:
@[email protected] (calling a DB function)
Built-in Database Functions
Security Construct (Not Supported)
The NSQL security construct feature is not supported. You must manually add a security query. For example, to secure investments:
AND EXISTS (SELECT 1 FROM dwh_inv_security_v WHERE user_uid = @WHERE:PARAM:[email protected] AND investment_type_key = 'project' AND investment_key = i.investment_key)
 
NSQL Troubleshooting and Tips
The following errors can occur when you are working with NSQL:
NSQL Error 1
:
This query produced duplicate dimensional data. The results shown here may be invalid or incomplete.
Resolution
: The unique key in the Dimension property cannot contain duplicate values. Verify that the tables joins are correct.
Error when trying to execute the query.
Resolution
  • A field listed in the SELECT or WHERE clause does not specify the table name. Because the field name appears in multiple tables, the table name must precede the field name.
  • A comma appears after the last @SELECT statement. Remove it.
  • A comma appears after the last table listed in the FROM clause. Remove it.
  • Verify the table names are correct.
 
  • Only SELECT statements that specify which rows and columns to fetch from one or more tables are permitted. NSQL statements fail with an error message if a statement does not start with @SELECT. This means that UPDATE, INSERT, and DELETE operations cannot be performed in NSQL.
  • Do not use NSQL for reporting or for stored procedures.
  • When you create queries for use with pie charts and funnel charts, verify that the metric does not contain negative values by filtering all values greater than zero.
  • NSQL adds SQL constructs to the end of the statement for automated filtering and other statements. This can create problems when you use UNION in NSQL. As a workaround, use the @[email protected] in the outer select of an inline view that encapsulates the UNION statement.