3 Replies Latest reply on Oct 13, 2009 4:05 PM by Pinchers

    How do I report on Parent / Child relationships?

    Rookie

      I am trying to write a report that will show if an Incident is either a Parent or Child of another Incidnet.  I have identified that the relationship is stored under im_incident_incident.

       

      However when using this data on a Crystal Report only the child Incidnets are displayed beacuse that is the only data returned from the (im_incident_incident) table.  (I have linked pm_process.pm_id to im_incident_incident.im_child_id).

       

      I need to show all Incidnets including those with a parent / child relationship.

       

      How can I achive this with Crystal?

        • 1. Re: How do I report on Parent / Child relationships?
          Lara Hellman SupportEmployee

          Hi there,

           

          You can add a table to the report more than once, so you could have pm_process.pm_id = im_incident_incident.im_child_id and pm_process1.pm_id = im_incident_incident.im_parent_id.  Don't forget that you can also change the join type from an Inner Join to a Left, Right or Full Outer join depending on exactly what it is that you wish to report on.

           

          Thanks,

           

          Lara

          • 2. Re: How do I report on Parent / Child relationships?
            karenpeacock SupportEmployee

            Ah I didn't see that Lara had already replied and had already knocked up a little basic report for you to show how this can be done.  Oh well I guess two replies is better than none!   I left the table joins as equal so it will only show you incidents which have parent or child relationships.

             

            Thanks

            Karen

            • 3. Re: How do I report on Parent / Child relationships?
              Rookie

              Karen and Lara - thanks!

               

              It was the join that was causing the issue.  With the standard join only those Incidents that had a parent - child relationship were shown, as per Karen's demo report.

               

              By changing the join to a left outer the child GUIDs appeared, whilst still reporting all of the records that had no parent - child relationship.  I also found that I needed to join the additional pm_process1 table with a left outer join to the im_incident_incident table otherwise only the related records were shown again.

               

              Once again thanks for the speedy response.

               

              Lee