Joyce Posted August 10, 2018 Share Posted August 10, 2018 Hi, How can I use Sql query to build a report, instead of selecting fields? Is there a place to put a queary? I am looking to create a report, to select tickets which have been re-assigned between team twice or more. I have done this in a measure to get a count per month, but I want a list so we can identify the tickets and review them. Is there any other way? Thanks. Regards, Joyce Link to comment Share on other sites More sharing options...
Victor Posted August 10, 2018 Share Posted August 10, 2018 57 minutes ago, Joyce said: How can I use Sql query to build a report @Joyce you can't I'm afraid. We want to make our products as user friendly as possible and eliminate as much as possible the need for specific technical expertise for configuration. Also on more than one occasion we saw really inefficient queries, some of them heavily impacting the database which causes issues with general usage of the service. I'll have a look and see if the report you need (tickets which have been re-assigned between team twice or more) can be built using the reporting interface. Link to comment Share on other sites More sharing options...
Joyce Posted August 10, 2018 Author Share Posted August 10, 2018 Thanks Victor. I have a measure for it with query: h_requesttype ='Service Request' AND h_pk_reference IN(SELECT h_request_id FROM h_itsm_request_team_assignment WHERE h_previous_team_id != 'No previous team' AND h_previous_user_id !='No previous owner' GROUP BY h_request_id HAVING COUNT(h_request_id) > 2) I just want to have a data list of these tickets. Thanks. Regards, Joyce Link to comment Share on other sites More sharing options...
Steven Boardman Posted August 10, 2018 Share Posted August 10, 2018 @Joyce you could consider a data list widget? Maybe something like this: SELECT h_request_id As Reference, h_itsm_requests.h_summary AS Summary , COUNT(h_request_id) AS Assignments from h_itsm_request_team_assignment inner Join h_itsm_requests on h_request_id=h_pk_reference and h_requesttype='Incident' and h_itsm_request_team_assignment.h_previous_team_id !='No Previous Team' and h_itsm_request_team_assignment.h_previous_user_id !='No previous owner' Group by h_request_id HAVING COUNT(h_request_id) > 2 order by Assignments desc Limit 10 I've limited it to 10 results, obviously you would not need to. I've included your No Previous Owner clause in this example, but i have the following question: In your existing widget, if you have the No Previous Owner clause, is this giving you a true reflection on assignments? I can see the No Previous Team clause, as i assume this is to remove the first assignment count, but if the request is then passed to individuals in the same team or to individuals in other teams, then your assignment count will be accurate, but if someone passes the request to a team and not an individual during the lifecycle of your request, then when the next assignment to an agent occurs, it would not show that assignment count in your widget as the table would hold a No Previous Owner entry when the request was assigned at the team level before? I hope that makes sense, and quite possibly i am missing something but thought i would at least ask the question Anyway perhaps the data list widget might give you the request info you want Steve Link to comment Share on other sites More sharing options...
Joyce Posted August 10, 2018 Author Share Posted August 10, 2018 Hi Steve, I created this report months ago, I think at a time the no previous owner was to eliminate the first count ( when ticket is logged, and assigned to a team, then another assignment is when an analyst within the same team takes the ticket). So the logic is, Its should have a Previous team, and have a previous Owner...so manly start counting the ticket when it move to the third assignment? does this make sense? I take it the above query can be used in Widgets and not possible on Report? Thanks. Regards, JOyce Link to comment Share on other sites More sharing options...
Steven Boardman Posted August 10, 2018 Share Posted August 10, 2018 @Joyce ok i can see that logic thanks I haven't been able to do the same in a report as yet, but wanted to share something Link to comment Share on other sites More sharing options...
Joyce Posted August 13, 2018 Author Share Posted August 13, 2018 Hi Steve, I have try your query above, but I am getting an error. SELECT h_itsm_request_team_assignment.h_request_id As Reference, h_itsm_requests.h_summary AS Summary , COUNT(h_itsm_request_team_assignment.h_request_id) AS Assignments from h_itsm_request_team_assignment inner Join h_itsm_requests on h_request_id=h_pk_reference and h_itsm_request_team_assignment.h_previous_team_id !='No Previous Team' and h_itsm_request_team_assignment.h_previous_user_id !='No previous owner' Group by h_request_id HAVING COUNT(h_request_id) > 2 order by Assignments desc Limit 10 Not sure what I am missing. Regards, Joyce Link to comment Share on other sites More sharing options...
Victor Posted August 13, 2018 Share Posted August 13, 2018 @Joyce you copy/pasted the syntax from somewhere and this introduced invalid characters throughout the statement: NOTE: Although the query does work, I would advise doing the JOIN the other way around: INNER JOIN h_itsm_requests ON h_pk_reference = h_request_id ...and to comply with my OCD the statement should look like: SELECT h_itsm_request_team_assignment.h_request_id As Reference, h_itsm_requests.h_summary AS Summary, COUNT(h_itsm_request_team_assignment.h_request_id) AS Assignments FROM h_itsm_request_team_assignment INNER JOIN h_itsm_requests ON h_itsm_requests.h_pk_reference = h_itsm_request_team_assignment.h_request_id AND h_itsm_request_team_assignment.h_previous_team_id != 'No Previous Team' AND h_itsm_request_team_assignment.h_previous_user_id != 'No previous owner' GROUP BY Reference HAVING COUNT(Reference) > 2 ORDER BY Assignments DESC LIMIT 10 But that's just how I would have to do it ... Link to comment Share on other sites More sharing options...
Joyce Posted August 17, 2018 Author Share Posted August 17, 2018 haha thanks Victor. will amend my query. 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