Some SQL assistance needed with feedback filtering via Service Domain in Advanced Analytics Widget

[Adam Toms]

Hi all,

Originally when when went live with this particular widget (please see attached screenshot), we were only capturing feedback survey results per month from one area, IT.

We've now onboarded our HR team and they are using the domain of HR written to h_custom_l in their tickets. We're now getting both sets of data, which in this area, which I need to correct.

I would like to use this dashboard for just IT only. However when using what I believe would be the correct SQL statement to add this in it errors and no data is provided.

My SQL knowledge is limited, and is self taught. So I may well just need some assistance with someone who is more experienced.

Is there a way I can filter this to only include the IT tickets via another method, or does the feedback table not include service domain, and therefore need an alternative method of getting this data?

 

Many Thanks

Adam

[samwoo]

Hi @Adam Toms,

Does something like this work? (add to the end of your existing query but first backup your current query)

AND h_request_id in 
(
  select	h_pk_reference 
  from 		h_itsm_requests 
  where 	h_custom_i = 'IT' 
)

(Sorry I couldn't tell if it's custom field i or L)

Though if you are using Service Domains, then it might be better to do it like this?

AND h_request_id in 
( 
  select	h_pk_reference 
  from 		h_itsm_requests, 	
  		h_itsm_services 
  where 	h_fk_serviceid = h_pk_serviceid 
  and 		h_service_catalog_domain = 'IT' 
)

 

[Adam Toms]

Thank you @samwoo

I've gone with the top option and it was custom L. That works perfectly. Thanks very much for your help. I didn't realise I could build it up like that in the filter, this gives me an idea for a few others, where I can enhance the reporting coming out of those reports. Much appreciated.

[samwoo]

Great to hear @Adam Toms and no worries at all, glad to help 

Sub-queries are great in these types of scenarios but as long as one is mindful of potential performance impacts when using larger sub-queries, multiple sub-queries or even nested sub-queries. 

I do a lot of checks using Database Direct to make sure the sub-queries are functioning as intended and are not taking too long to run or slowing down the system.

Sub-queries can also be used in Reporting as well and can get extraordinarily complex if you need it.

Thanks,

Samuel