IntegraGreg Posted January 15, 2016 Share Posted January 15, 2016 We have a requirement to produce a monthly report detailing how much time we spend working on requests (currently logged in activities). At the moment I have to produce 2 separate reports (one for h_sys_tasks and another for h_itsm_requests) as I'm unable to join the two tables. The data in h_sys_tasks has a column h_obj_ref_urn which returns values in the format "urn:sys:entity:com.hornbill.servicemanager:Requests:IN000000001", which doesn't match the values held in h_itsm_requests.h_pk_reference (IN000000001). Is there another field that I'm missing which could do this? Link to comment Share on other sites More sharing options...
TrevorKillick Posted January 18, 2016 Share Posted January 18, 2016 Hi Greg You can use a query like the following to join h_itsm_requests and h_sys_tasks . select h_itsm_requests.h_pk_reference, h_sys_tasks.h_task_id from h_itsm_requests,h_sys_tasks where h_sys_tasks.h_obj_ref_urn = CONCAT("urn:sys:entity:com.hornbill.servicemanager:Requests:", h_itsm_requests.h_pk_reference) Kind Regards Trevor Killick Link to comment Share on other sites More sharing options...
IntegraGreg Posted January 19, 2016 Author Share Posted January 19, 2016 Hi Trevor Thanks for the response. Yes that code would work but I can't see where I'd script some SQL to execute it! From what I can see all I can do is specify the tables and joins using the SQL Schema Designer and nothing fancier than that. Is there a way I'm able to directly interrogate our database using SQL? Link to comment Share on other sites More sharing options...
TrevorKillick Posted January 19, 2016 Share Posted January 19, 2016 Hi Greg, That correct i had a chat with development yesterday and we should be delivering a change to the Reporting Constructor in the next few days it just needs to go through testing. This should allow you to create an SQL Join using any valid SQL syntax. I will post back when this functionality is available in the Admin Tool. Kind Regards Trevor Killick Link to comment Share on other sites More sharing options...
IntegraGreg Posted January 19, 2016 Author Share Posted January 19, 2016 Hi Trevor Looks great - looking forward to using it! Link to comment Share on other sites More sharing options...
TrevorKillick Posted May 27, 2016 Share Posted May 27, 2016 Hi Greg Sorry i never did reply, this is now available ... Kind Regards Trevor Killick Link to comment Share on other sites More sharing options...
chriscorcoran Posted June 21, 2016 Share Posted June 21, 2016 Hi I tried the above but keep getting an error, report definition specified is not correct, it results in an invalid sql see report details below. Link to comment Share on other sites More sharing options...
TrevorKillick Posted June 21, 2016 Share Posted June 21, 2016 Hi Chris Your CONCAT needs a closing bracket does it not? Kind Regards Trevor Killick Link to comment Share on other sites More sharing options...
chriscorcoran Posted June 21, 2016 Share Posted June 21, 2016 Hi Yes, spotted when I posted, corrected but the same 1031377 2016-06-21 16:04:35 error database 2784 Report definition specified is not correct, It results in an invalid sql : SELECT h_itsm_requests.h_pk_reference,h_itsm_requests.h_summary,h_itsm_requests.h_fk_user_name,h_sys_tasks.h_time_spent FROM h_itsm_requests LEFT JOIN h_sys_tasks ON h_sys_tasks.h_obj_ref_urn = 'CONCAT("urn:sys:entity:com.hornbill.servicemanager:Requests:",h_itsm_requests.h_pk_reference)' OR WHERE h_itsm_requests.h_fk_serviceid = '22' LIMIT 500 Link to comment Share on other sites More sharing options...
Guest Chaz Posted June 23, 2016 Share Posted June 23, 2016 Hi Chris, just used a validator to check the statement and there is an OR keyword before the WHERE clause. Looking at your screenshot, I can see a kind of turquoise colour box with the word OR inside it. Removing this will hopefully sort it for you. Link to comment Share on other sites More sharing options...
chriscorcoran Posted June 23, 2016 Share Posted June 23, 2016 cchana perfect, good spot, works a treat now. Thanks all for you help on this. Chris Link to comment Share on other sites More sharing options...
chriscorcoran Posted June 27, 2016 Share Posted June 27, 2016 Hi Just following on from the above, I have added some customer contact details from the h_sys_contact table but I'm getting odd results seeing the call repeated loads of times with the wrong customer Attached are my tables 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