Jump to content

Use same table multiple times in single report?


Lyonel

Recommended Posts

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

  • 3 weeks later...

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

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

  • Like 2
Link to comment
Share on other sites

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

  • Like 1
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...