703.242.7200 [email protected]

Reporting on multi-level categories

RightStar TeamJune 21, 2018

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, and 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, the 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 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, 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:
BMCServiceDesk__FKCategory__r.BMCServiceDesk__parentTree__c

 

 

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 a 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!