Oracle Linux Server Release 6.6
DB Oracle 11G
CA PPM Jaspersoft Version = 4.7
JasperSoft Version = JaperReportServer 5.6.1 Enterprise (20140903_1011)
Most reports are working ok. There are some reports failing with the same error message:
The server has encountered an error. Please excuse the inconvenience.
Error filling report
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : CSK_PRJ_KPIsByProjectType
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
Have you checked the query itself, if it runs fine ? Also, check the "BUSINESS_ALIGNMENT" attribute
Look to start to look at the problem I would recommend that you try this:
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.
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.
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.
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 Administration2. In Administration --> Objects --> search for investment object3. Look for attribute Portfolio Category 1 with datatype Calculated - Number4. Rename the attribute name to Business Alignment from Portfolio Category 15.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 DWH9. 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