How to report on and group tasks by State field

[susithras]

Anyone using our platform or even relatively new comes to learn this fact fast: The Task table is the master table and parents all of the request and incident tables. This means most of the fields on the child table are referenced from the task table and the State field is one of them. Each of these child tables are mapped to the Task table by the sys_class_name field, but reference the same State field. So to identify the State field definitions for each of these tables, you need to filter on both these fields on the Choices (sys_choice) table. Understanding this is key to the topic we are about to discuss here.

When reporting and grouping tasks by the State field goes wrong

Let's say you or management want to see all the incidents, changes, requests, etc. categorized by their different Statuses (namely, Open, In Progress, Pending, Resolved, or Closed). As a user you would expect to achieve this by creating a report off of the Task table and grouping it by the State field.

Assuming the same, let us say you try reporting off of Task with the Group By field as State as shown below in Fig1.1.

Fig 1.1. Report off Task Group By State

You might expect it to show you a few generic States like New, Open, Work In Progress, Complete, Resolved and Closed grouped with all the records from all the child tables of Task. However, you might be surprised by the outcome as shown in the picture below in Fig 1.2.

                                                                                               

Fig 1.2. Actual Out of Box grouping by State field on Task

Why does reporting on the task and grouping on the State fields show unexpected results?

It is going to show you all the records from all those tables with their own State field values, which may or may not be set to be the same value in the Choice List definition for the State field, as we have seen generally is the case. Please note that we are talking about the values mapped to the Labels on the Choice List of each of those tables. The value could be numeric or string and it may or may not map to the same Labels across different tables. So, you can imagine the multitude of possible results this can show you. It will show you multiple groupings of similar statuses having different values set up in the Choice List of each of those child tables.

For example, let's say the Choice List definition value for the State field in Incident for Closed Complete is 10 and the same is set to 7 on the Change table. So even though you see the Label set to Closed Complete on both these tables in the Choice List definition, it will still show you two different groupings with the same label, because the value is different.

Is this offered as a default feature?

As much as I can see how upper management or everyone would like this to be an out-of-box feature, this is simply something that needs to be implemented right the first time and it will stay with you forever. Unless you don't want it to, of course, because reporting off of the Task table is a costly query, since Task is after all the Master table. Having said that, we perfectly understand that our customer's business needs come first, and hence provide you with the following options to achieve the same.  

How to report and group tasks by State field

Depending on your scenario, you have a few options for reporting on task and grouping by the State field.

Scenario 1: If you are implementing the solution for the first time on a brand new instance

Great! You are in an ideal place to start. You can achieve this by simply checking with your management team what are the main Statuses of the several child table records that they are interested in seeing on their reports and monitors. Then you can just set those Statuses' choice list values to be uniform across all the child tables, so you can pull the records from all the child tables of Task and group them by those Statuses accordingly. You can then display this report in whichever format you want to display it in a reader-friendly way in a dashboard! Voila! Good job!>

Please refer to this best practice documentation: How to configure state field choice values.

Management will hold you close and cherish you forever and, if wise enough, not let you go. Who knows if the new guys are smart enough to check for artifacts like you did in the Community rather than implement it the way most people do and then a few years down the lane, when someone runs up against this issue, have no clue what they did wrong in the first place to start with! Good on you for having checked the Community to leverage the wisdom of previous users and veterans like us who care to share.

Scenario 2: Your system was already set up by someone who is long gone and there is no documentation

While this is not as ideal as the first situation, we still have some options for your case too.

Before starting on this, we need to first plan for updating the existing records to be reflected with the new State field values. You can simply write an sql update query or stored procedure to update the records, or you can run a business rule to map the old State choice values to the new ones to run the report, as suggested in the third option below.

Possible Solution 1: Please refer to KB0523289: Filtering and grouping tasks by states does not work with a large number of state choices.

The basic idea here is to clear out all the existing State field flags and replace them with common ones for all the child tables of task for you to be able to report uniformly at the Task table level. While this KB article contains some instructions that do not apply to the newer versions, the general idea and script itself can be reused to customize it to your needs and achieve your requirements. Please use this as a guidance and not verbatim.

Possible Solution 2: For addressing the issue, the ideal way it is meant to work is to assign the same values (1,2,3, etc.) to the sys_choice list labels (New, In Progress, Closed, etc.), which are in similar states for each of those tables (Incidents, Problem, Change, etc). The solution is the same as the best-case scenario one mentioned above. However, since you need to modify the existing State fields, you can follow the steps which were automated in the mentioned KB0523289 script, manually or just broken down into several steps as follows. Choose the method that is best for you.

2.1.   Consolidate the sys_choice list for the States field, for the tables on which you are running this report. You can access the choice list by filtering in the navigation menu, by typing sys_choice.LIST.

2.2. It will take you to the page where you can filter to look for the specific table, for example Incident, that you would like to change the State field Choice List values for them. You can modify them to the new State values and make note of the same to update it in your existing records in your script or query, which you use to update the records. Please refer to this example: State modification example.

Fig 2.1. Incident table State Choice value

2.3 Please keep in mind, you will need to verify all the related Rules associated with the state field and test them thoroughly to ensure this change does not break any other feature. Please verify for Business Rules, Script Includes, etc. by filtering on the Script field containing the old state field values. Refer to this product documentation: Troubleshoot change states and business rules.

Possible Solution 3: Create a new field which will hold the consolidated new set of choice list values and use a BR to map them to this field automatically.

3.1. Create a new field work list status field on the task table (example: u_work_status)

3.2. Create a new set of choice labels/values you will want, for the new work list status field.

3.3. Create a business rule on the task table with logic to map the different <table>.state values to the corresponding work list status value, and set u_work_status to that value.

Note that whenever you add a set of new choices for <table>.state (such as turning on a plugin like SDLC — Scrum Process Pack or Demand Management), you will need to update this mapping business rule.

OR — you can put the mapping into a custom table, and design your business rule to do the mapping from the custom table, in which case you never have to update the business rule logic, only the contents of the mapping table.

You can also refer to this Community thread about the State field on the task table discussing this solution and other possible solutions to achieve the same goal.

Your new field will hold a standardized list of states. Now, you can use it to query by the new status, and the filtering and grouping will work as expected.

Once the report list is consolidated in the list view, you can now display it in a dashboard format of your choice to present it in a visually appealing format. This will provide you a high level overview of record statuses of all the types of requests, incidents, etc. and give a good status report of where your system is at.

Please bear in mind that this is a very high-level report and might cost you a lot in terms of performance, depending on the number of records and child tables you have in Task for your IT Operations Management team. 

As much as we like to share capabilities of the system, we also would like to understand the business needs of our customers to serve them better. Please share your thoughts and comments and any feedback, so we can improve on providing you with useful and accurate information.