Lyonel Posted December 12, 2016 Share Posted December 12, 2016 Hi, I am trying to build the following query using the report builder: SQL script: SELECT SR.h_custom_a as responsible_team, h_requesttype, h_pk_reference, h_summary, h_fk_user_name, REQ.h_status, h_fk_servicename, h_catalog, h_fk_priorityname, h_fk_team_name, h_ownername, h_site, h_source_type, h_source_id, h_datelogged, h_dateresolved, h_dateclosed, h_reopencount, h_isanalystunread, h_withinresponse, h_withinfix, FirstTask.h_created_on as priority_confirm_created_on, FirstTask.h_completed_on as priority_confirm_completed_on, MSG.h_msg_date as email_date_received, RESP.h_target_start as response_target_start, RESP.h_target_completed_time as response_completed_time, RESOL.h_target_start as resolution_target_start, RESOL.h_target_completed_time as resolution_completed_time FROM h_itsm_requests REQ inner join h_itsm_services SR on h_fk_serviceid = h_pk_serviceid left join (SELECT replace(h_obj_ref_urn,'urn:sys:entity:com.hornbill.servicemanager:Requests:','') as ref, h_created_on, h_completed_on FROM h_sys_tasks where h_title like '%Confirm Priority%' and not h_completed_on is null) as FirstTask on FirstTask.ref = REQ.h_pk_reference left join (SELECT h_msg_id, h_msg_date FROM h_msg_messages) MSG on REQ.h_source_id = MSG.h_msg_id left join h_itsm_request_slm_targets RESP on REQ.h_pk_reference = RESP.h_request_id and RESP.h_type = 'Response' left join h_itsm_request_slm_targets RESOL on REQ.h_pk_reference = RESOL.h_request_id and RESOL.h_type = 'Resolution' where REQ.h_requesttype in ('incident','service request') Definition of my report: monitor-slas.report.txt Output of my report (based on log files): SELECT h_itsm_requests.h_pk_reference,h_itsm_requests.h_catalog,h_itsm_requests.h_dateclosed,h_itsm_requests.h_datelastmodified,h_itsm_requests.h_datelogged,h_itsm_requests.h_dateplacedonhold,h_itsm_requests.h_dateresolved,h_itsm_requests.h_fk_priorityname,h_itsm_requests.h_fk_servicename,h_itsm_requests.h_fk_team_name,h_itsm_requests.h_fk_user_name,h_itsm_requests.h_isanalystunread,h_itsm_requests.h_iscustomerunread,h_itsm_requests.h_onholduntil,h_itsm_requests.h_ownername,h_itsm_requests.h_reopencount,h_itsm_requests.h_requesttype,h_itsm_requests.h_site,h_itsm_requests.h_status,h_itsm_requests.h_summary,h_itsm_requests.h_withinfix,h_itsm_requests.h_withinresponse,h_sys_tasks.h_created_on,h_sys_tasks.h_completed_on,h_msg_messages.h_msg_date,h_itsm_request_slm_targets.h_target_completed_time,h_itsm_request_slm_targets.h_target_start,h_itsm_services.h_custom_a FROM h_itsm_requests LEFT JOIN h_sys_tasks ON h_sys_tasks.h_obj_ref_urn LIKE h_itsm_requests.h_pk_reference LEFT JOIN h_msg_messages ON h_itsm_requests.h_source_id = h_msg_messages.h_msg_id LEFT JOIN h_itsm_request_slm_targets ON h_itsm_requests.h_pk_reference = h_itsm_request_slm_targets.h_request_id AND h_itsm_request_slm_targets.h_type = 'Response' LEFT JOIN h_itsm_request_slm_targets ON h_itsm_requests.h_pk_reference = h_itsm_request_slm_targets.h_request_id AND h_itsm_services. = 'Resolution' JOIN h_itsm_services ON h_itsm_requests.h_fk_serviceid = h_itsm_services.h_pk_serviceid WHERE ( h_itsm_requests.h_requesttype = 'incident' OR h_itsm_requests.h_requesttype = 'service request' ) AND h_itsm_services.h_custom_a = 'Finance & HR' As you can see, I am really not far off! Only problem: I need to reference the same table twice (h_itsm_request_slm_targets) and this is where the engine fails I think. Indeed, if I just alias the tables (and tweak a little bit the select) it works just fine: SELECT h_itsm_requests.h_pk_reference, h_itsm_requests.h_catalog, h_itsm_requests.h_dateclosed, h_itsm_requests.h_datelastmodified, h_itsm_requests.h_datelogged, h_itsm_requests.h_dateplacedonhold, h_itsm_requests.h_dateresolved, h_itsm_requests.h_fk_priorityname, h_itsm_requests.h_fk_servicename, h_itsm_requests.h_fk_team_name, h_itsm_requests.h_fk_user_name, h_itsm_requests.h_isanalystunread, h_itsm_requests.h_iscustomerunread, h_itsm_requests.h_onholduntil, h_itsm_requests.h_ownername, h_itsm_requests.h_reopencount, h_itsm_requests.h_requesttype, h_itsm_requests.h_site, h_itsm_requests.h_status, h_itsm_requests.h_summary, h_itsm_requests.h_withinfix, h_itsm_requests.h_withinresponse, h_sys_tasks.h_created_on, h_sys_tasks.h_completed_on, h_msg_messages.h_msg_date, A.h_target_completed_time, A.h_target_start, B.h_target_completed_time, B.h_target_start, h_itsm_services.h_custom_a FROM h_itsm_requests LEFT JOIN h_sys_tasks ON h_sys_tasks.h_obj_ref_urn LIKE h_itsm_requests.h_pk_reference LEFT JOIN h_msg_messages ON h_itsm_requests.h_source_id = h_msg_messages.h_msg_id LEFT JOIN h_itsm_request_slm_targets AS A ON h_itsm_requests.h_pk_reference = A.h_request_id AND A.h_type = 'Response' LEFT JOIN h_itsm_request_slm_targets AS B ON h_itsm_requests.h_pk_reference = B.h_request_id AND B.h_type = 'Resolution' JOIN h_itsm_services ON h_itsm_requests.h_fk_serviceid = h_itsm_services.h_pk_serviceid WHERE ( h_itsm_requests.h_requesttype = 'incident' OR h_itsm_requests.h_requesttype = 'service request' ) AND h_itsm_services.h_custom_a = 'Finance & HR' Is there something anybody can suggest? Or should I just give up and build 2 separate reports? One for response and one for resolution? Link to comment Share on other sites More sharing options...
NeilWJ Posted January 3, 2017 Share Posted January 3, 2017 Hi Lyonel, Nothing at the moment would work. However we do use aliases when using "entity" based reports so should be able to mod the UI to allow you to specify an alias to use for the table. Not sure how much work is needed for that but i know it is definitely do-able. I will have a look and let you know. Cheers Link to comment Share on other sites More sharing options...
NeilWJ Posted January 4, 2017 Share Posted January 4, 2017 Hi Lyonel, So i have taken a look at this and it is do-able. So what i will do is if you double click on a table in the "selected" tables list it will popup a form to let you set a sql alias for the table. If it all goes well and works as intended will release it next week hopefully. Cheers 2 Link to comment Share on other sites More sharing options...
NeilWJ Posted January 9, 2017 Share Posted January 9, 2017 Hi Lyonel, So i have implemented this and just going to put it out internally for testing. I uploaded your definition and it works ok (though after giving the tables an Alias i had to redo each clause that mentioned the tables because when setting the first instance to alias A it replaced all refs to the table, so when i set the 2nd instance to B there were no matching refs as they had been set to 'A'... if that makes sense. Anyway it appears to work as intended. Oh and in your def the alias B join clause against h_itsm_services was incomplete (note no column selected to test against 'Resolution') LEFT JOIN h_itsm_request_slm_targets ON h_itsm_requests.h_pk_reference = h_itsm_request_slm_targets.h_request_id AND h_itsm_services. = 'Resolution' Anyway thought i would update you and once this has cleared internal testing will push it out to beta this week and then live if there are no issues. Cheers 1 Link to comment Share on other sites More sharing options...
Lyonel Posted January 9, 2017 Author Share Posted January 9, 2017 Thanks @NeilWJ for the update! That feature will be very handy for developing more complex reports Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now