Getting the Project Manager name in a report for tasks

[Ann]

Morning All

Hope you are safe and well.  I have created a report and one of the fields it brings who a task is assigned to, the field is 'Assigned to' from the h_sys_tasks table, however this brings back a staff id, not the staff name.  How am I able to bring back the staff name please?  Thank you in advance.

[Hornbill Staff DR]

Hi Ann,

 thanks for your post.

As you'll have found out, the task assignee (h_assigned_to) in h_sys_tasks actually stores a "Uniform Resource Name".  I'm not au fait with the motivations for using this standard but I imagine it'll relate to the fact that a task assignee can be one of several things; a user, a group, or even a role. This means that the system has to understand more about the assignee because it needs to know whether it's resolving a user, group, or role when presented in the user interface - information that can't be gained from a simple ID alone.

Anyway, that might be interesting to some, but given this situation how do we get a more user-friendly output for the assignee considering that there isn't a corresponding URN value stored in h_sys_accounts, or an alternative value in h_sys_tasks which can be used for our JOIN?

When faced with this, I turn to the SQL "CONCAT" function. This allows us to concatenate two values into one single value which we can use to serve our needs i.e. make the necessary JOIN. In this case we would be constructing the value using the combination of a string of text (urn:sys:user:), and the value of a database column (h_sys_accounts.h_sys_user_id). So when using the function CONCAT('urn:sys:user:',h_sys_accounts.h_user_id) the output would equate to a value such as "urn:sys:user:userID" which is equivalent to what is stored in h_assigned_to.

This function would be used as shown in the image. Essentially we're saying; join the records in h_sys_tasks when h_sys_tasks.h_assigned_to equals a concatenation of the string urn:sys:user: and the user ID found in h_sys_accounts.

 

I've uploaded an example report definition too.

I hope that helps!
Dan

tasks---active-tasks-per-assignee.report.txt

[Ann]

Afternoon Dan

Thank you so much for the information above.  Apologies for the delay, I have been attempting the above report migrated into my existing report, however I keep getting the error message "Error definition specified is not correct, it results in an invalid".  I already have the two tables h_sys_tasks and h_sys_accounts.  Would it be possible to send the report file to see where I am going wrong please?

[Ann]

Apologies Dan, with that said I have now got this to work.  Thank you so much for your help it is very much appreciated.