Tutorial: Groups, filters, and functions in reports (App)
This article explains the basics of groups, functions, and filters in reports in App.
In this article
Overview
Groups, functions, and filters are advanced tools used for building reports in App. To explain how they work, we’ll walk through a tutorial of how to set up a report using all three.
The goal of this tutorial is to build a report that shows how many interactions are in each queue on a given day. To accomplish this, we’ll need to build a report that:
Groups by queue
Calculates sums with a function
Filters by day
Continue reading the subsections below to follow along with this tutorial.
Setup: Creating a report
To get started, follow the steps in the article Create a report (App), making sure to select the Interactions data source.
Once you have a report created, continue to Step 1 below.
Step 1: Setting up groups
The first step to building our report is adding a group. A group is a way of summarizing several rows of data by a common column.
Groups are useful for aggregate data. When you want to summarize data by a common column (like queue), use a group.
Let’s look at an example of grouping. On the left side of the graphic below, we have three columns showing:
Interaction Id
Queue (name)
Handle Time
This is raw data that’s not being grouped or otherwise manipulated. In other words, it’s a report with three columns and no groups.
Each row is color coded according to the queue:
Tech Support is blue, Billing 1 is green, and Main Q is red.
On the right side of the example above, we’re grouping by queue. In other words, we have the Queue as the group and Interaction Id and Handle Time as columns. This means that we’re aggregating all of the data seen on the left side by its queue value (the queue that hosted the interaction). Then, we apply functions to the Interaction Id and Handle Time columns to show the count of interactions and the average handle time, respectively. We’ll look more closely at functions further down in this article.
Remember that our goal is to build a report that shows how many interactions are in each queue on a given day. The key to understanding this goal is the phrase in each queue. This lets us know that we want to group by queue name.
In the screenshot below, we’ve added the column Queue Name to the Groups tab. This means that we’re grouping by Queue Name. So, we’re instructing the report builder to group all records in the Interactions data source by their Queue Name values.
The report has the Queue Name column as a group, meaning it will group all interaction records by the name of the queue that hosted the interaction
The screenshot below shows that, after grouping by queue name, the report displays a list of several queues. This is a list of every queue that’s ever hosted an interaction on this account. In other words, these are the groups the report has after grouping by Queue Name.
Once you’ve added a group, the next step is to add columns and apply functions to the columns that need them. For more information, continue reading the section below.
Step 2: Applying a function
The second step to building our report is applying functions.
In the report editor, a function is a command you can apply to a column to modify the value in some way, either by modifying its formatting or applying a calculation (like a sum or an average).
Therefore, it’s important to know that the function that you can apply to a column is based on the values of that column. Some column values are time-based, some columns are text- based, etc. So, first define the type of column you’re working with to determine the type of functions that you can apply to it.
In our scenario, we are seeking to define the total number of interactions for each queue. So, we need to apply a sum function to the Interaction ID column.
Then, we are seeking to see the total average handle time for the total interactions in each queue. So, we need to apply an averaging function to our time-based value column, Handle Time.
Continue reading to see how we’ll go about this.
In the screenshot above, we have the Queue Name group that we applied in Step 1, and the two additional columns we added Interaction Id and Handle Time.
We’re grouping by queue name, but we’re not yet seeing a count of interactions in the queue or the average handle time. This is because we haven’t told the report to use a function to calculate that data.
To accomplish this, we’ll apply a function to the columns.
When on the Columns tab, apply a function to a column or group with the function icon f(x)
Remember that we want to show a count of interactions in a queue on a given day.
So, we’ll first choose the Count function and apply it to the Interaction ID column. That column consists of values that the report can apply a calculation to - like sum - as opposed to a text-based value column, like the Communication Type column.
Next we will apply a function to Handle Time to average the time values of a column. In this scenario, we want to see the average handle time of all interactions in a queue, so we will apply the Average -Time function to the Handle Time column to calculate that average.
Applying the Count function to the Interaction Id column
Applying the Average - Time function to the Handle Time column
Let’s now take a look at the two screenshots below to see how it works when we set and then process the applied functions.
Figure A shows our report with the functions applied, but we haven’t re-run the report yet. The functions are just set in Figure A.
Figure B shows the same report after re-running the report, which triggered processing the applied functions to the data.
Figure A
Figure B
We can see from the above screenshots that, after applying the functions and re-running the reports, the report returns very different results.
Count function results: The Interaction ID column is showing a number value that represents the number of interactions that have ever been in the queue.
Average-time function results: The Handle Time column is showing the average handle time for all interactions in that queue
We’re almost done. This report is showing data from all time, but we want to see data in a specific time frame. To accomplish this, we’ll apply a filter.
Step 3: Applying a filter
As a reminder, the goal of this tutorial is to build a report that shows how many interactions are in each queue on a given day.
By now, we’ve built a report that shows how many interactions are in each queue. The final step is to apply a filter to only show interactions created on a given day.
Click Filters to reveal the filter builder. The first dropdown menu is where you select the column you want to apply the filter to. For our example, we’ll choose Created At.
The next column is where you apply the evaluator. This compares the column value on the left to a condition set on the right. For example, if you only want to show interactions created in the past day, you would set the evaluator to “greater than” (>) and choose a function to represent the past day.
For date filters, you can filter with a function (a dynamic date range like the last 30 days, last week, etc.) or a calendar picker (a static date).
Filtering by date with a function (a dynamic filter)
Filtering by date with a date picker (a static filter)
Finished result
After applying a filter, click the run button again to show the result.
The final report that shows a list of all interactions in queues over the past day with the average handle time across those interactions.