Structure

  1. Currently in Spira, the risks are associated at the product (project) level. 
  2. One or more projects are associated as part of the program.
  3. One or more programs are associated as part of the portfolio. 

For example, in the figure below,

  1. Core Services is the portfolio.
  2. It has two programs called Client Projects (shown collapsed) and Sample Program (shown expanded with the projects).
  3. The Sample Program has three projects - Flight Simulation Test, Library Information Systems (Sample) and Sample Application One. 

Project Structure

4. The risks are mentioned in the individual projects as shown below.

Risk Project Association

 

 

 

 

 

The Query

Given below is the query.

select 
   R.PROJECT_ID, PR.NAME as PROJECT_NAME, 
   PR.PROJECT_GROUP_ID, PG.NAME as PROGRAM_NAME, 
   PG.PORTFOLIO_ID, PF.NAME as PORTFOLIO_NAME, 
   R.RISK_ID, R.NAME, 
   R.RISK_PROBABILITY_NAME, R.RISK_IMPACT_NAME, R.RISK_EXPOSURE 
from 
   SpiraTestEntities.R_Risks as R 
left join SpiraTestEntities.R_Projects as PR on 
   R.PROJECT_ID = PR.PROJECT_ID
left join SpiraTestEntities.R_ProjectGroups as PG on 
   PG.PROJECT_GROUP_ID = PR.PROJECT_GROUP_ID
left join SpiraTestEntities.R_Portfolios as PF on 
   PF.PORTFOLIO_ID = PG.PORTFOLIO_ID

 

Explanation

  • In Spira, Program is called as PROJECT_GROUP_ID
  • We bring the risk information from Risks joining with Projects on PROJECT_ID
  • We bring program information from ProjectGroups joining with PROJECT_GROUP_ID
  • We bring portfolio information from Portfolios joining with PORTFOLIO_ID
  • This ESQL query would bring all risks from all projects connected with their programs and portfolios
  • If the data set should be limited to a specific project, then, then a "WHERE" clause must be added as follows at the end of the query above
"where R.PROJECT_ID = ${ProjectId}
  • If the data set should be limited to a specific program, then, a "WHERE" clause must be added as follows at the end of the query above
"where PR.PROJECT_GROUP_ID = ${ProjectGroupId}

  Output

Given below is the partial screenshot of the output

Cross Project Risk Report