Jump to content
clampj

Report showing the linked ticket reference number

Recommended Posts

Hi

We have a requirement to be able to produce a report that shows tickets logged against a category name.  These tickets are linked to a 'master' ticket.  I'd like the report to return details about the ticket (which is easy) including the master ticket reference number.  Which column holds the linked ticket reference number?

Thanks

J

Share this post


Link to post
Share on other sites

@clampj most likely you need the info stored in h_sm_relationship_entities table (it's called RelatedTequests as entity). This is where all relations are stored.

Share this post


Link to post
Share on other sites

Thanks although I'm having a few problems getting it to work

I'm assuming that the call from which the link activity is performed is the parent call and the reference of this call is placed into the Parent Request ID column with the Related Requests table?

In order to better understand which values are place into which columns I've created a test report to view the contents of the Related Requests table with the following config:

Report output type: Single list of data

Report using: SQL Schema Designer

I've selected all columns and tried setting the filter for any of the columns in the Related Requests = not null which should return rows where data exists but the preview doesn't return any results.  This suggests the table is empty does it not?

Any ideas?

Thanks

J

Share this post


Link to post
Share on other sites

@clampj If you require detailed assistance with creating reports you are welcome to raise a Service Request with our Expert Services team.

Share this post


Link to post
Share on other sites

Hi @clampj

I have created a test report for you that shows how you can perform a JOIN between the main requests table (which holds your tickets against the category) and the "Linked Requests" table (which would hold the link to the Master Ticket)

I have based this simply around Incidents that are currently open, but obviously you could change the main criteria. Also I have only included the Linked Request Number. If you are after further details about the linked request, it becomes a little more complicated. 

To upload this onto your own instance, download the attachment, then navigate to Admin Tool -> Applications -> Service Manager -> Reports. Then create a brand new report, and click the Green "Upload" button in the top right hand corner

N.B - one other thing - be aware that if you have more than one linked request, then there will be multiple rows to show each of these references

I hope this gives you a starting point and you can add the coumns and filtering required to build the reports you need!

Kind Regards

Bob

currently-open-incidents-including-linked-request-number.report.txt

Share this post


Link to post
Share on other sites

@Bob Dickinson Many thanks!

I can now see where I was going wrong - using the wrong table.  I was using Related Requests instead of Relationship Entities.

I can now do what I needed to so thanks again!

Share this post


Link to post
Share on other sites

@clampj oops, apologies, I got the entity name wrong :( 

RelatedRequests is the old entity used to store request relation but it was some time ago replaced with RelationshipEntities. Sorry for teh confusion. I have asked our dev team if they can remove the old entity as is no longer in use in any instance.

Share this post


Link to post
Share on other sites

Hi @Bob Dickinson, I really need your help on a similar request to that which you have provided a solution to here.

I need to be able to output information from a Linked (child) request (Summary, Closure Category etc)  where the report is filtered by data contained in the Parent request.

For instance... I want to see a list of child requests where the parents closure category is X. 

This seems to involve referencing the Requests table twice with the Related Entities table and I can't quite figure it out. 

Would appreciate any help you can offer.

 

Thanks!

Keith

 

 

 

 

 

 

 

Share this post


Link to post
Share on other sites

@Mary apologies for being so late to comment but his was really helpful and solved my issue.

 

Share this post


Link to post
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...