Reporting Module - Timesheets Data Provider - Does not show timesheets that have never been opened from the UI

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Steps to Reproduce:
    1. Create a new Resource. Make it available for Time Reporting:
        a. Set Tracking Mode to PPM
        b. Set 'Open for Time Entry' = True
        c. Run 'Load Data Warehouse' full load if instantaneous sync is not enabled in the system
    2. Navigate to Modern UX > Reporting module
    3. Make a copy of the 'Timesheets' Data Provider. Publish it
    4. Create a new 'Report Design'. Choose the Data Provider from above
        a. Choose Layout Format as Fixed
    5. Add a Table widget to the Report Design
        a. Set the Data Provider from above in the Table Properties
        b. Set 'Resource' and 'Full Name' as columns
    6. Filter for the Resource from step 1
    7. Now navigate to the Timesheets module. Open a Timesheet belonging to the Resource from step 1
    8. Run 'Load Data Warehouse' full load if instantaneous sync is not enabled in the system
    9. Navigate back to the Report Design from step 4. Check the data in the table widget.

Expected Results: 
    * Data about the Resource is present in step 6 itself.

Actual Results: 
    * Data about the Resource becomes available only at step 9. It is not visible in step 6.

Environment

16.4.0

Cause

DE176027

Resolution

Updates to this functionality are expected in later releases than the one mentioned above.

In the meanwhile, a new Data Provider can be created that uses the following query to fulfil this requirement. Additional columns that might be needed can be seleted from the tables used in the query. The query uses Postgres syntax. It can be adopted to use other vendor syntax as necessary.

Query:

SELECT
  res.resource_key resource_key,
  res.user_key,
  res.resource_name,
  tp.time_period_key period_key,
  tp.start_date start_date,
  tp.finish_date finish_date,
  ts.timesheet_key prtimesheet_key,
  st.timesheet_status timesheet_status_name
FROM
  dwh_res_resource res
  INNER JOIN dwh_tme_period tp ON COALESCE(
    res.hire_date,
    TO_DATE('01/01/1900', 'MM/DD/YYYY')
  ) < (tp.finish_date - INTERVAL '1 day')
  AND COALESCE(
    res.termination_date,
    TO_DATE('12/31/2100', 'MM/DD/YYYY')
  ) > tp.start_date
  LEFT OUTER JOIN dwh_tme_sheet ts ON res.resource_key = ts.resource_key
  AND tp.time_period_key = ts.time_period_key
  LEFT OUTER JOIN dwh_lkp_timesheet_status st ON coalesce(ts.timesheet_status_key, 0) = st.timesheet_status_key
  AND st.language_code = 'en'
WHERE
  res.is_open_for_te = 1
  AND res.track_mode_key IN (1, 2)