Author Topic: Custom reports (Tasks) feature request  (Read 1821 times)

stefan

  • Freshman
  • *
  • Posts: 27
    • View Profile
Custom reports (Tasks) feature request
« on: June 17, 2009, 06:54:21 am »
Hi together,

we are using the custom reporting feature to get a report about the current tasks assigned to a specific user.
What we are missing is the possibility to select a condition "Completed" because now all task, completed and pending, are displayed in the report.
And we would like to see only the pending tasks in the report.

Best regards

Stefan

nexxer

  • Newbie
  • *
  • Posts: 8
    • View Profile
Re: Custom reports (Tasks) feature request
« Reply #1 on: June 23, 2009, 10:23:41 am »
I'll second that.

Right now you have to add a condition for "Completed On" to be less than a day in the past (before you started using Opengoo). This is because the date field will not let you enter 0 or leave it empty, to indicate a task that hasn't been completed.

Showing the Workspace column in Task reports would be useful too.

In case someone finds this useful, what I am using right now to get a customized tasks report is the following query:

SELECT
projects.name AS Project,
IF(milestones.name is not null, milestones.name, '') AS Milestone,
IF(tasksParent.title is not null, tasksParent.title, '') AS ParentTask,
tasks.title AS Task,
users.username AS 'User',
(CASE tasks.priority WHEN 300 THEN 'High' WHEN 200 THEN 'Medium' WHEN 100 THEN 'Low' END) AS Priority,
IF(tasks.due_date <> '0000-00-00 00:00:00', tasks.due_date, '') AS 'Due Date'
FROM opengoo_project_tasks AS tasks
LEFT JOIN opengoo_project_tasks AS tasksParent ON tasks.parent_id = tasksParent.id
LEFT JOIN opengoo_projects AS projects ON tasks.project_id = projects.id
LEFT JOIN opengoo_users AS users ON tasks.assigned_to_user_id = users.id
LEFT JOIN opengoo_project_milestones AS milestones ON tasks.milestone_id = milestones.id
WHERE tasks.completed_by_id = 0 /* incomplete tasks */
AND tasks.project_id NOT IN (SELECT id FROM opengoo_projects WHERE name LIKE '%personal') /* no personal tasks */
AND tasks.priority > 100 /* no low priority */
ORDER BY projects.name ASC, milestones.name ASC, ParentTask ASC, tasks.priority DESC;

Notes:
You'll need to run this directly in your database client program, while having selected the opengoo database.
It won't show low priority or personal workspace tasks.