6 Replies Latest reply on Mar 8, 2017 1:13 PM by Spartan

    Querying against top parent category

    Expert

      Hi Guys,

       

      For ages we've been asked if we can query and group  against just top level categories.

       

      We can query against title, or against parent, using equals or "equals category" and this is very flexible.

       

      But we have quite distinct tier 1 categories, e.g.:

       

      Finance

      IT

      HR

      Facilities Management

       

      Each of which have many sub-category levels (sopme of which may be called the same).

       

      So querying against category title = "printer" may return both IT and FM calls, as both have suncategories called that.

      Grouping against category where the query equals category "IT" will return hundreds of sub groups.

      Querying against parent, simply shifts one layer up, which of course may not even exist.

       

      We want to be able to group against just the top levels, so we would get a result somehting like

       

      Finance = 324

      IT = 3454

      HR = 77

      FM = 134

       

      Anyone know how we could do this?  Possibly create a new field and a calc that records the ultimate parent when the category is selected?

       

      Any useful ideas welcomed.

       

      Cheers.

        • 1. Re: Querying against top parent category
          Andrew Swindells SupportEmployee

          Hi Adam,

           

          Could you add a boolean to the category object. Add this to the category window and set it for the top level category.

           

          Group your query by the Category.Title

           

          Then add a criteria to your query to only show 'Category.Is Top Level = True'.

           

          This will then group by those categories that are set as 'Top Level'

          • 2. Re: Querying against top parent category
            Expert

            Doh!

             

            Why didn't that occur to me?

             

            Must be getting old.

             

            I've set two booleans - one for Root categories and another for "Key Categories" so we can also compare the most common incidents, wherever they lie in the hierarchie(s) without having to use individual queries.

             

            Thanks Andrew.

            • 3. Re: Querying against top parent category
              Expert

              I might create a calculation that returns the contents of the full category name before the first separator.  Then you could create reports that group on that value allowing you to see the number of incidents for each category but then also allowing you to drill down and see the details.  Then might create another calc that returns the second category name based on separators in the full category name and then add this as a second level group to your reports so you can see 500 HR incidents and then drill down to see 250 new accounts, 150 terminations, and 100 changes, etc etc etc.

              2 of 2 people found this helpful
              • 4. Re: Querying against top parent category
                Expert

                Looking over this again, a calculation seems to offer more flexibility.

                 

                I''ve created a very simple calc in the Incident Category object on a new string field called "Root Title"

                 

                 

                import System

                static def GetAttributeValue(Category):

                FullCat = Category.FullName

                Marker = FullCat.IndexOf('-')

                if Marker == -1:

                  RootValue = FullCat

                else:

                  RootValue = FullCat.Substring(0,Marker-1)

                return RootValue

                 

                Simply temporarily adding a space at the end of each root category and saving populates the new value through to all child nodes.

                 

                Also updates when I drag and drop vategories across nodes so looking good.

                 

                Will test on dev a bit mnore...

                 

                Cheers.

                1 of 1 people found this helpful
                • 5. Re: Querying against top parent category
                  Spartan Apprentice

                  Just thought I would fix the syntax for those of us who are not so good at Boo

                   

                  import System
                  static def GetAttributeValue(Category):
                  FullCat = Category.FullName
                  Marker = FullCat.IndexOf('-')
                  if Marker == -1:RootValue = FullCat
                  else:RootValue = FullCat.Substring(0,Marker-1)
                  return RootValue
                  

                   

                  This worked perfectly, many thanks!

                  • 6. Re: Querying against top parent category
                    Spartan Apprentice

                    I lied, it's not quite working as expected... While the above seems to pull the data I'm looking for, I would ultimately like to be able to group against the data in this field.

                     

                    Here is what I get?    ... No?    Yet Root Title is clearly getting data?

                     

                    My query:

                     

                    Not knowing muct about Boo I thought it might have something to do with Null values so I tried changing the calculation but this made no difference.

                     

                    import System
                    static def GetAttributeValue(Category):
                         FullCat = ''
                         if Category.FullName != null: FullCat = Category.FullName
                         Marker = FullCat.IndexOf('-')
                         if Marker == -1:RootValue = FullCat
                         else:RootValue = FullCat.Substring(0,Marker-1)
                         return RootValue
                    

                     

                    Any help would be greatly appreciated

                     

                    Thank you,