Add query to report

[billster]

Hi - how do I add this query result as an extra column within reporting?

 

(SELECT timestampdiff(minute,H_datelogged,current_timestamp) as incidents_minutes_open FROM h_itsm_requests
    WHERE h_dateclosed IS NULL ORDER BY h_datelogged DESC LIMIT 1)

 

It just calculates the minutes an incident has been open for.

 

Within reporting I tried adding it here, but it doesn't do anything - I am probably putting it in the wrong area.

 

 

 

ta

 

 

 

[Jim]

I don't think you can just add a column in a report, but theoretically, you could do a replace on one of the fields like time open in seconds, not sure of the exact field that is now though

[billster]

17 hours ago, Jim said:

I don't think you can just add a column in a report, but theoretically, you could do a replace on one of the fields like time open in seconds, not sure of the exact field that is now though

How could you do a repace? I tried to do like... SELECT x as h_custom_o but it didn't do anything.

[Jim]

Hi @billster Yeah In theory it would work as it does in the database query directly, however the way the reports work I had a go myself and it doesn't seem possible, It would be useful if we could add criteria to the reports that aren't necessarily a part of the join to allow us to do calculations etc as part of the report, I think the response would be to use measures for calculations although I don't think even they do a time conversion sadly, Which means the only option would be to run the query in database direct, not that that helps any user without access to it.  It's either going to end up being a feature request or for the interim use excel to do the calculations, maybe include the example calculation in the description of the report for those that need to run it