3 Replies Latest reply on Jan 31, 2012 10:56 PM by hadyn.dawson

    Reporting against categories




      I need to compare the category used for logging an incident against the category used in the resolution


      I am using Crystal (yes I know LD don't support Crystal), however I need to check that I am linking the correct tables and attributes


      I have pulled through the im_incident_category through twice and attached one to im_incident.im_category_guid --> im_incident_category.im_guid

      and have attached the other im_incident_resolution.im_category_guid --> im_incident_category_1.im_guid


      However this is not working, when I pull the attribute onto the report it removes all records  - can you help?


      I have also created a new location category - I have pulled the usr_newlocationcode table onto the report and linked it im_incident.usr_newlocationcode --> usr_newlocationcode.usr_guid however when I go to the report it doesn't pull any fields through


      I have also noticed that the attributes created in the usr_newlocationcode table are ubr_fullname


      Please help




        • 1. Re: Reporting against categories

          I don't think the category guids for resolution categories and incident catergories will ever match. As they are from different category lists. I think you will have to link the tables using the categroy title or name fields. 

          • 2. Re: Reporting against categories

            Based off the default joins that crystal uses and the since the guids wont match as someone has already said, this should return nothing.


            I've got a lot of good information and trending out of crystal through trial and error so if you explain the type of report you're looking for, maybe we can help.


            As far as your category report, I would right join im_incident_category.im_guid --- im_incident.im_category_guid


            I would then left join im_incident.pm_guid --- im_incident_resolution.im_incident_guid


            then left join im_incident_resolution.im_category_guid --- im_incident_resolution_category.im_guid



            then Id drop im_incident_category.im_full_name and im_incident_resolution_category.im_full_name to compare each tickets category vs. resolution category.


            I dont know what fields are mandatory on your incident and if you do not have every category filled in for resolutions and incidents, this will drop a lot of incidents under the default joins.

            • 3. Re: Reporting against categories

              Hi Helen,


              As has been mentioned before, the Resoution and Incident categories are not the same thing, even if they're titles match the GUIDs will not, so you would need to add both of these fields in.


              You JOINS would be from Incident to Incident Category and from Resolution to Resolution Category. As for the Location Code, some of your earlier incidents may not have a selected location so the default INNER JOIN Crystal would select wouldn't work. I've found with these the easiest solution is to set the link type to LEFT OUTER JOIN and ensure the reference list (in this case Location) is on the left side of the JOIN.


              This may also impact your Resolution Category joins. If every Resolution doesn't have an associated Category you will likely fall into a similar situation, so again you can use LEFT OUTER JOIN to correct this. This may not necessarily be best practise but it is the simplest solution I've found. Hope this helps.