Jump to content

Using SQL queries on report platform


Joyce
 Share

Recommended Posts

Hi,

 

How can I use Sql query to build a report, instead of selecting fields? Is there a place to put a queary?

image.thumb.png.6280184ebfbf38046df65cafc7cb5b3d.png

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

  • Victor changed the title to Using SQL queries on report platform
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

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

@Joyce you could consider a data list widget?

image.png

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

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

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

@Joyce you copy/pasted the syntax from somewhere and this introduced invalid characters throughout the statement:

image.png

 

 

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

 

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
 Share

×
×
  • Create New...