Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

how to display milestones as columns

Highlighted
patrick-sa
Regular Collector

how to display milestones as columns

Hi,
working on 7.5 sp3,the client wants a custom portlet where i display a project as a row and its associated milestones as columns.The milestones are constant for all projects so the client wants to see a similar display as shown below.how do i formulate such an sql query?


project 1 milestone 1 milestone 2
project 2 milestone 1 milestone 2

the query below returns the project and milestones but not in the format required.it displays projects as a row and a milestone as a row.

SELECT master_project_name PROJECT_NAME, PROJECT_NAME MILESTONE_NAME , STATE_NAME STATE_NAME
FROM kdrv_projects_v
where
MILESTONE_FLAG ='Y'
and
PROJECT_TYPE_CODE='TASK'

thanks in advance
6 REPLIES
Sangiliraja_1
Collector

Re: how to display milestones as columns

Hi Patrick,

How many milestones you have.

To have the milestones in columns, one should use natural join. if you still didnt get, send me the no of milestones you are looking for and format. i Can help you in getting the query.

Thanks
Sangiliraja.P
patrick-sa
Regular Collector

Re: how to display milestones as columns

Hi Sangiliraja,

Thanks for offering to help.I have attached the format the client wants.If you can just show me how to get the format i'll add the rest of the details.

Thanks,
Patrick.
Mahen M
Honored Contributor

Re: how to display milestones as columns

Sangiliraja_1
Collector

Re: how to display milestones as columns

HI,

I am not able to see the attachment.

Thanks
Sangiliraja.P
patrick-sa
Regular Collector

Re: how to display milestones as columns

Hi Sangiliraja,

I"ve attached the format plus the query i'm busy working on.i'm tryign to use a nested query and even though its not working 100% it looks like its my best bet.the query is pasted below incase u cant open the attachement.please open the attachement with wordpad.im getting the error ora-01427 on the query so i'm trying to refine the query.



SELECT prj.PROJECT_NAME,

(SELECT '' || ks2.STATUS_NAME || '
' || ts2.SCHED_START_DATE || '
' || ta2.ACT_START_DATE || '
' FROM PM_PROJECTS prj2, PM_WORK_PLANS pwp2, WP_TASKS pt2, WP_TASK_INFO pti2,kcrt_statuses ks2,wp_task_schedule ts2,wp_task_actuals ta2 WHERE prj2.PROJECT_ID = pwp2.PROJECT_ID AND pwp2.WORK_PLAN_ID = pt2.WORK_PLAN_ID AND pt2.TASK_INFO_ID = pti2.TASK_INFO_ID AND prj2.PROJECT_ID = prj.PROJECT_ID AND pti2.NAME = 'Audits') TP_APPROVED,

(SELECT '' || ks3.STATUS_NAME || '
' || ts3.SCHED_START_DATE || '
' || ta3.ACT_START_DATE || '
' FROM PM_PROJECTS prj3, PM_WORK_PLANS pwp3, WP_TASKS pt3, WP_TASK_INFO pti3,kcrt_statuses ks3,wp_task_schedule ts3,wp_task_actuals ta3 WHERE prj3.PROJECT_ID = pwp3.PROJECT_ID AND pwp3.WORK_PLAN_ID = pt3.WORK_PLAN_ID AND pt3.TASK_INFO_ID = pti3.TASK_INFO_ID AND prj3.PROJECT_ID = prj.PROJECT_ID AND pti3.NAME = 'Second Task') SECOND_TASK

FROM PM_PROJECTS prj
patrick-sa
Regular Collector

Re: how to display milestones as columns

hi,

I think i've found the sql to do this but the sql has a logical error.the sql runs fine but doesnt return any values.not sure what i'm missing.please replace pti2.NAME = 'Hand Over to Client Office ') with any milestone in your system and see if it returns any milestones.thanks


select prj.PROJECT_NAME ,

(SELECT '' || l2.MEANING || '
' || ts2.SCHED_START_DATE || '
' || ta2.ACT_START_DATE || '
'
FROM
PM_PROJECTS prj2,KNTA_LOOKUPS l2, PM_WORK_PLANS pwp2, WP_TASKS pt2, WP_TASK_INFO pti2,wp_task_schedule ts2,wp_task_actuals ta2 ,wp_milestones m2
WHERE prj2.PFM_REQUEST_ID = pwp2.PROJECT_ID
AND pwp2.WORK_PLAN_ID = pt2.WORK_PLAN_ID
AND pt2.TASK_INFO_ID = pti2.TASK_INFO_ID
AND prj2.PROJECT_ID = prj.PROJECT_ID
AND m2.MILESTONE_ID=pt2.MILESTONE_ID
AND m2.MAJOR='Y'
AND LOOKUP_TYPE like '%PM - Task Statuses%'
AND l2.LOOKUP_CODE = pti2.STATUS
AND pti2.STATUS in ( 1,2,3,4,5,6,7,8,9,10,11,12,13)
AND pti2.NAME = 'Hand Over to Client Office ') first_milestone ,


(SELECT '' || l3.MEANING || '
' || ts3.SCHED_START_DATE || '
' || ta3.ACT_START_DATE || '
'
FROM
PM_PROJECTS prj3,KNTA_LOOKUPS l3, PM_WORK_PLANS pwp3, WP_TASKS pt3, WP_TASK_INFO pti3,wp_task_schedule ts3,wp_task_actuals ta3 ,wp_milestones m3
WHERE prj3.PFM_REQUEST_ID = pwp3.PROJECT_ID
AND pwp3.WORK_PLAN_ID = pt3.WORK_PLAN_ID
AND pt3.TASK_INFO_ID = pti3.TASK_INFO_ID
AND prj3.PROJECT_ID = prj.PROJECT_ID
AND m3.MILESTONE_ID=pt3.MILESTONE_ID
AND m3.MAJOR='Y'
AND LOOKUP_TYPE like '%PM - Task Statuses%'
AND l3.LOOKUP_CODE = pti3.STATUS
AND pti3.STATUS in ( 1,2,3,4,5,6,7,8,9,10,11,12,13)
AND pti3.NAME = 'Audit') second_milestone










from pm_projects prj
//Add this to "OnDomLoad" event