Thoughts, ideas and solutions from a few EPM consultants.

Tasklist Reports from the Planning Database - Part 2

In the previous post I showed you how to query the planning database to get a report of the task lists. At the time I figured I was done but a co-worker had other plans and asked me if I could sort it properly. So here ya go, version two so to speak. Just change PLAN1 to your planning schema name. Also you may have noticed the connect by prior, unfortunatily SQL Server does not support that command so this is for Oracle DB only but using the WITH syntax it could be modified to work for Microsoft SQL Server.

select tasklist.object_name as tasklist,
  task.object_name task, form.object_name as form, calc.calc_name, ver.object_name version
inner join PLAN1.HSP_OBJECT task
  on t.TASK_ID = task.OBJECT_ID
inner join PLAN1.HSP_OBJECT tasklist
  on t.TASK_LIST_ID = tasklist.OBJECT_ID
left outer join PLAN1.HSP_OBJECT form
  on t.INT_PROP1 = form.OBJECT_ID
left outer join PLAN1.HSP_FORM formdetail
    on t.INT_PROP1 = formdetail.FORM_ID
left outer join PLAN1.HSP_FORM_CALCS calc
    on formdetail.FORM_ID = calc.FORM_ID
left outer join PLAN1.HSP_OBJECT ver
  on t.INT_PROP2 = ver.OBJECT_ID
connect by prior task.OBJECT_ID = task.PARENT_ID
  start with tasklist.POSITION = task.POSITION