Linking Project and Service Manager tables for reporting

[Steve]

Hi

I'd like to try and get some help in how to pull Project information into a report in Service Manager which is using the 'h_sys_tasks' table.

The 'Object Reference URN' field in the h_sys_tasks table displays the value in the format 'urn:sys:entity:com.hornbill.projectmanager:Projects:123

The Project ID field in the Projects table displays the same project as 123.

I suspect I would need to set some custom criteria in the table join to match the last characters of the Object Reference URN value to the Project ID field, but I'm not sure where to start.

Is this possible please?

Thanks

 

[Steve Giller]

Project Tasks are stored in h_project_project_tasks so it might be more efficient to use that table.

[Steve]

Hi Steve

Unfortunately, I was hoping to be able to pull together all tasks for an individual or team from both Project and Service Manager into one place. 

[Steve Giller]

It can be done - you know the format so it's "just" a case of cutting off the "urn:sys:entity:com.hornbill.projectmanager:Projects:" from the value - unfortunately I don't have the SQL for that to hand.