Clarity: How can we create a power filter expression to select projects that have an ID value exactly 7 characters in length?

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Description:

The Power Filter Expression Builder does not provide a method to determine the length of a field.
A Calculated Attribute on the Project Object does not provide a method to determine the length of a field.
However an NSQL Query can be created to determine the length of a value contained in any attribute from the database.

Solution:

Here is an example query that can be used to accomplish this need:

Enter the following NSQL Code in a user-defined Studio Query:

/* Example for Oracle */
SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:I.CODE:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:LENGTH(I.CODE):[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:I.NAME:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:I.ODF_OBJECT_CODE:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:I.IS_ACTIVE:[email protected]
FROM INV_INVESTMENTS I
WHERE @[email protected]

Save the Query and create a Portlet to display the columns and to include the 'INV_ID_LEN' field in the Filter Section.
Now you can filter for projects with any specific length using the filter on the user-defined portlet.

Keywords: CLARITYKB, how-to, studio, configuration, tips, portlet, queries

Environment

Release:
Component: STUDIO