Extracting Modern UX Unit of Measure (UOM) Preferences from the Database for respective users

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

How to reference Modern UX (MUX) Unit of Measure settings, in the database and generate a SQL to get an extract of these values per user. This is typically required when administrators need to audit whether users are viewing work values in Hours, Days, or FTE

Environment

All valid Clarity PPM releases

Resolution

The following Clarity PPM table: CMN_UI_PERSONALIZATIONS stores information on Modern UX personalizations. The component column acts as a unique identifier for the respective workspace

• The column: component = 'general', refers to Global User Settings (UOM, Decimal Places, Language, and Locale)
• The column: personalizations, referencing the 'general' row this column contains a JSON blob, that shall contain a key named "units"

Refer to the following sample SQL query, this would extract user names and their corresponding Modern UX global personalizations:

SELECT
    u.user_name,
    p.component,
    p.personalizations, 
    p.last_updated_date
FROM
    cmn_ui_personalizations p
JOIN
    cmn_sec_users u ON p.user_id = u.id
WHERE
    p.component ='general'
ORDER BY
    p.last_updated_date DESC;

In the resulting output, the personalizations column shall have data, for example: {"dateFormat":"medium", "units":"fte", "export":{"type": "default"}}

The value associated with "units" (e.g., "hours", "days", or "fte") represents the user's specific UOM preference

 

Additional Information

Clarity now includes the Queries capabilities in the Modern UX under Administration workspace. Refer to the following link