Clarity

 View Only
Expand all | Collapse all

CA PPM Tech Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2+

navzjoshi00

navzjoshi00May 06, 2015 01:41 AM

  • 1.  CA PPM Tech Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2+

    Broadcom Employee
    Posted May 05, 2015 06:17 PM

    After the documentation is reviewed:

     

    https://docops.ca.com/ca-ppm/14-2/reporting/advanced-reporting
    https://docops.ca.com/ca-ppm/14-3/add-ins-and-integrations/content-add-ins/pmo-accelerator/pmo-accelerator-release-notes/import-the-jaspersoft-advanced-reporting-content-for-the-pmo-acceleratorAdvanced Reporting with Jaspersoft - FAQ - CA PPM - 14.2 - CA Technologies Documentation

     

     

    the next step is to validate if Jaspersoft Advanced Reporting works by taking the following steps:

     

    1. Set the Data Warehouse Entity and Language.

    Go to Administration > General Settings > System Options > Data Warehouse Options
    Set the fields:
    a. Language
    b. Entity for Fiscal Time Periods


    2. Make sure the object is included in the Data Warehouse.

     

    Go to admin > object
    Select an object, e.g. project
    and make sure the boolean(include in DWH) is checkmarked.


    3. Review the report/data needed and review the time slices required to populate the data from the PPM to the PPM data warehouse in
    Go to Administration > Time Slices


    4. Check if the required PMO attributes are included in the DWH:

    a. for object(project)

    -Business Owner

    -Estimating Factor 1

    -Estimating Factor 2

    -Finance and Governance

    -Objective

    -Project Category

    -Project Management Office

    -Schedule Variance %

    -Status Reporting

     

    b. for object(investment)

    -Architectural Fit

    -Business Alignment

    -Business Unit Priority

    -Commercial Value

    -Corporate Priority

    -Portfolio Category 1

    -Portfolio Category 2

    -Portfolio Category 3

    -Portfolio Category 4

    -Regulatory Compliance

    -Technology Compliance

    -Work Status


    c. for object(idea)

    -Business Owner

    -Idea Category

    -Idea Type

    -Fast Track

    -Objective

     

    d. for object(status report)

    -Change Explanation

    -Cost and Effort Explanation

    -Cost and Effort Status

    -Deliverable Scope Changed

    -Key Accomplishments

    -Next Milestone on Track

    -Overall Status

    -Project Affected by External Factors? is_project_external_factor ??? or cop_cost_effort_ext ???

    -Project Objective Changed

    -Report Date

    -Report Status

    -Review Approval Problems?

    -Schedule Status

    -Scope Change Required

    -Scope Status

    -Staffing/Availability Issues?

    -Status Report Update

    -Upcoming Activities

    -Variance Explanation

     

    e. for object(Benefit Plan detail)

    -Benefit Class

    -Benefit Subclass

     

    4. Run the necessary jobs.

     

    To provide users access to the Data Warehouse:

    a. Create and Update Jaspersoft Users

    b. Load Data Warehouse Access Rights

     

    to populate the Data Warehouse.
    c. Time Slicing
    d. Load Data Warehouse (Load DWH) with the 'full' option if
    -an incremental Load DWH job fails
    -a new language is added in the data warehouse
    -an entity for fiscal time periods is changed in the data warehouse.

    -a data warehouse time slice is changed to include a larger time frame.

    -an attribute is deleted or unselected from the data warehouse via Administration > Studio.

    -an attribute data type is changed. If it does change, follow the steps:

     

    For each object in question,

    Disable the attribute.

    Go to Administration > Studio >

    Filter for field 'Data Warehouse' = Yes

     

    Go to the Object > Attributes tab

    Select the custom attributes

    Click button 'Exclude from Data Warehouse'


    Run the full Load DWH job.

    Re-enable the attribute data type.
    Go to Administration > Studio >

    Filter for field 'Data Warehouse' = Yes

     

    Go to the Object > Attributes tab

    Select the custom attributes

    Click button 'Include in Data Warehouse'


    5. Run the full Load DWH job.

     

    6. Test Jaspersoft reporting.

    Ensure users have been assigned the one or more of the desired rights:

    Advanced Reporting - Ad Hoc

    Advanced Reporting - Administer

    Advanced Reporting - Dashboard Create

    Advanced Reporting - Data Source Create

    Advanced Reporting - Domain Create

    Advanced Reporting - Navigate

    Advanced Reporting - Report Create

     

    6. Go to Home > Personal > Advanced Reporting
    to run a simple report such as the "Resource Skills" report
    to verify if data is returned.

     

    If you experience an issue with any of the aforementioned steps, please open a case to investigate.

     

    Refer to the following post for job scheduling recommendations with the addition of the new jobs in 14.2

    https://communities.ca.com/thread/241698250



  • 2.  Re: CA PPM Tuesday Tip: Getting Jaspesoft Advanced Reporting to work after upgrading to 14.2

     
    Posted May 05, 2015 06:34 PM

    Thanks for the tip Tuan!

    TUAN TRAN wrote:

     

    To validate if Jaspersoft reporting works, the following steps need to be taken:

     

    1. Make sure the object is included in the Data Warehouse.

     

    Go to admin > object
    Select an object, e.g. project
    and make sure the boolean(include in DWH) is checkmarked.

     

    2. Set the Data Warehouse Entity and Language.

    Go to Administration > General Settings > System Options > Data Warehouse Options
    Set the fields:
    a. Language
    b. Entity for Fiscal Time Periods

     

    3. Run the jobs necessary to populate the Data Warehouse.
    a. Time Slicing
    b. Load Data Warehouse (LDW)
    c. Load Data Warehouse Access Rights (LDWAR)

     

     

    4. Test Jaspersoft reporting.
    Go to Home > Personal > Advanced Reporting



  • 3.  Re: CA PPM Tuesday Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2

    Posted May 06, 2015 01:41 AM

    Thanks for sharing this, Tuan

     

    NJ



  • 4.  Re: CA PPM Tuesday Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2

    Broadcom Employee
    Posted May 06, 2015 02:15 AM

    I think Create and Update Jaspersoft Users is one another important job to be executed for using the Jaspersoft.

     

    Regards,

    Jerin    



  • 5.  Re: CA PPM Tuesday Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2

    Posted May 06, 2015 07:26 AM

    Hi Tuan,

     

    when we try to run Time Slice job it processes for a very long time before failing.

    Last error message showed:

    "

    ERROR 2015-05-04 14:18:20,626 [Dispatch pool-5-thread-2 : bg@SERVER (tenant=clarity)] niku.njs (clarity:admin:199480846__8640E4D4-9645-43CE-AD59-FD93651DB333:Time Slicing) Error executing job: 5821997

    java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver]Object has been closed."

     

     

    Looks like the timeslicing job got stuck and the connection got closed due to inactivity.

    We are trying to run it again and querying every 30 minutes or so with the below:

     

    SELECT 'Assignment' Slice_Object, Count(*), SLICE_STATUS

    FROM prassignment

    WHERE SLICE_STATUS in (1,2,3,4)

    GROUP BY SLICE_STATUS

    UNION SELECT 'Availability' Slice_Object,Count(*), SLICE_STATUS

    FROM prj_resources

    WHERE SLICE_STATUS in (1,2,3,4)

    GROUP BY SLICE_STATUS

    UNION SELECT 'Allocation' Slice_Object, Count(*), SLICE_STATUS

    FROM prteam

    WHERE SLICE_STATUS in (1,2,3,4)

    GROUP BY SLICE_STATUS

    UNION SELECT 'Allocation' Slice_Object, Count(*), HARD_SLICE_STATUS

    FROM prteam

    WHERE HARD_SLICE_STATUS in (1,2,3,4)

    GROUP BY HARD_SLICE_STATUS

    UNION SELECT 'Timeentries' Slice_Object, Count(*), SLICE_STATUS

    FROM prtimeentry

    WHERE SLICE_STATUS in (1,2,3,4)

    GROUP BY SLICE_STATUS

    UNION SELECT 'Costplan Units' Slice_Object, Count(*), odf_ss_units as

    slice_status

    FROM fin_cost_plan_details

    WHERE odf_ss_units in (1,2,3,4)

    GROUP BY odf_ss_units

    UNION SELECT 'Costplan Bill Revenue' Slice_Object, Count(*),

    odf_ss_bill_revenue as slice_status

    FROM fin_cost_plan_details

    WHERE odf_ss_bill_revenue in (1,2,3,4)

    GROUP BY odf_ss_bill_revenue

    UNION SELECT 'Costplan Bill Cost' Slice_Object, Count(*), odf_ss_bill_cost

    as slice_status

    FROM fin_cost_plan_details

    WHERE odf_ss_bill_cost in (1,2,3,4)

    GROUP BY odf_ss_bill_cost

    UNION SELECT 'Costplan Revenue' Slice_Object, Count(*), odf_ss_revenue as

    slice_status

    FROM fin_cost_plan_details

    WHERE odf_ss_revenue in (1,2,3,4)

    GROUP BY odf_ss_revenue

    UNION SELECT 'Costplan Revenue' Slice_Object, Count(*), odf_ss_cost as

    slice_status

    FROM fin_cost_plan_details

    WHERE odf_ss_cost in (1,2,3,4)

    GROUP BY odf_ss_cost

    UNION SELECT 'Assignment Baselines' Slice_Object, Count(*), slice_status as

    slice_status

    FROM PRJ_BASELINE_DETAILS

    WHERE SLICE_STATUS in (1,2,3,4) and object_type='ASSIGNMENT' and

    IS_CURRENT=1

    GROUP BY slice_status

     

     

    So far it looks like it is updating. But from past experience this job will run for a very long time.

    I have a case open but so far have not received any answers. Do you have any ideas as to what we could try or why Time Slicing is not completing successfully?



  • 6.  Re: CA PPM Tuesday Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2

    Broadcom Employee
    Posted May 06, 2015 07:29 AM

    From Installation notes

     

    SQLNET.ora parameters

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    SQLNET.INBOUND_CONNECT_TIMEOUT = 0

    SQLNET.SEND_TIMEOUT = 600 (Applicable for Oracle 11.2.0.4, Oracle Bug 18841764)

     

    Make sure you set the time out accordingly, if its requires more than 600 please set accordingly.

     

    Regards

    Suman Pramanik



  • 7.  Re: CA PPM Tuesday Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2

    Broadcom Employee
    Posted May 06, 2015 10:20 AM

    Hi CMCN1982,

    Based on the errors and behavior surrounding the latest time slicing job instance, it is best to troubleshoot further in that case that is opened since the causes of time slicing job or any job for that matter can be environmental.

     

    -Tuan



  • 8.  Re: CA PPM Tuesday Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2

    Posted May 06, 2015 10:44 AM

    Hi Tuan,

     

    we did some further troubleshooting. We ran the Time Slicing job again and I kept a close eye on the bg-ca.log file.

    Soon we saw the error:

    "ERROR 2015-05-06 07:18:19,336 [Dispatch pool-5-thread-6 : bg@SERVER (tenant=clarity)] niku.blobcrack (clarity:admin:199481040__64A6AB84-EDA6-4B00-95D7-225EE324B3FD:Time Slicing) Bad prAllocSum value.  Object ID = 5266621 Object type = 4"

     

    After this we found the KB article - http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec529770.aspx

     

     

    The error is caused by a corrupt value in the allocation record.

    To locate the allocation record, do the following:

    1. Use the object id in the error message to run the following query:select P.unique_name as project_code , R.UNIQUE_NAME as resource_code from srm_projects P , prteam T, srm_resources R where P.id = T.PRPROJECTID and R.ID = T.PRRESOURCEID and T.PRID = 'enter object id from the error message'e.g. this is the query for the above error:
      select P.unique_name as project_code , R.UNIQUE_NAME as resource_code from srm_projects P , prteam T, srm_resources R where P.id = T.PRPROJECTID and R.ID = T.PRRESOURCEID and T.PRID = 5266621
    2. Use the project_code to locate the project and the resource_code to locate the resource and allocation record.

    3. Check the allocation record for any corrupt values.

      In this case the allocation percentage field had a non numeric value i.e. '~'

     

    4. Update the value to a valid one.

    5. Restart the background (bg) services.

    6. Run the Time Slicing job.

     

    We are now in the process of running Time Slice again so we will see what happens this time.



  • 9.  Re: CA PPM Tuesday Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2

    Posted May 07, 2015 09:17 AM


    Hi Tuan,

     

    In above mentioned screen shot, there is a Dashboards Option in Advance Reporting. In that they said Combined Related Reports to Custom dashboard layout, that means we can add the Jasper report to Project Dashboard Tab as Interactive portlet?

     

    Rajesh BS.



  • 10.  Re: CA PPM Tuesday Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2

    Posted May 11, 2015 09:55 AM

    Hi Tuan,

     

    So I have managed to:

     

    1. Run Time Slice

    2. Run LDWH

    3. Run LDWHAR

    4. Run CUJU

     

    When I go to Home -> Personal -> Advanced Reporting I see an error:

     

    Jaspersoft Error.PNG

     

    and when I click to open the content in a new window:

     

    Jaspersoft Error 2.PNG

     

    Could this be because of my browser? I'm using IE8 to test.



  • 11.  Re: CA PPM Tuesday Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2

    Posted May 11, 2015 11:09 AM

    Getting the same in Chrome.

     

    I also tried admin upload general-config and restarted the services.

    Same error appears.



  • 12.  Re: CA PPM Tuesday Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2

    Broadcom Employee
    Posted May 11, 2015 11:18 AM

    Hi CMCN1982,

    On the IE version being used, can you try the following?

    IE > Tools > Internet Options > Security tab, click "Reset all zones to default"
    Intranet Sites Icon, "Sites" button..... remove any domains from this list.

    Trusted Sites Icon, "Sites" button..... remove the unnamed website from this list if its there.

     

    If this does not work, open up a ticket to get this investigated.



  • 13.  Re: CA PPM Tuesday Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2

    Posted May 11, 2015 12:02 PM

    Thanks Tuan,

     

    I tried that but had no luck so I have opened a case.



  • 14.  Re: CA PPM Tuesday Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2

    Posted May 12, 2015 04:39 AM

    Hi Tuan,

     

    I think I have managed to resolve the issue. I restarted the Tomcat service and now when I go to Home > Personal > Advanced Reporting I see:

     

    Advanced Reporting.PNG

     

    So I am now going to work with our Developers to test all of the new functionality available. Many thanks for your advice and assistance.



  • 15.  Re: CA PPM Tech Tip: Getting Jaspersoft Advanced Reporting to work after upgrading to 14.2+

    Posted Feb 09, 2017 02:31 PM

    Below can be part of Initial Jaseprsoft server setup as it should be done before too many users create their reports.

    Jaspersoft date format options vary between OTB domain vs custom domain, why?