Report on billable tasks on requests per Service

[Alex8000]

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

[Ryan]

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

[Alex8000]

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!

[Ryan]

No problem Alex.