3 Replies Latest reply on Jul 28, 2009 12:26 PM by sbuehler

    Category Query

    Leanne Apprentice

      I want to be able to create a query to pull incidents through for where they have been raised against a specific category (which goes down to levels 3 and 4), and on the query results view, also show the category levels (i.e. IT Request - Software - Microsoft Office).

       

      I have set up a query, which is currently based on incident, and on the criteria have set category to 'is equal to' and prompt analyst for value.  However, when you run the query and select the category (i.e. IT Request - Software - Microsoft Office) it is ignoring the criteria and pulling through all incidents.

       

      Can anyone give me any recommendations on how I can achieve this???

        • 1. Re: Category Query
          Stu McNeill Employee

          Hi Leanne,

           

          What other criteria is on the query?  Are any of them using "OR" rather than "AND"?  A screenshot of the page in query designer would be really helpful.

           

          Also when using categories in criteria its worth mentioning that as well as "Is equal to" there is "Is equal to category" which will also search sub-categores of that selected.  This normally makes results a lot more meaningful when searching at a higher level category.

          • 2. Re: Category Query
            Leanne Apprentice

            Stu,

             

            There is no other criteria set, so no 'OR' or 'AND'.

             

            I have tried both 'Is Equal To' and 'Is Equal To Category' both of which bring back what appears to be all incidents.

             

            I am not so worried about reporting from high level (i.e. level 1) it is more so where we have levels 2,3 and 4 set, especially when it is regarding specific software which may be categorised at level 3 or 4.

             

            Leanne

            • 3. Re: Category Query
              Apprentice

              I don't know if this will help you or not, but this is the command I use in Crystal reports to count the number of incidents assigned to each category over a date range:

               

              SELECT im_incident_category.im_full_name AS Category, Count(im_incident.im_id) AS Count_Incidents FROM im_incident LEFT JOIN im_incident_category ON im_incident.im_category_guid = im_incident_category.im_guid LEFT JOIN pm_process ON im_incident.pm_guid = pm_process.pm_guid WHERE pm_process.pm_creation_date >= {?begin_date} and pm_process.pm_creation_date <= {?end_date}
              GROUP BY im_incident_category.im_full_name
              ORDER BY Count(im_incident.im_id) DESC