Creating a Post Query Filter

You can use queries in the Post Query Filter box to filter the data that is collected using the SQL query on the Query tab in the data set. Each data set query creates a cache table in a database where the data collected from all of the data sources is stored. Post query filters allow you to aggregate and group data across multiple data sources. Instead of querying all of the data sources again, operations performed using the post query filter return results from the data cached in the database, which improves the speed and efficiency of the report. For example, you can use post query filters to retrieve agent types in a Multi-CommCell query, or to create a list box input.

Before You Begin

Build a Report
To create data sets, build reports, manage reports, and deploy reports to the dashboard, your CommCell user account requires a role with the following permissions: Add Report, Delete Report, Edit Report, Execute Report, Add Datasource, Delete Datasource, Edit Datasource, Query Datasource.
Your CommCell user account must have an association with the CommCell entity.

For more information on users, permissions, and associations, see User Administration and Security - Overview.

About This Task

The $this$ moniker in the query is used to access the cache table created in the database for the dataset.

Procedure

On Web Console, click Reports.

The Reports page appears.
From the navigation pane, click Configuration > Reports, and next to the Report Name under Actions, click Edit.
On the Dataset tab, select the main data set for the report and click Edit .
In the Edit Data Set dialog box, on the Advanced tab, select Post Query Filter, and enter a SQL query.
- For example, to aggregate cached data across multiple data sets in one report, type:
```
select agent, count(*) as [Number of Agents] 
from $this$ 
group by agent
    
```
- For example, to create a list box input with multi selection that will filter the cached report data, type:
```
select *
from $this$
where agent in (select @agentName from agentName)
    
```
  In this example, @agentName is a variable created on the Parameters tab in the data set.
  
  To make this input work, create a list box input control, create the input parameter with multi enabled in the data set, and then add this query to the Post Query Filter box.
  
  Important: The variables that you add must be preceded by the at symbol (@).
Click Done.