Advanced Reporting: Jaspersoft

[Anon Anon]

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:

 

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

[navzjoshi00]

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

 

NJ

[Rodrigo.gregoriodelasheras]

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.

NOTE:

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.

[Anon Anon]

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.

[Suman Pramanik]

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