We have about 150 ish projects set up in PPM - and we have an initial Priority (between 0-4) - but we want to give projects a relative group priority, so each project is ranked (1-200 for example) so we can look at the top 20 projects across all divisions.
However, the above becomes a real pain as everytime we start a new project we have to go into each project and change the priority.
Has anyone done anything similiar, or is there a way to store the values and increase/decrease the values automatically (if i have a new project and give it the relative ranking of 5, then the current project 5 moves to position 6, project in position 6 moves to 7 etc etc)
Instead of giving the projects a direct ranking, you might consider what attributes drive those rankings and just store those. That would let them rank themselves in a portlet.
Actually, this is the idea behind the Portfolio Management scoring key - a project's "score" is a function of whatever risk & value ratings you define. You can of course adjust whatever the computed score is, based on your need or some other criteria, but then the projects essentially rank themselves in the Analyze Current Portfolio page or Current Portfolio Map portlet.
instead of choosing from a list without gaps (1..200) and reordering the lower ranked projects, I would select a relative position and let PPM calculate the rank. So lets start with the first project and assume that there is a maximum of 100.000.000 projects to prioritize. Because there is no other project PPM should assign the priority rank 50.000.000 . On next project we have two choices: Move it in front of project one, which means 25.000.000, or put it at the end of list with 75.000.000 . So every time we prioritize a project, we choose another project we want our project to be in front of. Now PPM or an SQL can calculate the rank by deviding the gap between the selected project and the predecessor into two parts like the following SQL:
SELECT ROUND((priority - NVL((SELECT MAX(parameter1) AS predecessor FROM kcrt_request_details WHERE request_type_id IN (SELECT request_type_id FROM kcrt_request_types WHERE request_type_name = 'xyz') AND parameter1 < a.priority), 0)) / 2) AS priority, 'in front of ' || project_name FROM ( SELECT parameter1 AS priority, visible_parameter2 as project_name FROM kcrt_request_details WHERE request_type_id IN (SELECT request_type_id FROM kcrt_request_types WHERE request_type_name = 'xyz') UNION SELECT '100000000', 'End' FROM dual ) a