How to create a view that diplays both Incidents and Interactions

I am using Service Manager 9.33 with PD4. I am trying to see if there is a way to create a view that displays both Interactions and Incidents.

The specific request right now is that Management wants to be able to have 1 view in which they can see all records for a spcific CI regardless of the status. They are not keen on going to multiple modules, but I'm not sure how I can achieve this without manually exporting and combining spreadheets.

Any thoughts?

  • Showing tickets from multiple tables(name is complex reporting :p) has always been the main reason why HPSM is packaged with a Reporting tool. - May be you can try with MySM? (we are still not 9.34 & not sure how SM PD works)
  • This is something you would normally use a reporting tool for because views aren't made to combine multiple tables. However, if you need to have some kind of SM solution, you could create a seperate table to buld the View off of in SM. Then use triggers to update the table combining the Incident and Interaction data. You could have the trigger execute on update, close, etc. Though, that table could get large pretty quickly if you have a high volume of tickets coming through your customer's service desk. Perhaps only store open tickets and then purge out the closed ones.. HTH.

  • If the management is interested only of open cases, you could add a new view to ToDo, that sorts cases as a default by the CI. Of course, ability to show only open cases is a restriction. IMO, SM doesn't contain another table, that collects ticket data (title, description, CI...) from various tables to a single table.

    To go further than that, you need a proper reporting tool like Crystal Reports to do the complex queries. I've done some Excel experiments (Pivot table, data from database), but it also has its limitations.

    I'd not start using MySM right now, because it's discontinued in SM9.4x series. It's been replaced by the revamped Reporting tools. In SM9.4x, the reporting has taken huge steps forward (and finally I can even recommend to use it without keeping fingers crossed). It has almost all the graphical bells and whistles the management can ask.

    Multitable queries aren't too well supported and it's not meant for number crunching operations, but for a "lightweight reporting" it's a solid tool.

    EDIT: small corrections

  • Verified Answer

    I think the key things here in meeting the requirement is the idea of 'a specific CI' and the word 'view'.

    When you log in to HPSM, you get the Queue and View section, usually showing your ToDo Queue, and whatever view is set up as that user's default.  Management sees this screen, thinks - hey, this is just like a report!  I love reports!  Everything I ever care about should come up on a screen just like this! - and doesn't really care that the purpose of a View is to quickly get users access to _records_ they care about, rather than to _export_ this thing to excel.  So, if they _really_ want a view, you're right, that's going to be pretty complicated.

    But the part that maybe implies a solution is your comment regarding 'a specific CI'.  If the goal is not so much to get a 'View' as HPSM defines it, but a way for management to look at a CI record and see what tickets are out there against that CI, something like that _is_ possible.  Not from the View, but from the CI record.

    Just like in all the modules, it is possible to have multiple virtual joins displayed in a single record.  In Incident Management, for example, you can see a tab that shows the list of related Interactions, Incidents, Changes, Problems that are related to _this_ Incident record.  You are querying multiple tables, but each join is split into separate sections based on the tables its going against.

    So while you can't give them a true 'View' very easily, you could modify the configuration item format to add sections for related records, and then set up virtual joins to Incident, Interaction, Problem, Change, etc, and display those in easy to parse out lists.

    Otherwise, if they _really_ need a list, you _can_ trick HPSM.  Someone suggested setting up an additional table and using triggers to populate that table.  There is another option, but it's sneaky.

    In HPSM, _you_ control the database table that a dbdict maps to.  There's logic in the code that pulls records from the underlying database to display them in the UI.  It is possible to fool that logic.

    In your underlying database, you can create a database view (like, a true join of tables using Oracle or SQL or whatever your backend is).  Then, after that has been created in your database, you create a dbdict, and map the table and fields in the dbdict to the view in your database.  When you save the dbdict, the system will check the database and see that the fields already exist, and will bypass the step where it tries to create the table and fields.  Then, within the UI, you can display the data from this custom join in the database.  

  • Thanks Jacob, you explanation was thurough and helpful. Management is not pushing this further and are content with using SQL reporting.