Report showing the linked ticket reference number

[clampj]

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

[clampj]

Can anyone help?  @Victor?

[Victor]

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

[clampj]

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

[clampj]

@Victor Any ideas?  Should I log this with support?

[Steve Giller]

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

[Bob Dickinson]

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

[clampj]

@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!

[Victor]

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

[clampj]

No probs @Victor - as always, I appreciate your help!

[Keith]

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

 

 

 

 

 

 

 

[Guest Mary]

@Keith

I have attached a sample report which shows the modification required to display information from a Linked (child) request (Summary etc)  where the report is filtered by data contained in the Parent request.

Feel free to modify the filter as required.

linked-child-request-report-filtered-by-data-in-the-parent-request.report.txt

[Keith]

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