Jump to content

Report on billable tasks on requests per Service


Recommended Posts

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!












Edited by Alex8000
Link to comment
Share on other sites

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
h_itsm_services.h_pk_serviceid = h_itsm_servicesubscriptions.h_fk_serviceid
JOIN h_itsm_servicesubscriptions	
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


Link to comment
Share on other sites

Hi Ryan,


Thank you, that seems to have fixed that! 

After trying it this way I noticed 


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

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...