Determine which users are not using the default UoM

Products

• Clarity FedRAMP
• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Which users are not using the default Unit of Measurement (UoM) general setting? What is the user’s personalization of the UoM setting? How can this be determined?

Environment

Clarity 16.4.0

Resolution

This information can be determined through a query to the Clarity database. Starting in Clarity 16.4.0 a new "Queries" module will allow you to run these queries through the MUX. These queries can also be run directly against the database as needed. The queries are provided for each database platform:

• PostgreSQL  - This query returns the Unit of Measure personalization setting per user, if they personalized it  

  with u as (
    SELECT p.user_id,
      LOWER(TRIM(p.personalizations::json->>'units')) as units
    FROM cmn_ui_personalizations p
    WHERE p.component = 'general'
  )
  select
  	p.user_id,
  	  r.unique_name as resource_code,
  	  r.full_name,
  	  p.last_updated_date,
  	  CASE
      u.units
      WHEN 'pct' then '% Availability'
      WHEN 'hours' then 'Hours'
      WHEN 'fte' then 'FTE'
      WHEN 'days' then 'Days'
      ELSE u.units
    END as units
  from
  	cmn_ui_personalizations p
    JOIN srm_resources r on
  	r.user_id = p.user_id
    JOIN u on
  	u.user_id = p.user_id
  where
  	p.component = 'general'
    AND u.units in ('hours', 'fte', 'days', 'pct')

• Oracle  - This query returns the Unit of Measure personalization setting per user, if they personalized it 

  SELECT
  	p.user_id,
  	  r.unique_name AS resource_code,
  	  r.full_name,
  	  p.last_updated_date,
  	  CASE
      WHEN u.units = 'pct' THEN '% Availability'
      WHEN u.units = 'hours' THEN 'Hours'
      WHEN u.units = 'fte' THEN 'FTE'
      WHEN u.units = 'days' THEN 'Days'
      ELSE u.units
    END AS units
  FROM
  	cmn_ui_personalizations p
    JOIN srm_resources r ON
  	r.user_id = p.user_id
    CROSS APPLY (
      SELECT LOWER(
          TRIM(
            JSON_VALUE(
              p.personalizations,
              '$.units'
              RETURNING VARCHAR2(20) NULL ON ERROR NULL ON EMPTY
            )
          )
        ) AS units
      FROM dual
    ) u
  WHERE
  	p.component = 'general'
    AND u.units IN ('hours', 'fte', 'days', 'pct')

• MSSQL  - This query returns the Unit of Measure personalization setting per user, if they personalized it 

  SELECT
  	p.user_id,
  	  r.unique_name AS resource_code,
  	  r.full_name,
  	  p.last_updated_date,
  	  CASE
      WHEN u.units = 'pct' THEN '% Availability'
      WHEN u.units = 'hours' THEN 'Hours'
      WHEN u.units = 'fte' THEN 'FTE'
      WHEN u.units = 'days' THEN 'Days'
      ELSE u.units
    END AS units
  FROM
  	cmn_ui_personalizations p
    JOIN srm_resources r ON
  	r.user_id = p.user_id
    CROSS APPLY (
      SELECT LOWER(TRIM(JSON_VALUE(p.personalizations, '$.units'))) AS units
    ) AS u
  WHERE
  	p.component = 'general'
    AND u.units IN ('hours', 'fte', 'days', 'pct')