Alex8000 Posted October 3, 2016 Share Posted October 3, 2016 (edited) Good Morning all, We hope to create a list of all requests which have tasks marked as billable for service 'X'. What I have so far allows us to create a list of tasks marked as billable for clients with a specific value within a custom organization field. For example: All requests with tasks marked as billable for every client whose organization has h_custom_8 = 'Swyx Hosted'. We would rather have the report look at the actual service instead of some custom organization field which may or may not be up-to-date. I figured I would just join h_itsm_servicesubscriptions (h_fk_subscriberid) with h_sys_organizations (h_organization_id). (and optionally h_itsm_servicesubscriptions (h_fk_serviceid) with h_itsm_services (h_pk_serviceid) for filtering on service name instead of service id.) When generating the report the system throws a general "Error occurred while executing generated querry" error. I think it has something to do with the joins, but have been stuck on this for a while and am open for suggestions! Thanks, Alex 1-swyx-hosted---alle-tickets.report.txt Edited October 3, 2016 by Alex8000 spelling Link to comment Share on other sites More sharing options...
Ryan Posted October 3, 2016 Share Posted October 3, 2016 Hi Alex, I think the issue you are encountering is that your join clause is referencing a table you are yet to include. The generated SQL will be something like: JOIN h_itsm_services ON h_itsm_services.h_pk_serviceid = h_itsm_servicesubscriptions.h_fk_serviceid JOIN h_itsm_servicesubscriptions ON h_itsm_servicesubscriptions.h_fk_subscriberid = h_sys_organisation.h_id It is trying to reference the subscriptions table when the services table is joined, before the subscriptions table is joined. I would remove the h_itsm_services table, and then add it at the bottom of the list with the same criteria. I hope this helps Ryan Link to comment Share on other sites More sharing options...
Alex8000 Posted October 6, 2016 Author Share Posted October 6, 2016 Hi Ryan, Thank you, that seems to have fixed that! After trying it this way I noticed h_fk_servicename in the h_itsm_requests table. I should have looked better before trying to do it the hard way... A simple WHERE h_fk_servicename = 'Swyx Hosted' would have sufficed. Thank you for looking into this, I will keep your solution in the back of my head when generating the other reports! Link to comment Share on other sites More sharing options...
Ryan Posted October 6, 2016 Share Posted October 6, 2016 No problem Alex. 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