Resource Skills Report is not showing any data.

Document ID : KB000031034
Last Modified Date : 14/02/2018
Show Technical Document Details

 

Problem:

There is no data coming into the Resource Skills report. We have verified that the job to Update Report Tables has run with the required parameters. Have not been able to find any other report issue that would cause this.

Steps to Reproduce:

1. Create Skills in Administration, Skills Hierarchy
2. Create some skills with a Skill Name greater than 60 characters
3. Add at least 1 skill to one resource
4. Execute 'Update Business Objects Report Tables' job, selecting the option 'Update Resource Skills Index' (see *Note)
5. Generate 'Resource Skills' Report 
   
Expected Result: The job should complete successfully with a Skill Name greater than 60 characters and the Report should show data.
Actual Result:  The job completes successfully and the table is not populated, therefore when the report is executed it shows a message 'No Data Found'
   
*Note:  In v14.2, the 'Update Business Objects Report Tables' job is still required to be executed for updating the Resource Skills Index for use in the Jaspersoft Reports.  For more information on the impact of this job in v14.2, reference the Advanced Reporting Guide found on the CA PPM Advanced Reporting and Database Schema Index page.  
 

Environment:

Applies to all supported PAS environments for specified releases.

Cause:

Caused by CLRT-78397

The RSM_SKILLS source table defines the SKILL_NAME field with VARCHAR2(240 CHAR).
The RPT_RES_SKILLS_FLAT table defines all the LEVELn_NAME fields as VARCHAR2(60 BYTE)
The stored procedure that populates the RPT_RES_SKILLS_FLAT table encounters an ORA-12899 error message but it does not trigger a failure for the job.
We analyzed the report stored procedure and when you manually run the statement to insert the records into the table it encounters the ORA-12899 error. 

Resolution/Workaround:

Resolved in CA PPM 14.3

Workaround:

Skills Flat table is empty after running job to update report tables.  It is caused by an issue in populating the flat table field size is only 60 characters whereas the source table for the skill name allows more characters.  

Change the Skill Name to have less than 61 characters.

When the job runs to populate the report tables, the stored procedure deletes all existing data in the RPT_RES_SKILLS_FLAT table at the beginning of the job. Therefore, at any time, if a new skill is added with more than 60 characters, after the job runs, the table could be empty. If the issue is encountered again, check for Skill Names with more than 60 characters

Use this query to identify which skills need to be updated

For Oracle:

  select * from RSM_SKILLS where length(skill_name) > 60 ; 

Search for any Skills that are returned from this query and update the Skill Name to be less than 61 characters.  

Additional Information:

Reference CA PPM Resolved Defects Index 

For more information on the changes to the 'Update Business Objects Report Tables' job in v14.3, refer to the Release Notes and Change, Impact and Upgrade Guide.