Clarity PPM

Expand all | Collapse all

Tech Tip : How to add Last Updated By and Last Updated Date fields to Data Warehouse and Jaspersoft Domains

  • 1.  Tech Tip : How to add Last Updated By and Last Updated Date fields to Data Warehouse and Jaspersoft Domains

    Posted 03-28-2016 03:52 PM

    Hello fellow members of the community,

     

     

    As you probably have already seen, those two attributes are not available in the Domains for adding to Ad Hoc Views. this is by design, but there is a workaround you can follow in order to still have this information in your views.

     

    Adding Last Updated By field:
    1. Connect to PPM - Administration - Object
    2. Open the Object needed - go to Attributes
    3. Click on New to create a new attribute
    4. Select Calculated as Data Type, and Result Data Type = String - click Save
    5. Select Build a Calculated Attribute
    6. Use function Concat, attribute name = Last Updated by
    7. Click on Generate
    8. Ensure the expression is valid, hit Save and Return.
    9. Enable the new attribute for Data Warehouse
    10. Run Load Data Warehouse job until completion

     

    Adding Last Updated Date field:

    1. Connect to PPM - Administration - Object
    2. Open the Object needed - go to Attributes
    3. Click on New to create a new attribute
    4. Select Calculated as Data Type, and Result Data Type = Date - click Save
    5. Select Build a Calculated Attribute
    6. Use function DateAdd, Set Argument 1 name = Attribute Name - Last Updated Date
    7. Set Argument 2 to Constant - Second (ensure the case matches)
    8. Set Argument 3 to Constant - 0
    9. Click on Generate
    10. Ensure the expression is valid, hit Save and Return.
    11. Enable the new attribute for Data Warehouse
    12. Run Load Data Warehouse job until completion

     

    You should then be able to find the new field in the Domains and use it in reports.

     

     

    (Please note the screenshot examples were made with Benefit Detail object but any other object can be used with this solution). 

     

    Data fields display in Domains and can be selected for Ad Hoc View:

     



  • 2.  Re: Tech Tip : How to add Last Updated By and Last Updated Date fields to Data Warehouse and Jaspersoft Domains

    Posted 03-28-2016 05:08 PM


  • 3.  Re: Tech Tip : How to add Last Updated By and Last Updated Date fields to Data Warehouse and Jaspersoft Domains

    Posted 03-30-2016 07:49 AM

    Question:

    last updated date is already in the DWH.. is there a reason why this is listed here? I am on premise, working on 14.3

    Could we use this work around to get Created by? Created date is already in there as well.

     

    Thanks

    Lynn



  • 4.  Re: Tech Tip : How to add Last Updated By and Last Updated Date fields to Data Warehouse and Jaspersoft Domains

    Posted 03-30-2016 08:46 AM

    The last_updated_date in DWH is the date when DWH was updated and not the value which is stored in CA PPM.



  • 5.  Re: Tech Tip : How to add Last Updated By and Last Updated Date fields to Data Warehouse and Jaspersoft Domains

    Posted 03-30-2016 12:32 PM

    Hi Lynn,

     

    Yes, on some objects the Last Updated Date is available in DWH, and it corresponds to the object's Last Updated Date. This field is not available on all the objects though.
    In the example I am showing with the screenshots, I am adding the field to object Benefit Plan Detail, where the field is not available for DWH. Hope this helps clarify.

     

    And yes, you can use the same workaround for the Created By field, it should work.


    -Nika