Clarity

Good Day Community,

I am tasked with writing a report to track project fluidity over time; month to month at this point.  We want to track a project's stage.  So I am expecting something along the lines of:

      Project Stage                   November (prior month)            December (current month)

        Not Started                                 count of prj                                count of prj

        Intiated                                       

        Planning

        Design & Build

        Implementation

        Closing

My preference is to pull this from the DWH and utilize Jasper reporting tools.  But, I will use the PPM DB if that is what is needed. 

I can see that the "stage" exists at least as a point in time, but I want to pull over months.  Is that available within the database(s) or will I need to start now (to capture December) and build a separate tracking table from a query (like) this one:

          SELECT investment_key, clarity_investment_key, investment_id, investment_name, stage_number,

                         stage_count, stage key, clarity_update_date

          FROM [ppm_dwh].[DWH_INV_INVESTIMENT]

          WHERE investment_type_key = 'project'

          (note, I am aware this may not be complete or correct)

Thank you for your input.

Michael Tognetti

Hi Michael. I haven't seen the new trending capability in 15.3's Data Warehouse yet. It's possible there's a solution here in the new capability.

We timestamp when a project moves between stages with the process engine. We stamp the day an active project goes into a stage, then each night (business day) we 'count up' a days in stage attribute. With this information as 'hard data' in the system you can do just about anything you wish.

https://communities.ca.com/message/241797279?commentID=241797279#comment-241797279 

I agree that the new Trending capability being built is going to be the ideal solution here. But at the moment, with version 15.3, only the Status attributes and Risk related information is available in the Trending tables of DWH.

Hi mtognetti,

Project instance does not have historical data for Stage attribute, so if anyone update Stage value, the new value will overwrite old value. No one can view old Stage value any more. 

But PPM has Audit Trail function. It may support your requirement.

Please consider to define Audit Trail for Project Stage attribute.

ADMINISTRATION->Objects->Select Project object->Audit Trail tab->select Stage attribute for "changes" and "insert" operation.

After configure Audit Trail for Stage attribute, you will find projects on ADMINISTRATION->Audit Trail page as following.

All history data will show until reach to "Days after which audit records will be purged" days.

This page has "Date Range" filter, so you will be able to filter audit trail data for each month.

You also able to access these Audit Trail data by Portlet/NSQL.

Please check CMN_AUDITS table.

I hope this is useful idea for you.

Regards,

Shoichi