Purpose

The Bright Pattern Contact Center Custom Reporting Tutorial explains how you can create custom report templates for your Bright Pattern Contact Center solution.

The first two sections of this tutorial describe how to configure the TIBCO Jaspersoft Studio application to customize report templates. The last section describes an alternative way to export custom data sets from the Reporting Database in CSV format using BPXML templates.

Additional Information

The following resources are recommended as a starting point for learning the Jasper Reports syntax and the Jaspersoft Studio visual editor:

http://community.jaspersoft.com/wiki/jaspersoft-studio-tutorials-archive

Note: Jaspersoft has two different tools for report development: iReport Designer and Jaspersoft Studio. Only Jaspersoft Studio is supported as the tool for the creation of custom report templates for your Bright Pattern Contact Center solution.

Audience

The Bright Pattern Contact Center Custom Reporting Tutorial is intended primarily for professionals responsible for the design, development, and testing of custom applications and reports in your contact center.

Readers are expected to be have experience with relational databases and Jaspersoft Studio, as well as a solid understanding of contact center operations and resources that are involved in such operations.

Prerequisites

Download and install the latest version of TIBCO Jaspersoft Studio. After installation is finished, create a new workspace that will be used for your custom report templates.

Downloading Libraries and Localization Resources

To reuse Bright Pattern report templates in Jaspersoft Studio, follow these steps:

1. Download the Report-template-libs.zip package containing libraries and localization resources that facilitate the reuse of Bright Pattern Contact Center report templates in Jaspersoft Studio.
   
   
2. Extract the files to your local computer, preserving the internal folder structure.

Importing Libraries

After you have downloaded libraries and localization resources, the next step is to import the libraries.

Note that if you have just installed Jaspersoft Studio on your computer, your workspace may not contain any folders or projects in the Project Explorer tab, and you may have to add a new folder called MyReports before you can proceed with the following steps.

Procedure

Step 1: Add libraries to MyReports project

1. In Jaspersoft Studio, navigate to the Project tab in the left-hand part of the screen.
   
   
2. Select MyReports.
   
   
3. Right-click MyReports and then select New > Folder.
   
   
   

   How to create a new folder
   
   
   
4. In the dialog box that pops up, select parent folder MyReports and name the new folder libs. This is the folder that will contain your libraries.
   
   
5. Click Finish.
   
   
   

   Project > MyReports > New > Folder

Step 2: Import files

1. Right-click the libs folder and select Import.
   
   
   

   libs > Import
   
   
   
2. In the dialog window that pops up, select File System.
   
   
3. Click Next.
   
   
   

   libs > Import > File System

Step 3: Add files from the report template library for use in Jaspersoft Studio

1. Click Browse to find the report-template-libs folder of the package that you previously downloaded and extracted.
   
   
2. Select the report-template-libs folder and click OK.
   
   
3. Select these files:
   
   
   1. report-formatter.jar (to provide scriplet functions for output formatting)
      
      
   2. mariadb-java-client-1.1.6.jar (for database connection)
      
      
   3. icu4j-4.8.1.jar (for Unicode and localization support)
      
      
   4. report-fonts.jar (for Unicode fonts)
      
      
4. Click Finish.
   
   
   

   Libs > Import > File System > report-template-libs

Step 4: Build paths to the files within the project folder

1. Select these .jar files in the Project Explorer. You can select all four as a batch.
   
   
2. Right-click the selection and then select Build Path > Add to Build Path. Doing so will add a path for each .jar file to the root folder of the project.
   
   
   

   Build Path > Add to Build Path

Step 5: Add remaining report-template-libs files to the project

Add the files report_logo_bar.png and report_logo.png to the root folder of the project by following step 2 and step 3 of this procedure. Instead of selecting the .jar files, select the .png files from the report-template-libs folder.

Importing Localization Resources

Localization resources are imported to Jaspersoft Studio so that reports are displayed in multiple languages. These resources are found in the com subfolder of the report-template-libs folder in the package that you previously downloaded and extracted.

How to Import

To import the localization resources, follow these steps.

Step 1: In the MyReports project, create a folder called resources

• In Jaspersoft Studio, navigate to the Project tab in the left-hand part of the screen.
  
  
• Select MyReports.
  
  
• Right-click MyReports and then select New > Folder.
  
  
• In the dialog box that pops up, select parent folder MyReports and name the new folder resources. This is the folder that will contain your localization resources.
  
  
• Click Finish.

Step 2: Import files

• Right-click the resources folder and select Import….
  
  
• In the dialog window that pops up, select File System.
  
  
• Click Next.
  
  
• Open the report-template-libs folder of the previously downloaded and extracted package and click OK.
  
  
• Select the com subfolder and click Finish.

MyReports now contains folder resources and subfolder com.

Step 3: Build paths to the files within the project folder

Right-click resources and select Build Path > Use As Source Folder. Doing so makes resources a source folder available at the top of MyReports.

Downloading Report Templates

Bright Pattern's Contact Center Administrator application provides a number of report templates. Report templates provide an easy launch point to create custom reports in Jaspersoft Studio.

Note that report templates are available in either .jrxml or .bpxml format. Only CSV reports have the .bpxml file extension. The capability to export a large volume of data as a CSV report (.bpxml) is currently provided for two report types only: Agent Activity (CSV) and Call Detail (CSV).

How to Download Report Templates

1. From Contact Center Administrator's Configuration menu, navigate to Reporting > Report Templates.
2. Select the template(s) that you wish to modify.
   
   
3. In the Properties tab, beside the report template file name, click download.
   
   
   

   Contact Center Administrator > Reporting > Report Templates
   
   
   
4. When the file is opened in Jaspersoft Studio, the report template appears as shown.
   
   
   

   A .jrxml report template file in Jaspersoft Studio

Importing and Modifying Report Templates

To work with and customize report templates in .jrxml format, you must first import them to Jaspersoft Studio using the following steps.

How to Import Report Templates

Step 1: Create a folder to store your reports

1. In Jaspersoft Studio, navigate to the Project tab in the left-hand part of the screen.
   
   
2. Select MyReports.
   
   
3. Right-click MyReports and then select New > Folder.
   
   
4. In the dialog box that pops up, select parent folder MyReports and name the new folder reports. This is the folder that will contain your report templates.
   
   
5. Click Finish.

Step 2: Import report template files to the new reports folder

1. Right-click the reports folder and select Import.
   
   
2. In the dialog window that pops up, select File System.
   
   
3. Click Next.
   
   
4. Choose the folder where you have stored the report template files that you previously downloaded from the Contact Center Administrator application. These files will have .jrxml file extensions in their names.
   
   
5. Select these files and click Finish.

Example: How to Modify .jrxml Report Templates

Once you have downloaded a report template, you can use the visual editor of the Jaspersoft Studio to import and modify .jrxml report templates (e.g., remove some of the existing metrics or add new ones). Report templates in the .jrxml format that are imported to Jaspersoft Studio will look as shown:

What follows is an example of how one might modify a .jrxml report template. In this example, the column Case is removed from the out-of-the-box Agent Activity Report and the formatting/visual appearance of the report is changed.

Step 1: Open file agent_activity.jrxml

1. In Jaspersoft Studio, navigate to My Reports > reports.
   
   
2. Find and double-click the agent_activity.jrxml file to open it.

Step 2: Select the elements you wish you change

1. Select elements $R{col_case_id} in the group header frame, and $F{case_number} in the detail frame of the last column. You can select both elements at the same time.
   
   
2. Right-click the selected elements and select Delete to remove these elements.

Step 3: Adjust the presentation of report elements as desired

1. Select the report frames and reduce their widths by dragging the elements to the right position. You can use the grid lines to help you find the edges of each element.
   
   
   

   Frame width is adjusted
   
   
   
2. To reduce the width of the report, click into the design view (the gray part of the screen, below the report) of the report in focus. That will bring up the Properties pane on the right-hand side of the screen.
   
   
   

   Report template properties
   
   
   
3. At the bottom of Properties, select Edit Page Format. In the window that pops up, you can reduce the width of the report, as well as make other adjustments as needed.
   
   
   

   Properties > Page Format

Previewing and Saving Modified Report Templates

When modifying report templates, you may wish to see how the changes you make in the Design view actually look on the report template. The Jaspersoft Studio Preview tab generates and displays the report as-is, providing a glimpse of the report template in its current state.

How to Preview

To preview a modified report, follow these steps:

1. Enter/edit all parameters in Design view (e.g., Timeframe (start_time:Timeframe Start)).
   
   
2. Select the Preview tab.
   
   
3. Select the data source.
   
   
4. Enter the desired report format (i.e., Java, HTML, PDF, DOCx, etc.) with which to view the report.
   
   
5. Click the Run button .

Your modified report will be displayed in the Preview area. If necessary, return to the Design view for further editing. Otherwise, save the modified report template with a new name by clicking File > Save As and entering a new file name.

Doing a Save As only saves the modified report template in the Jaspersoft Studio reports folder. If you wish to upload the modified report template to Contact Center Administrator, you must also export the new report template and save it to the appropriate folder.

Notes

• If you see an exception regarding JRAbstractScriplet, restart the Jaspersoft Studio and repeat these steps.

• When you click the Preview tab, a report will be run automatically. You must wait for it to be complete before you can select the desired data source and click Run.

• If you get a compilation error, try reformatting the report template, and repeat the above Preview steps.

Uploading Modified Report Templates

Once you have finished customizing your report template, you can upload it to the Contact Center Administrator application and put it to use. Revised report templates are available for use as soon as they are uploaded.

In the Contact Center Administrator application,

1. Navigate to Report Templates.
   
   
2. Click + to add a new report template.
   
   
3. Specify the report name and category, and click upload.
   
   
4. Select the template you saved previously.
   
   
5. Click the Upload button.

Overview

Bright Pattern Contact Center version 3.16 and later provides the capability to export a large volume of data as a CSV report. This capability is generally used for detailed tables such as agent_activity and call_detail (see our out-of-the-box CSV reports: Agent Activity (CSV) and Call Detail (CSV)). These report templates are in a new format called BPXML, and such files have the file extension .bpxml.

You can create CSV reports for any report template type, not just the out-of-the-box report templates described above.

Unlike report templates in JRXML format, report templates in BPXML format are not customized in Jaspersoft Studio. Designed to be simpler to edit, BPXML report templates are worked in the plain text editor of your choice. Essentially, a BPXML report template is an XML file with special headers and SQL statements that retrieve the specified data and place it in the CSV file.

In the sections that follow, you will learn how to download and modify BPXML files to create custom CSV reports.

See the Contact Center Administrator Guide for more information on report templates.

BPXML Syntax and Samples

You can create a custom BPXML report template and use it to export CSV data. This section explains the syntax and structure of BPXML report templates.

Syntax

The top level "BPcsvReport" element contains optional "parameters" and "queryString" sub-elements.

"queryString" is a common SQL statement, and it can contain some parameters.

Parameter Types

The following is the full set of supported parameters:

• "agent" – agent selector
• "agentlist" – agent multiple selector
• "end_time"
• "service" – service selector
• "servicelist" – service multiple selector
• "start_time"
• "team” – team selector
• "teamlist" – team multiple selector
• "timeframe" subtypes:
  • "start"
  • "end" (only one pair per report) – start and end dates selector
  • "string" - an arbitrary string value

Sample BPXML Report

<?xml version="1.0" encoding="UTF-8"?>
<BPcsvReport version="1.0" resourceBundle="com.brightpattern.reports.oob_reports">
<parameters>
<parameter name="start_time" type="timeframe" subtype="start"/>
<parameter name="end_time" type="timeframe" subtype="end"/>
<parameter name="login_id" type="agent" />
</parameters>
<queryString>
<![CDATA[
SELECT
start_time,
first_name,
last_name,
num_calls_in,
num_calls_answered,
team_name
FROM agent_performance
WHERE start_time > $P{start_time} AND login_id=$P{login_id}
]]>
</queryString>
</BPcsvReport>

Examples

The following are examples of various types of BPXML reports. Values that differ from the Sample BPXML Report given above are shown in pink.

"agentlist" Usage Sample

In this sample, parameter agentlist is being used to add agent data to the Agent Performance report.

<?xml version="1.0" encoding="UTF-8"?>
<BPcsvReport version="1.0" resourceBundle="com.brightpattern.reports.oob_reports">
<parameters>
<parameter name="start_time" type="timeframe" subtype="start"/>
<parameter name="end_time" type="timeframe" subtype="end"/>
<parameter name="login_id" type="agentlist" />
</parameters>
<queryString>
<![CDATA[
SELECT
start_time,
first_name,
last_name,
num_calls_in,
num_calls_answered,
team_name
FROM agent_performance
WHERE start_time > $P{start_time} AND login_id IN ($P{login_id})
]]>
</queryString>
</BPcsvReport>

"call_detail" Usage Sample

In this sample, metrics hold_time, held, and max_hold are being used to add hold time data to the Call Detail report.

<?xml version="1.0" encoding="UTF-8"?>
<BPcsvReport version="1.0" resourceBundle="com.brightpattern.reports.oob_reports">
<parameters>
<parameter name="start_time" type="timeframe" subtype="start"/>
<parameter name="end_time" type="timeframe" subtype="end"/>
<parameter name="login_id" type="agent" />
</parameters>
<queryString>
<![CDATA[
SELECT
start_time,
first_name,
last_name,
num_calls_in,
num_calls_answered,
hold_time,
held,
max_hold,
team_name
FROM call_detail
WHERE start_time > $P{start_time} AND login_id=$P{login_id}
]]>
</queryString>
</BPcsvReport>  

How to Modify and Use BPXML Report Templates

1. Either copy the Sample BPXML Report XML, or download a BPXML report template from the Contact Center Administrator application.
   
   
2. Paste the XML into a plain text editing application, and edit the parameters to obtain the desired data in CSV.
   
   
3. Save the file with file extension .bpxml. This is your customized report template.
   
   
4. In Contact Center Administrator, upload your customized report template. The new report template will appear in the list of available templates.

Creating a Data Adapter

Data adapters allow Jaspersoft Studio to use SQL queries to get data from your Reporting Database. In the following procedure, you will create a data adapter that uses the Java Database Connectivity (JDBC) API for the programming language Java, which defines how a database can be accessed by a client. JDBC provides methods to query and update data in a database.

Note that creating a data adapter is not required unless you have direct database access; direct database access is a licensed fee.

Procedure

To create a data adapter, navigate to the Repository Explorer tab in the left-hand corner of the screen, and proceed through the following steps.

Step 1: Create a data adapter

1. In Repository Explorer, select and right-click Data Adapters.
   
   
2. Then select Create Data Adapter.

Step 2: Choose your connection

• In the window that pops up, select Database JDBC Connection and click Next.

Step 3: Specify JDBC properties

You will be defining the properties (i.e., driver, database server host, authentication) for the Java Database connection.

In the window that pops up, specify the following:

• Name: Name of the data adapter; note that "New Data Adapter" is the default name but it may be renamed
• JDBC Driver: org.mariadb.jdbc.Driver (Note: copy and paste this driver name into the field, or use the pull-down menu to select this driver.)
• JDBC Url: jdbc:mariadb://<your database server host name>:3306/< your reporting database name>
• Username: Username for access to your Reporting Database
• Password: Password for access to your Reporting Database

Then click Finish.

Notes

• For database server host name, database name, and username, open the Contact Center Administrator application and navigate to Reporting Settings.

• In the JDBC Url field, do not include "<" or ">" symbols. Replace "<your database server host name>" and "<your reporting database name>" with your respective host and database names.