Clarity Development III (Queries, NSQL)

--- Queries  in Clarity---


NSQL
SELECT field
FROM table *

-How to find the names to use from Clarity.

Table=Table name. Check in Object, Attributes, "Database Table" column
* i.e for custom objects: odf_ca_cop_xxxxxx

Field=Attribute name.
For custom attributes, check in Object, Attributes, "Database Column" column
For default attributes, click on "Attribute" column, and use  "Attribute ID" (in Object Attribute window)
For virtual attributes, check in Object, Attributes, "Database Column" column

-NSQL Query in a Lookup-Query:
Lookup-query supports works with a quite standar SQL syntaxis

i.e
SELECT   @SELECT:my.code:code@,       
         @SELECT:P.PROJECT_CODE:PROJECT_CODE@,       
FROM   odf_ca_myobject my, PAC_MNT_PROJECTS P
WHERE @FILTER@


-NSQL query in QUERY 

Requires the comple NQL syntaxis and at least one dimension definition (SELECT:DIM:)

i.e.
SELECT   @SELECT:DIM:USER_DEF:IMPLIED:Mytest:my.code:code@,       
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:Mytest:my.created_by:created_by@
FROM   odf_ca_myobject my
WHERE  @FILTER@

-Table Prefixes


Prefix,  Table Topic
BIZ,Opportunities
CAL,Events
CAP,Capacity Planning
CLB,Collaboration
CMN,Common tables that span functionality
CMN_RPT,Reporting
CMN_SEC,Security
MR,Methodology
NBI,Datamart
NTD,Discussions
OBS,Organizational Breakdown Structure
PAC,Project Accounting
PPA,Project Billing
PR and PRJ,Projects
RSM,Resource management
SRM,Main objects table that crosses functionality modules
TAX,Tax
XDM,eXtensible Data Model (XDM) for user-defined fields and forms

(added tables)
COP, from PMO Accelerator (not tied to any specific industry)
CATS,  from Best Practises Accelerator (old) (BPA)
ODF,
ODMD, CA Clarity PPM Essentials
PR2, Accelerator PRINCE2 (tied to PRINCE2 from Office of Government Commerce)
PBK, Accelerator PMBOK guide (tied to PMI PMBOK)
CSK, Accelerator Program Management Office (PMO)

TIPS for Naming in Clarity (objects, attributes,...)
qry query
q_ query
lkp lookup
l_ lookup
prt portlet
rpt report
prj project
proc process
res resource
tab tab
obj object
o_ object
pfl portafolio

Clarity development II (power filter, calculated attributes, Processes..)

Calculated attributes & Power Filters  & Process conditions

Calculated attributes

result type: string
   
Concat(string1,string2,...)   

Concat(attribute)

If a lookup attribute (ie. project.goal_code) are not listed in the pulldown try to write id  in formula ;)



DateDiff(start,finish,"Day")

Be careful with syntax because some functions like DateDiff,  internally the result can be negative but the attribute will displays with no minus symbol.!!
!!(If negative it will be displayed in red color and between bracket () symbols)


Boolean output Min(Max(DateDiff(Now(), prfinish,"Day"),0),1)


Calculated attribute+mappi>ng colors 
DateDiff(Now(), prfinish,"Day")
-1 to -100 (red)
0 to 100 (green)
In views, Fields, configure field; Display Elements=Image  



Power Filters
-Power filters are not SQL!
-The Power Filter is currently designed to search for values only. If you leave the field empty (NULL value) that field is not included in the filtering. There is no current functionality that allows the configuration of the power filter to search for NULL or NOT NULL values.

"wildcard" character in Oracle is a percentage "%", but Clarity handles that;
i.e. I enter a "*" in a Clarity LIKE condition and this is converted by Clarity to a "%" when the SQL hits the database.

note: on v7.5.3 on Oracle, both % and * worked as wildcards, since v8 only * has worked

Examples:

project.schedule_finish >= dateMacro("today")+50
like( project.name, "Mi_" ) && notLike( project.name, "PR-" )

Syntax: dateMacro(“period name”)
previousDay
currentDay
nextDay
today
previousWeek
currentWeek
nextWeek
previousMonth
currentMonth
nextMonth
previousQuarter
currentQuarter
nextQuarter
previousYear
currentYear
nextYear


Process Conditions 

Start conditions. to avoid to enter in the process. Include here the conditions required by steps.
To detect a change in on attribute: I.e of formula created by manual writing or generated from forms.

( project.status__oldValue != lookup("INVESTMENT_OBJ_STATUS", 1) && project.status == lookup("INVESTMENT_OBJ_STATUS", 1) ) || ( project.status__oldValue == lookup("INVESTMENT_OBJ_STATUS", 1) && project.status != lookup("INVESTMENT_OBJ_STATUS", 1) )

Same expression (Clarity Evaluated)

( ( ( Project Status [Previous Value] != 'Approved' ) and ( Project Status = 'Approved' ) ) or ( ( Project Status [Previous Value] = 'Approved' ) and ( Project Status != 'Approved' ) ))

Process Notification
Process have default message for notifications, and these messsages can be customized per each process.

Some generic tags:

[processName]
[stepName]
[stepActionName]
[objectName]: [objectInstanceName]

when using "project" object
$[Projects.unique_code]
$[Projects.manager_id]
@[:projmgr.projectDefaultTab!~:~:Click Here]