Thoughts, ideas and solutions from a few EPM consultants.

Tasklist Reports from the Planning Database

Have you ever wanted to create a report of all the tasklists and tasks in a planning application. Fortunately there is an easy way. First open the planning application and locate the View menu and locate the Report menu item.

Task List Menu

Next select the task lists that should appear in the report.

Selected Task Lists

Then select the user since task list items can vary by user.

Selected Users

Last generate the CSV or PDF report.

Generate Report

Well that works great since you get the Tasks Lists, Items and Forms, but what if you wanted to find out the rules on those forms as well. Unfortunately there is no easy way to do this from the Planning GUI and performing this task by hand depending on the size of the application could be very tedious. Fortunately Planning is a database application and with a little help from Cameron Lackpour's Blog Post we were able to throw together the following pretty quickly.

select tasklist.object_name as tasklist, task.object_name task, form.object_name as form, calc.calc_name, ver.object_name version
from PLAN1.HSP_TASK t
  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
order by tasklist.object_name, task.object_name, form.object_name, calc.calc_name

Executing this snippet of SQL aganist your Planning database will allow you to see what rules are being used in your Planning application. Keep in mind you will need to change PLAN1 to your planning schema name.

01/28/2015 UPDATE:

It didn't take long for someone to push me to improve upon this report with sorting.