Reporting on multi-level categories

0 Flares 0 Flares ×


First, I want to define the problem we need to solve.

We had a customer who had a multi-level category tree- more than two levels. Some of her categories were three levels deep, some four, some five. Something like this:


Now of course, this isn’t a problem to set up in Remedyforce; you can have many levels, although we recommend not going too deep.

But the problem came with reporting: The customer wanted to run a report from the top level – in the example above, regardless of whether they picked Level 1, 2, 3 or 4, she wanted to run a report of all “Level 1” tickets. Since the ultimate parent category isn’t stored on the ticket, this took a little bit of work.

Now you can put the whole parent tree on the ticket – In the case of the above example, if I choose Level 4, that gives me a field with this in it:


If I pick Level 3, the field looks like this:


Which is a great start; but HOW do you pull the top category off of that? It’s on the right-hand side of the field – but of course, number of characters may vary. And you can’t just search for “>” because that may be in the string multiple times. Here’s what I did.

Step 1

Put a special character that the beginning of your top level categories. The sharp-eyed among you will have noticed I’m using an exclamation point.


Step 2

Put the Parent Tree on to your form. You can show it or not in the Field Sets; but create a field that looks up the full Parent Tree in your Incident form. So go to Setup/Create/Objects/Incident Object; and go to Custom Fields and Relationships, and create a new field. I called mine AB_Category_Tree. Create it as a formula field, and in the Formula, enter:



I put it on my Remedyforce Incident form, so I could see the results:


Step 3

Create a field that will extract everything after the “!” and put it into a field that can be used for reporting. The important thing here is that IF someone chooses the very top level category – i.e. !Level 1 – the field we created in Step 2 will be blank, so we need to account for that.

So again, go to Setup/Create/Object/Incident and go to Custom Fields and Relationships. Create a formula field; I called my field Parent Category. This time, the Formula is:

IF( ISBLANK(AB_Category_Tree__c),   BMCServiceDesk__Category_ID__c , RIGHT(AB_Category_Tree__c, LEN(AB_Category_Tree__c)- FIND(“!”,  AB_Category_Tree__c) + 1) )

To break that down:

IF( ISBLANK(AB_Category_Tree__c),   BMCServiceDesk__Category_ID__c , This is for those cases where someone picks the top level category; since the tree will be blank, we’ll just assign the category
RIGHT(AB_Category_Tree__c We want to start from the right of the parent tree field, not the left and take a certain number of characters
FIND(“!”,  AB_Category_Tree__c) This gives us the place in the string where the special character is – I used !, but replace that with your special character
LEN(AB_Category_Tree__c)- FIND(“!”,  AB_Category_Tree__c) + 1) If I take the full length of the string (let’s say 25) and remove where I found the special character (let’s say position 17) I’ll have the number of characters remaining after the special character – 8. I add 1 back on to get the special character. (I’ll need this to match what happens if I just select the top level category.)
RIGHT(AB_Category_Tree__c, LEN(AB_Category_Tree__c)- FIND(“!”,  AB_Category_Tree__c) + 1) Using the example right above, if the string is 25 characters long and the special character is in position 17, I am taking the right-most 9 characters to fill in the Parent Category field.


Step 4

Create a report showing Parent Category and Category to see the results! No matter which category they pick under !Level 1, I can still report on all tickets that came in for that parent category.


And here’s that same report in Lightning:


Let us know if you have any questions or comments!

0 Flares Twitter 0 Facebook 0 LinkedIn 0 0 Flares ×

Comments are closed.

Show Buttons
Hide Buttons