Concepts and differences:
Portlets
Provide information in real time for CA Clarity PPM users, most suitable when your query data contains only one or two dimensions(metrics); from different objects
-Grid portlet. based in data from a dataprovider such as Object, System, Clarity Query (NSQL).
Real only when dataprovider is a Clarity Query (NSQL).
If your grid contains virtual columns, the source data for those columns must be defined in the query that supplies data to the grid.
--A hierarchical grid portlet can be used to show the structure of data that has more than one level. A parent row can have multiple child rows, and the data that shows in the parent row can be an aggregate of the child data.
Lookups
Static List
Dynamic Query. NSQL is supported in Query.
Hidden key * : Lookup Code /Lookup Enum (type to use in database)
* Important to decide which type use before Save.
After choose hiddenkey
ID (enter once+unique): *
* will assigned to previously chosen field in hiddenkey
LOOKUP_CODE is character type on the database,(if chose "enum", it will contains same number)
LOOK_ENUM is numeric type on the database, use when values are numbers,(if chosen "code", it will contains 0)
"Object" not required for a Dynamic query
Parent Window tab
"Hidden Key" is associated to the field in the first SELECT sentence, (value will be stored in the destination attribute and database)
"Display Attribute":(Output field) will display the content of this field in the attribute, after lookup selection.
Notes:
Lookup configuration is reset. After changes in Query, configuration for Display Attribute and Browse window has to be reconfigured. (Avoid surprises with config documentation)
Attribute Lookup(string) and Hidden Key. Hidden key will pass the complete result to attribute but it is limited to 30chars, so a bigger string will give error "Error:Value is too large" (verified in v13.1)
Attribute "Data Type" takes its value when it is associated to a lookup for first time, so it will take the type of lookup output (Parent Window )
Be careful!!! because if you change the lookup output field and it has a different type then the attribute will display an error on the web. Furthermore the "Data type" of a lookup attribute is read only the first association to a lookup, so you need to create a new attribute with the correct "Data type".
How to pass parameters to the query:
- from attribute mapping
Put @WHERE:PARAM:USER_DEF:INTEGER:xxxx@ in the WHERE clause
Create an attribute and associate with previous lookup, press Save
Now, attribute showsin the bottom the section "Lookup Parameter Mappings"
Select an attribute to map to parameter; from the list of attributes available for this object.
*INTEGER or any other Data type.
- from the web address (Xpath)
xxxx=field in the address such as id, projectid, object_id, etc...
value=returns the value after symbol "="
i.e /niku/nu#action:projmgr.projectProperties&id=1234567&classCode=project
@where:param:xml:integer:/data/id/@value@
returns 1234567
Note: be careful with this pameters because the query could not work as expected with other views containg the lookup, since views can have different fields in the web address.
NSQL.
-Comments:
-- line comment
/* */ multiline comment
Must be after the first SQL Sentence, ie
SELECT
-- ver. 1.2 Diciembre2012
@SELECT:resources.id:prid@,
-Read only queries
-UPDATE, INSERT, and DELETE operations cannot be performed in NSQL.
-not recommend using NSQL for reporting or for stored procedures
- Data types: STRING, INTEGER, FLOAT, IMPLIED, FLOAT,DATE, MONEY (in SELECT)
-Dimension Properties Column
@SELECT:DIM:USER_DEF:IMPLIED:
In "Source" select "Dynamic Query", Submit/Save
In "Query" tab, "Query" text box, enter NSQL query, Save
In "Browse Window" click on the "Preview" button
(a window popup with the query results will be showed).
