5 Replies Latest reply on Jul 25, 2013 2:00 PM by Miketedeschi

    Report Configuration

    Rookie

      Does anyone know where the Support Group for a resolved incident resides in the database? (table/column)

       

      I'm trying to add the support group to the TimeToResolution report so i can then sort on the report by Support Group. 

       

      Any help would be greatly appreciated.

        • 1. Re: Report Configuration
          Expert

          The easiest way to see the relationships is to open the sample Crystal Reports.  The reports themselves are an ok start to CR but the true jewel of the reports is that it shows the linking between tables.  All linking is thru GUIDS, not the incident number or some other normal looking text so it takes a bit of getting used to but it works.  Follow the reports and it all makes sense.

          1 of 1 people found this helpful
          • 2. Re: Report Configuration
            gramsay Specialist

            SELECT     dbo.im_incident.im_id AS IncidentRef, dbo.im_incident_resolution.im_creation_date AS ResolveDateTime, dbo.tps_group.tps_title AS ResolvingGroup

            FROM         dbo.im_incident_resolution INNER JOIN

                                  dbo.tps_group ON dbo.im_incident_resolution.im_create_group_guid = dbo.tps_group.tps_guid INNER JOIN

                                  dbo.im_incident ON dbo.im_incident_resolution.im_incident_guid = dbo.im_incident.pm_guid

            • 3. Re: Report Configuration
              Apprentice

              As far as the column and table, i think it would have to be the im_latest_assignment_grp_guid in the im_incident table.  It is a guid so you would have to tie in the tps_group table to actually get the name.

               

              The current_assignment is usually blanked out when tou resolve or close a ticket.

               

              You can also get the same latest assignment from the pm_process table, but that table includes more than just incidents.

              • 4. Re: Report Configuration
                Apprentice

                i agree with the crystal reports starting point.  you can actually copy and paste the SQL code from crystal into sql management studio into a new sql view, and it will connect all the table relationships visually.  save that, and refer to it.

                 

                from there, add tables you need 1 at a time, but be aware the auto relationships created when adding a new table are basically useless.  Delete almost all the auto created relationships to the new table, and make them 1 at a time.

                 

                now that I think about it, there must be a master relationship diagram somewhere. I would love to print it on the 36" plotter we have in the office to refer to...  I think I would need a bigger printer