This Forum Post will explain how to create a Report that will show you how many times a Case has been closed.

The screenshot below gives you an idea of how it could look:

Why should I use this Report?

Understanding how many times a Case has been closed can give you an insight into the quality of service your Customer Support Teams are offering.

If a Case has been closed multiple times this may be an indication that the first resolution was incorrect and had to be reopened in order to find the correct resolution.

This is particularly helpful if you have KPIs or Targets to ensure that Cases are resolved first time.

How do I create this Report?

Step 1. Create a pre-populated report based on Audit Records

Navigate to Start > New > Report > Create a new pre-populated report > Audit Records

Step 2. Add the Criteria

This pre-populated Report gives us all of the information that we are interested in, notably New Value, Record Type and User Name.

The first step is for us to add a number of Criteria that will limit the Report to only show us Cases that have been closed.

To do this we will need to add the following Criteria:

Field Changes > New Value contains Closed

It is important to use the contains Operator especially if you have multiple Statuses such as “Closed Customer Notified” or “Case Closed”.

Record Type is Case

These criteria will ensure that the only Audit Records we now see are ones where a Case’s State has become Closed, which is what we want to see.

Step 3. Create a Summary View

Once we have add the Criteria we will now need to create a Summary View, this will be where we see how many times a Case has been closed.

The first Column we will create is a Grouping Column, we will be grouping by Reference.

After this we will now create a Summarised Column, in this Column we are also going to use Reference and calculate the Count, so you should have something like below:

This will give us the total amount of times this Case has been closed, we can also choose to add a grand total at the bottom if you want to see how many times all of your Cases have been closed.

We can then add the Descriptor and User name as Value Columns so we can see what the Case was called and who closed it.

If you would like to see the first and last time the Case was closed, you can add two more Summarised Columns. To do this we calculate the Maximum and Minimum of the Date Column, such as below, note that we do not want to tick the Display Summary box.

We can also create a Calculated Column to calculate the difference between the first and last time the Case was opened using the following formula:

DATEDIFF(MAX(PARENT(‘Date’)), MIN(PARENT(‘Date’)))

Together we are given a Report that looks something like this:

Additional Customisation

If you are targeting your Customer Support Team to keep closures to a minimum you can also add CSS styling to any of the columns, i.e. the Number of time Closed Column. This could be used to highlight those that have been closed an excessive number of times. More information about using CSS styling can be found here.

If you only want to see Cases that have been closed more than once, you would achieve this using by using Calculated Criteria such as below: