View Only
  • 1.  Advanced Reporting: Jaspersoft

    Posted May 07, 2015 03:54 PM

    Hello Community


    Clarity 14.2

    Oracle Linux Server Release 6.6

    DB Oracle 11G

    CA PPM Jaspersoft Version = 4.7

    JasperSoft Version = JaperReportServer 5.6.1 Enterprise (20140903_1011)



    Advanced Reporting:

    Most reports are working ok. There are some reports failing with the same error message:

    Report Error.jpg


    Error Details



    The server has encountered an error. Please excuse the inconvenience.





    Error Message


    Error filling report


    Error Message


    net.sf.jasperreports.engine.JRException: Error executing SQL statement for : CSK_PRJ_KPIsByProjectType



    Error Message


    java.sql.SQLSyntaxErrorException: ORA-00904: "I"."BUSINESS_ALIGNMENT": invalid identifier



    Reports so far identified as failing are:


    Project Portfolio Summary

    KPIs by Project Type

    Top 50 Project Watchlist

    Project Status Detail



    Thanks for your help and cooperation

  • 2.  Re: Advanced Reporting: Jaspersoft

    Posted May 08, 2015 01:50 AM

    Have you checked the query itself, if it runs fine ? Also, check the "BUSINESS_ALIGNMENT" attribute



  • 3.  Re: Advanced Reporting: Jaspersoft
    Best Answer

    Broadcom Employee
    Posted May 08, 2015 10:15 AM

    Hi GermanK,


    Look to start to look at the problem I would recommend that you try this:


    Step 1:

    In reviewing the query for KPIs by Project Type report, BUSINESS_ALIGNMENT field is coming from dwh_inv_investment, so first of all please check and make sure that attribute exist in that table.


    you will need to install Jasper Studio to be able to open the report in design view to be able to see the query that is being executed. You should be able to download Jasper studio from support site.


    Step 2:

    If is it not there please check under Investment object and go into properties of Business Alignment field to see if “Include in the Data Warehouse” flag maybe be checked off.


  • 4.  Re: Advanced Reporting: Jaspersoft

    Posted May 08, 2015 12:10 PM

    You ROCK Rodrigo!


    That was it.  Business Alignment field was not included in the Data Warehouse. I included it, ran the Load Data WareHouse jobs and the issue is now resolved.

    Thanks a lot for your quick and precise response.

  • 5.  Re: Advanced Reporting: Jaspersoft

    Broadcom Employee
    Posted Sep 30, 2017 01:07 PM

    We had similar problems for one of our customers and it was a known bug where the PMO attribute was mismatched and we had to perform the below steps


    1. Logon to PPM and Navigate to Administration
    2. In Administration --> Objects --> search for investment object
    3. Look for attribute Portfolio Category 1 with datatype Calculated - Number
    4. Rename the attribute name to Business Alignment from Portfolio Category 1
    5.Modify the PPM Schema view: Open dwh_dw_investment_v and modify the field obj_alignment to business_alignment everywhere in the view text.
    7. Save and recompile the view.
    8. Ensure the field Business Alignment in Investment object is checked for DWH
    9. Modify the table DWH_INV_INVESTMENT in DWH Schema by running alter table DWH_INV_INVESTMENT rename column obj_alignment TO BUSINESS_ALIGNMENT;
    10. Run Full Load DWH job