Generating the Report
These two queries generate the "Due Date" report.
The first obtains the information on the current projects that are owned by the specified project manager. The second obtains the current task information for each project.
SELECT name, pdm_blobid, pdm_revid, pdm_entid, creator, due_date
FROM [Project Object Type (PROJECT)], parent_list
WHERE parent_list.child = [Project Object Type (PROJECT)].pdm_blobid AND
[Project Object Type (PROJECT)].pdm_current = 1 AND
[Project Object Type (PROJECT)].pdm_deleted = 0 AND
creator = '[Project Manager(Kelly)]' AND
[Project Object Type (PROJECT)].due_date IS NOT NULL;
SELECT pdm_blobid, task, usnm, due_date, pdm_src_abs_id, pdm_revid, pdm_entid
FROM rhist
WHERE pdm_blobid = :myblobid[I] AND
pdm_src_abs_id = :myentid[I] AND
due_date IS NOT NULL
ORDER BY pdm_revid DESC, pdm_entid DESC;
The second query above is executed for each blobid and entid found by the first query.
Once the data is collected, it is filtered on the task due date greater than or equal to From Date and less than or equal to To Date.
Example:
Due Date Report
| Column label: | Contains the following data: |
|---|---|
| Project | name obtained in the first query |
| Task | task obtained in the second query |
| Team Member | usnm obtained in the second query |
| Task Due Date | due_date obtained in the second query |
| Task Days | calculated using current (today's) date minus due_date |
| Project Due Date | due_date obtained in the first query |
| Project Days | calculated using current (today's) date minus due_date |