Project Open 3.5.x

Using Reports to Extend Project Open’s UI

I like Project Open’s User Interface (UI). The tabbed metaphor keeps the functions organized. I can find what I’m looking for quickly.

Well, most of the time.

Every once and a while, there’s a particular screen or function that I’d really like, but that Project Open doesn’t have. For example, I’d like a screen that shows all open items regardless of their stage in the workflow engine. I can barely program in TCL, Project Open’s native language, and I really don’t want to build or maintain any kind of separate program to display Project Open data.

Fortunately, Project Open’s own flexibility gives me the tools I need to extend the UI using the custom report system.

In case you’ve never used this feature, you can easily create your own Project Open report (if you know enough about the database schema). Just click on the Reporting tab and click on the New Report URL. You’ll get a screen that looks like the one in Figure 1.


Figure 1: Creating a report is easy — if you know the schema!
To use our example of listing open workflow tickets by assignee, I could fill out the fields like this:

  1. Name: I could call the report Tickets by Assignee (Raw)
  2. Report Code: I generally convert the Name to all lowercase and replace the spaces with underlines: tickets_by_assignee_raw
  3. Report Group: Project Open supplies this list, and for our example, I selected Other
  4. Sort Order: If I wanted to control the sort order, I could enter a value here; I generally leave it blank
  5. Description: This can be anything you want, like “This shows how to make a basic report”

The last field, Report SQL, is also the most difficult. Here’s what I put together for my ticket assignee report:


SELECT persons.first_names || ' ' || persons.last_name AS assignee,
wf_workflows.description AS workflow, wf_tasks.transition_key AS workflowstep,
im_category_from_id(im_tickets.ticket_status_id) AS thestatus,
im_tickets.ticket_id, im_projects.project_name, im_tickets.ticket_description
FROM im_tickets
JOIN wf_cases ON wf_cases.object_id = im_tickets.ticket_id
JOIN wf_tasks ON wf_tasks.case_id = wf_cases.case_id
JOIN wf_task_assignments ON wf_task_assignments.task_id = wf_tasks.task_id
JOIN persons ON persons.person_id = wf_task_assignments.party_id
JOIN wf_workflows ON wf_workflows.workflow_key = wf_cases.workflow_key
JOIN im_projects ON im_projects.project_id = im_tickets.ticket_id
WHERE im_tickets.ticket_status_id 30001
AND im_tickets.ticket_status_id 30097
AND wf_cases.state = 'active'
AND wf_tasks.state 'finished'
AND wf_tasks.state 'canceled'
UNION ALL
SELECT persons.first_names || ' ' || persons.last_name AS assignee,
wf_workflows.description AS workflow, wf_tasks.transition_key AS workflowstep,
im_category_from_id(im_tickets.ticket_status_id) AS thestatus,
im_tickets.ticket_id, im_projects.project_name, im_tickets.ticket_description
FROM im_tickets
JOIN wf_cases ON wf_cases.object_id = im_tickets.ticket_id
JOIN wf_tasks ON wf_tasks.case_id = wf_cases.case_id
JOIN wf_task_assignments ON wf_task_assignments.task_id = wf_tasks.task_id
JOIN groups ON groups.group_id = wf_task_assignments.party_id
JOIN acs_rels ON acs_rels.object_id_one = groups.group_id
JOIN persons ON persons.person_id = acs_rels.object_id_two
JOIN wf_workflows ON wf_workflows.workflow_key = wf_cases.workflow_key
JOIN im_projects ON im_projects.project_id = im_tickets.ticket_id
WHERE im_tickets.ticket_status_id 30001
AND im_tickets.ticket_status_id 30097
AND wf_cases.state = 'active'
AND wf_tasks.state 'finished'
AND wf_tasks.state 'canceled'
ORDER BY assignee, workflow

If I run this report, I get a lot of lines (well, if Project Open has a lot of tickets, I’ll get a lot of lines) that look something like Figure 2.

Figure 2: Reports can help you do all sorts of things, like see who’s working on what.
Seeing the information is helpful, but if I want to look at the ticket this example represents, I have to copy the ticket number, go to the Help Desk tab, and go through some gyrations to find that ticket number. That’s not very convenient.

Instead, why not change the report to allow the ticket number to be clickable — in other words, change the ticket number to be an anchor tag?

Here’s an example of the piece from the query that displays the ticket ID:


im_tickets.ticket_id,

Boring, huh? Well, how about if we make it interesting, like this?


'<a href=/intranet-helpdesk/new?form_mode=display&ticket_id=' || im_tickets.ticket_id || '>' || im_tickets.ticket_id || '</a>' AS clickhere,

This may be obvious to some of you, and if it is, my apologies — just ignore this entry and go do something you’ll find more interesting. But if this is something you think might help you, here’s how it works:

You can see that im_tickets.ticket_id is still part of the selection. That’s because I still want it to display as the report column; I just want it to be an anchor tag so I can click on it. So, I surround it with HTML code.

I start with a single quote, because I want PostgreSQL to treat this part of the query as a string it’s going to return as part of the results. In this case, I’m returning the anchor tag and specifying as the href the path to the part of Project Open that can display a help desk ticket:


/intranet-helpdesk/new?form_mode=display&ticket_id=

I close the single quotes and use the two vertical pipe signs — || — to tell PosgreSQL to concatenate what comes next with the string. What comes next is the ticket ID:


im_tickets.ticket_id

Next, I close the first half of the anchor tag, concatenate the ticket number again (so it displays in the report column), and chose the anchor tag. Finally, I give the who thing the column name “clickhere”.

When I run the report, it looks like Figure 3.

Figure 3: Using HTML, especially anchor tag, in a report can expand Project Open’s UI.
If I click on this example, Project Open will display ticket 57974.

We do the same kind of thing with selected subsets of Conf Items — for instance, everything with a non-null IP address. It’s fairly accessible to anyone who’s willing to invest in learning Project Open’s schema.

I hope you find this technique helpful!

terrance
Terrance A. Crow is the Manager of Global Security at a global library services company. He holds a CISSP and has been writing applications since the days of dBASE III and Lotus 1-2-3 2.01.
https://www.interstell.com